Left join on multiple column performance?

Question

Assume that I have 2 tables :

table1 :(20.000 records)
id    code1    code2   something    status

table2: (7.500 records)
id    code1    code2    name

All I want is list all records in table1 with the "name" in table2 by using this QUERY:

SELECT DISTINCT `tb1`.*, `tb2`.`name` FROM `table1` AS `tb1`
LEFT JOIN `table2` AS `tb2`
ON (tb1.code1 = tb2.code1 AND tb1.code2 = tb2.code2)
WHERE (tb1.status = 1)

But it took me too long to retreive the data (after 5 minutes I still cant see the result).

What is the best way to do this?

Thanks in advance..

Answer 1

Please try adding an index on table1 using columns(code1,code2,status). If you don't have too many columns in table1, you can add them to the index too. In MS SQL, we have "include columns" that we can add to an index. Maybe mysql has something similar.

Add an index on table2 using columns(code1,code2, name).

If you are concerned about index size then just keep (code1, code2, status) for index1 and (code1, code2) for index2.

Hope this helps.