•  

Announcement

Announcement Module
Collapse
No announcement yet.

INNER JOIN OPTIMALIZATION

Page Title Module
Move Remove Collapse
Conversation Detail Module
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts

  • INNER JOIN OPTIMALIZATION

    #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_l en|ref|row s|Extra |
    |1|SIMPLE|loans|ALL|PRIMARY|NULL|NULL|NULL|2440|Us ing temporary;Using filesort|
    |1|SIMPLE|transaction_accounts|ref|lid|lid|3|tommy stachi.loa ns.lid 10|Using where|

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

  • #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.

    Comment

    Working...
    X