Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

INNER JOIN OPTIMALIZATION

lejtkinlejtkin EntrantCurrent User Role Beginner
I have problem with inner join optimalization, query:
SELECT `transaction_accounts`.`date`, `transaction_accounts`.`amount`, `transaction_accounts`.`refund`, `transaction_accounts`.`lid`, `loans`.`signatureNumber`, `transaction_accounts`.`identified` FROM `transaction_accounts` INNER JOIN `loans` ON `loans`.`lid` = `transaction_accounts`.`lid` WHERE `transaction_accounts`.`identified` != `transaction_accounts`.`date` ORDER BY `transaction_accounts`.`date`;

EXPLAIN:
|id|select_type|table|type|possible_keys|key|key_len|ref|row s|Extra |
|1|SIMPLE|loans|ALL|PRIMARY|NULL|NULL|NULL|2440|Using temporary;Using filesort|
|1|SIMPLE|transaction_accounts|ref|lid|lid|3|tommystachi.loa ns.lid 10|Using where|

I want change using temporary and using filesort, but I have no idea how can I do

Comments

  • sterinsterin Mentor Inactive User Role Contributor
    Can you change your WHERE:

    `transaction_accounts`.`identified` != `transaction_accounts`.`date`</pre>

    To something else that is "not equals another column".
    Because that line right there will always result in at least a range scan of the complete index or a full table scan.

    As for temporary and file sort:
    What you can laborate with is creating a combined index where the columns of the where clause are first and the order by column is the last one.
    This way MySQL should be able to use that index to solve both the WHERE and the ORDER BY and it is the ORDER BY part that causes this temporary, filesort for you.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.