HeadwindFly HeadwindFly - 5 months ago 20
MySQL Question

How to optimize sql of left join?

I have two table phone and phone_area

phone cloumns:



  1. id: pk

  2. phone: unique index




phone data:

|id| phone |
+--+-------------+
|1 | 1882601xxxx |
+--+-------------+
|2 | 1882602xxxx |
+--+-------------+
|2 | 1882602xxxx |
+--+-------------+
|2 | 1882603xxxx |
+--+-------------+


phone_area cloumns:


1.id: pk

2.number: unique index

3.area: varchar(20)


phone_area data:

|id| phone | area |
+--+--------+----------+
|1 | 1882601| area_one|
+--+--------+----------+
|2 | 1882602| area_two|
+--+--------+----------+
|2 | 1882603|area_three|
+--+--------+----------+


My sql following:

SELECT t1.phone,t2.area FROM phone t1
LEFT JOIN phone_area t2 ON substr(t1.phone, 1, 7)= t2.phone


It is very slowly.

When explain the sql, it shows the type as "ALL" and Using where; Using join buffer (Block Nested Loop)

How to improve my sql?

Answer

Follow this steps:

ALTER TABLE phone ADD phone_cut int; --add a new column

UPDATE phone SET phone_cut = substr(phone, 1, 7); --store the cropped value of phone

ALTER TABLE phone ADD INDEX ind_name (phone_cut); -- add an index on that column

And then :

SELECT t1.phone,t2.area FROM phone t1
LEFT JOIN phone_area t2
 ON t1.phone_cut= t2.phone