当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - slow left join using mysql

问题描述:

Here is the SQL query in question:

select * from company1

left join company2 on company2.model

LIKE CONCAT(company1.model,'%')

where company1.manufacturer = company2.manufacturer

company1 contains 2000 rows while company2 contains 9000 rows.

The query takes around 25 seconds to complete.

I have company1.model and company2.model indexed.

Any idea how I can speed this up? Thanks!

+----+-------------+-----------+------+---------------+------+---------+------+------+--------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-----------+------+---------------+------+---------+------+------+--------------------------------+

| 1 | SIMPLE | company1 | ALL | NULL | NULL | NULL | NULL | 2853 | |

| 1 | SIMPLE | company2 | ALL | NULL | NULL | NULL | NULL | 8986 | Using where; Using join buffer |

+----+-------------+-------+---+------+---------------+------+---------+------+------+--------------------------------+

网友答案:

This query is not conceptually identical to yours, but maybe you want something like this? I am quite sure it will give you the same result as yours:

select
  *
from
  company1 inner join company2
  on company1.manufacturer = company2.manufacturer
where
  company2.model LIKE CONCAT(company1.model,'%')

EDIT: i also removed your left join and put an inner join. If the join doesn't succeed, company2.model is always null and NULL LIKE 'Something%' can never be true.

网友答案:

One way to speed this up is to remove the LIKE CONCAT() from the join condition.

MySQL is not able to use an index for substring based searches like that, so your query results in a full table scan.

网友答案:

Your EXPLAIN shows that you have no indexes that can be used.

Appropriate indexes on both tables would help. Either a single index on (manufacturer) or a composite (manufacturer, model):

ALTER TABLE company1
  ADD INDEX manufacturer_model_IDX       --- this is just a name (of your choice)
    (manufacturer, model) ;              --- for the index

ALTER TABLE company2
  ADD INDEX manufacturer_model_IDX
    (manufacturer, model) ;
分享给朋友:
您可能感兴趣的文章:
随机阅读: