多列性能的左连接?

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:

我想使用这个查询，列出表1中带有表2“name”的所有记录:

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).

但是我花了很长时间才把数据收回来(5分钟后我还是看不到结果)。

What is the best way to do this?

最好的方法是什么?

Thanks in advance..

提前谢谢. .

1 个解决方案

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

请尝试使用列(code1、code2、status)在表1上添加索引。如果表1中没有太多列，也可以将它们添加到索引中。在MS SQL中，我们有“包含列”，可以添加到索引中。也许mysql也有类似的功能。

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

使用列(code1、code2、name)在表2上添加索引。

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

如果你关心索引大小，那么只需要保持(code1, code2, status)为index1和(code1, code2)为index2。

Hope this helps.

希望这个有帮助。

#1