INNER JOIN OPTIMALIZATION

MySQL & MariaDB Other MySQL® Questions
1

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

2

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.