CODEWITHSUNDEEP
mysql
user2914583
user2914583

Reputation: 1

MySQL LEFT JOIN optimisation

I have the following query where

The query itself seems to take around 50 seconds to complete. It will usually contain a WHERE clause as it is doing a search through all the possible data in the database. Is there any way this could be improved?

SELECT A.one, A.two, A.three
FROM A
LEFT JOIN B ON ( A.id = B.id ) 
LEFT JOIN C ON ( A.id = C.d ) 
LEFT JOIN D ON ( A.id = D.id ) 
LEFT JOIN E ON ( A.name =  E.name 
AND  E.date <= A.date ) 
ORDER BY A.id ASC

Explain query:

+----+-------------+-------+--------+---------------+----------+---------+-----------+--------+-------------+
| id | select_type | table | type   | possible_keys | key      | key_len | ref       | rows   | Extra       |
+----+-------------+-------+--------+---------------+----------+---------+-----------+--------+-------------+
|  1 | SIMPLE      | A     | index  | NULL          | PRIMARY  | 17      | NULL      | 357752 |             |
|  1 | SIMPLE      | B     | eq_ref | PRIMARY       | PRIMARY  | 17      | db.A.id   |      1 | Using index |
|  1 | SIMPLE      | C     | eq_ref | PRIMARY       | PRIMARY  | 17      | db.A.id   |      1 | Using index |
|  1 | SIMPLE      | D     | eq_ref | PRIMARY       | PRIMARY  | 17      | db.A.id   |      1 | Using index |
|  1 | SIMPLE      | E     | ref    | Name,Date     | Name     | 62      | db.A.name |      1 |             |
+----+-------------+-------+--------+---------------+----------+---------+-----------+--------+-------------+

Upvotes: 0

Views: 46

Answers (1)

calcinai
calcinai

Reputation: 2617

I would recommend replacing the indexes that you have on E - Name and Date with a dual-column index on both, because for that last join, you're effectively selecting from E where name and date match a criteria. Because name is eq, it should be first in the index.

ALTER TABLE `E` ADD INDEX idx_join_optimise (`name`,`date`)

This will let the join select fully use an index.

Also - I assume that this is an example query, but you don't seem to be using B, C or D which is possibly slowing it down.

If the WHERE clause that you mentioned uses values from the other tables, I'd suggest changing them to an INNER JOIN based on the criteria.. (It'd help if you posted some example of what you were doing)

Upvotes: 1

Related Questions