# INNER JOIN OPTIMALIZATION

**URL:** <https://forums.percona.com/t/inner-join-optimalization/265>\
**Category:** Other MySQL® Questions\
**Created:** [March 29, 2007, 9:15am UTC](https://forums.percona.com/t/inner-join-optimalization/265 "2007-03-29T09:15:20Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![lejtkin](https://avatars.discourse-cdn.com/v4/letter/l/9de0a6/32.png) [@lejtkin](https://forums.percona.com/u/lejtkin)\
**Post date:** [March 29, 2007, 9:15am UTC](https://forums.percona.com/t/inner-join-optimalization/265/1 "2007-03-29T09:15:20Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [March 30, 2007, 3:01am UTC](https://forums.percona.com/t/inner-join-optimalization/265/2 "2007-03-30T03:01:08Z")

</div>

Can you change your WHERE:

`transaction_accounts`.`identified` != `transaction_accounts`.`date`

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.