I have a problem executing a simple LEFT JOIN.
When I run EXPLAIN SELECT * FROM Feature f LEFT JOIN feature_translations t ON f.id = t.object_id I got the following result:
+----+-------------+-------+------+---------------------------+------+---------+------+--------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------------------+------+---------+------+--------+----------------------------------------------------+
| 1 | SIMPLE | f | ALL | NULL | NULL | NULL | NULL | 249200 | NULL |
| 1 | SIMPLE | t | ALL | IDX_7ED26C14232D562B,olol | NULL | NULL | NULL | 1 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------------------+------+---------+------+--------+----------------------------------------------------+
Here I can see that the Join is not using the existing index !!! If I DELETE almost all rows then the index is used. This is why I think is just a configuration variable but I don't know which one !
Following my tables structures.
CREATE TABLE IF NOT EXISTS `Feature` (
`id` int(11) NOT NULL,
`site_id` int(11) NOT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`clientId` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`signature` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=1866705 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE `Feature`
ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `UNIQ_D06A4CFAF6BD1646AE880141` (`site_id`,`signature`), ADD KEY `IDX_D06A4CFAF6BD1646` (`site_id`), ADD KEY `IDX_D06A4CFAAE880141F6BD1646` (`signature`,`site_id`), ADD KEY `IDX_D06A4CFAEA1CE9BEF6BD1646` (`clientId`,`site_id`);
CREATE TABLE IF NOT EXISTS `feature_translations` (
`id` int(11) NOT NULL,
`object_id` int(11) DEFAULT NULL,
`locale` varchar(8) COLLATE utf8_unicode_ci NOT NULL,
`field` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
`content` longtext COLLATE utf8_unicode_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
ALTER TABLE `feature_translations`
ADD PRIMARY KEY (`id`), ADD KEY `IDX_7ED26C14232D562B` (`object_id`), ADD KEY `feature_translation_idx` (`locale`,`field`,`object_id`);
ALTER TABLE `feature_translations`
ADD CONSTRAINT `FK_7ED26C14232D562B` FOREIGN KEY (`object_id`) REFERENCES `Feature` (`id`) ON DELETE CASCADE;
Thank You !
EDIT
Thank for your reply !
The think is that this request come from a bigger one. I'm using Doctrine and I try to use a big join on my used table. This work fine but there is 3 tables that reduce drastically my performances. Using a join in that case has lower performance than multiple requests.
The request I wanted to run is
SELECT * FROM Product p1_ INNER JOIN Poi p0_ ON p1_.id = p0_.id INNER JOIN Site s42_ ON p0_.site_id = s42_.id LEFT JOIN PoiLogoLink p2_ ON p0_.id = p2_.poi_id LEFT JOIN File f3_ ON p2_.file_id = f3_.id LEFT JOIN PoiPictureLink p4_ ON p0_.id = p4_.poi_id LEFT JOIN File f5_ ON p4_.file_id = f5_.id LEFT JOIN poi_translations p6_ ON p0_.id = p6_.object_id LEFT JOIN CustomObject c7_ ON p0_.id = c7_.poi_id LEFT JOIN Picto p8_ ON c7_.id = p8_.id LEFT JOIN Label l9_ ON c7_.id = l9_.id LEFT JOIN custom_objects_translations c10_ ON c7_.id = c10_.object_id LEFT JOIN category_poi c43_ ON p0_.id = c43_.poi_id LEFT JOIN Category c11_ ON c11_.id = c43_.category_id LEFT JOIN category_translations c12_ ON c11_.id = c12_.object_id LEFT JOIN place_poi p44_ ON p0_.id = p44_.poi_id LEFT JOIN Place p13_ ON p13_.id = p44_.place_id LEFT JOIN PoiLink p14_ ON p0_.id = p14_.childId LEFT JOIN Poi p15_ ON p14_.parentId = p15_.id LEFT JOIN Person p16_ ON p15_.id = p16_.id LEFT JOIN Store s17_ ON p15_.id = s17_.id LEFT JOIN Product p18_ ON p15_.id = p18_.id LEFT JOIN Exhibitor e19_ ON p15_.id = e19_.id LEFT JOIN Room r20_ ON p15_.id = r20_.id LEFT JOIN Service s21_ ON p15_.id = s21_.id LEFT JOIN PoiLink p22_ ON p0_.id = p22_.parentId LEFT JOIN Poi p23_ ON p22_.childId = p23_.id LEFT JOIN Person p24_ ON p23_.id = p24_.id LEFT JOIN Store s25_ ON p23_.id = s25_.id LEFT JOIN Product p26_ ON p23_.id = p26_.id LEFT JOIN Exhibitor e27_ ON p23_.id = e27_.id LEFT JOIN Room r28_ ON p23_.id = r28_.id LEFT JOIN Service s29_ ON p23_.id = s29_.id LEFT JOIN poi_translations p30_ ON p23_.id = p30_.object_id LEFT JOIN PoiMediaLink p31_ ON p0_.id = p31_.poi_id LEFT JOIN Media m32_ ON p31_.media_id = m32_.id LEFT JOIN MediaText m33_ ON m32_.id = m33_.id LEFT JOIN MediaImage m34_ ON m32_.id = m34_.id LEFT JOIN MediaVideo m35_ ON m32_.id = m35_.id LEFT JOIN MediaPdf m36_ ON m32_.id = m36_.id LEFT JOIN Movie m37_ ON m32_.id = m37_.id LEFT JOIN MediaUrl m38_ ON m32_.id = m38_.id LEFT JOIN media_translations m39_ ON m32_.id = m39_.object_id LEFT JOIN product_feature p45_ ON p1_.id = p45_.product_id LEFT JOIN Feature f40_ ON f40_.id = p45_.feature_id LEFT JOIN feature_translations f41_ ON f40_.id = f41_.object_id WHERE s42_.id IN (15) ORDER BY p0_.id ASC LIMIT 100 OFFSET 0;
100 rows in set (15,43 sec)
+----+-------------+-------+--------+------------------------------------------------------------+----------------------+---------+------------------------+------ +----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+------------------------------------------------------------+----------------------+---------+------------------------+------+----------------------------------------------------+
| 1 | SIMPLE | s42_ | const | PRIMARY | PRIMARY | 4 | const | 1 | Using temporary; Using filesort |
| 1 | SIMPLE | p0_ | ref | PRIMARY,UNIQ_45F65936F6BD1646AE880141,IDX_45F65936F6BD1646 | IDX_45F65936F6BD1646 | 4 | const | 8875 | NULL |
| 1 | SIMPLE | p1_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p0_.id | 1 | NULL |
| 1 | SIMPLE | p2_ | ref | IDX_7990D9C97EACE855 | IDX_7990D9C97EACE855 | 4 | adsum.p0_.id | 1 | NULL |
| 1 | SIMPLE | f3_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p2_.file_id | 1 | NULL |
| 1 | SIMPLE | p4_ | ref | IDX_6CC279957EACE855 | IDX_6CC279957EACE855 | 4 | adsum.p0_.id | 1 | NULL |
| 1 | SIMPLE | f5_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p4_.file_id | 1 | NULL |
| 1 | SIMPLE | p6_ | ref | IDX_9478F0F7232D562B | IDX_9478F0F7232D562B | 5 | adsum.p0_.id | 1 | NULL |
| 1 | SIMPLE | c7_ | ref | IDX_FE0561D77EACE855 | IDX_FE0561D77EACE855 | 5 | adsum.p0_.id | 1 | NULL |
| 1 | SIMPLE | p8_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.c7_.id | 1 | NULL |
| 1 | SIMPLE | l9_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.c7_.id | 1 | NULL |
| 1 | SIMPLE | c10_ | ref | IDX_A653A52A232D562B | IDX_A653A52A232D562B | 5 | adsum.c7_.id | 1 | NULL |
| 1 | SIMPLE | c43_ | ref | IDX_5879B28C7EACE855 | IDX_5879B28C7EACE855 | 4 | adsum.p0_.id | 1 | Using index |
| 1 | SIMPLE | c11_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.c43_.category_id | 1 | NULL |
| 1 | SIMPLE | c12_ | ref | IDX_1C60F915232D562B | IDX_1C60F915232D562B | 5 | adsum.c11_.id | 1 | NULL |
| 1 | SIMPLE | p44_ | ref | IDX_720979277EACE855 | IDX_720979277EACE855 | 4 | adsum.p0_.id | 1 | Using index |
| 1 | SIMPLE | p13_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p44_.place_id | 1 | NULL |
| 1 | SIMPLE | p14_ | ref | IDX_64E377A92FD6B47 | IDX_64E377A92FD6B47 | 5 | adsum.p0_.id | 4 | NULL |
| 1 | SIMPLE | p15_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p14_.parentId | 1 | NULL |
| 1 | SIMPLE | p16_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p15_.id | 1 | NULL |
| 1 | SIMPLE | s17_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p15_.id | 1 | NULL |
| 1 | SIMPLE | p18_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p15_.id | 1 | NULL |
| 1 | SIMPLE | e19_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p15_.id | 1 | NULL |
| 1 | SIMPLE | r20_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p15_.id | 1 | NULL |
| 1 | SIMPLE | s21_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p15_.id | 1 | NULL |
| 1 | SIMPLE | p22_ | ref | IDX_64E377A910EE4CEE | IDX_64E377A910EE4CEE | 5 | adsum.p0_.id | 2 | NULL |
| 1 | SIMPLE | p23_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p22_.childId | 1 | NULL |
| 1 | SIMPLE | p24_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p23_.id | 1 | NULL |
| 1 | SIMPLE | s25_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p23_.id | 1 | NULL |
| 1 | SIMPLE | p26_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p23_.id | 1 | NULL |
| 1 | SIMPLE | e27_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p23_.id | 1 | NULL |
| 1 | SIMPLE | r28_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p23_.id | 1 | NULL |
| 1 | SIMPLE | s29_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p23_.id | 1 | NULL |
| 1 | SIMPLE | p30_ | ref | IDX_9478F0F7232D562B | IDX_9478F0F7232D562B | 5 | adsum.p23_.id | 1 | NULL |
| 1 | SIMPLE | p31_ | ref | IDX_5D2C1AE27EACE855 | IDX_5D2C1AE27EACE855 | 4 | adsum.p0_.id | 1 | NULL |
| 1 | SIMPLE | m32_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p31_.media_id | 1 | NULL |
| 1 | SIMPLE | m33_ | ALL | PRIMARY | NULL | NULL | NULL | 1 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | m34_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.m32_.id | 1 | NULL |
| 1 | SIMPLE | m35_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.m32_.id | 1 | NULL |
| 1 | SIMPLE | m36_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.m32_.id | 1 | NULL |
| 1 | SIMPLE | m37_ | ALL | PRIMARY | NULL | NULL | NULL | 1 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | m38_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.m32_.id | 1 | NULL |
| 1 | SIMPLE | m39_ | ref | IDX_AF46700B232D562B | IDX_AF46700B232D562B | 5 | adsum.m32_.id | 1 | NULL |
| 1 | SIMPLE | p45_ | ref | PRIMARY,IDX_CE0E6ED64584665A | IDX_CE0E6ED64584665A | 4 | adsum.p0_.id | 2 | Using index |
| 1 | SIMPLE | f40_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p45_.feature_id | 1 | NULL |
| 1 | SIMPLE | f41_ | ALL | IDX_7ED26C14232D562B | NULL | NULL | NULL | 1 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+--------+------------------------------------------------------------+----------------------+---------+------------------------+------+----------------------------------------------------+
But if I removed tables not using keys
SELECT * FROM Product p1_ INNER JOIN Poi p0_ ON p1_.id = p0_.id INNER JOIN Site s31_ ON p0_.site_id = s31_.id LEFT JOIN PoiLogoLink p2_ ON p0_.id = p2_.poi_id LEFT JOIN File f3_ ON p2_.file_id = f3_.id LEFT JOIN PoiPictureLink p4_ ON p0_.id = p4_.poi_id LEFT JOIN File f5_ ON p4_.file_id = f5_.id LEFT JOIN poi_translations p6_ ON p0_.id = p6_.object_id LEFT JOIN CustomObject c7_ ON p0_.id = c7_.poi_id LEFT JOIN Picto p8_ ON c7_.id = p8_.id LEFT JOIN Label l9_ ON c7_.id = l9_.id LEFT JOIN custom_objects_translations c10_ ON c7_.id = c10_.object_id LEFT JOIN category_poi c32_ ON p0_.id = c32_.poi_id LEFT JOIN Category c11_ ON c11_.id = c32_.category_id LEFT JOIN category_translations c12_ ON c11_.id = c12_.object_id LEFT JOIN place_poi p33_ ON p0_.id = p33_.poi_id LEFT JOIN Place p13_ ON p13_.id = p33_.place_id LEFT JOIN PoiLink p14_ ON p0_.id = p14_.childId LEFT JOIN Poi p15_ ON p14_.parentId = p15_.id LEFT JOIN Person p16_ ON p15_.id = p16_.id LEFT JOIN Store s17_ ON p15_.id = s17_.id LEFT JOIN Product p18_ ON p15_.id = p18_.id LEFT JOIN Exhibitor e19_ ON p15_.id = e19_.id LEFT JOIN Room r20_ ON p15_.id = r20_.id LEFT JOIN Service s21_ ON p15_.id = s21_.id LEFT JOIN PoiLink p22_ ON p0_.id = p22_.parentId LEFT JOIN Poi p23_ ON p22_.childId = p23_.id LEFT JOIN Person p24_ ON p23_.id = p24_.id LEFT JOIN Store s25_ ON p23_.id = s25_.id LEFT JOIN Product p26_ ON p23_.id = p26_.id LEFT JOIN Exhibitor e27_ ON p23_.id = e27_.id LEFT JOIN Room r28_ ON p23_.id = r28_.id LEFT JOIN Service s29_ ON p23_.id = s29_.id LEFT JOIN poi_translations p30_ ON p23_.id = p30_.object_id WHERE s31_.id IN (15) ORDER BY p0_.id ASC LIMIT 100 OFFSET 0;
100 rows in set (0,02 sec)
+----+-------------+-------+--------+------------------------------------------------------------+----------------------+---------+------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+------------------------------------------------------------+----------------------+---------+------------------------+------+-------------+
| 1 | SIMPLE | s31_ | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
| 1 | SIMPLE | p0_ | ref | PRIMARY,UNIQ_45F65936F6BD1646AE880141,IDX_45F65936F6BD1646 | IDX_45F65936F6BD1646 | 4 | const | 8875 | Using where |
| 1 | SIMPLE | p1_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p0_.id | 1 | NULL |
| 1 | SIMPLE | p2_ | ref | IDX_7990D9C97EACE855 | IDX_7990D9C97EACE855 | 4 | adsum.p0_.id | 1 | NULL |
| 1 | SIMPLE | f3_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p2_.file_id | 1 | NULL |
| 1 | SIMPLE | p4_ | ref | IDX_6CC279957EACE855 | IDX_6CC279957EACE855 | 4 | adsum.p0_.id | 1 | NULL |
| 1 | SIMPLE | f5_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p4_.file_id | 1 | NULL |
| 1 | SIMPLE | p6_ | ref | IDX_9478F0F7232D562B | IDX_9478F0F7232D562B | 5 | adsum.p0_.id | 1 | NULL |
| 1 | SIMPLE | c7_ | ref | IDX_FE0561D77EACE855 | IDX_FE0561D77EACE855 | 5 | adsum.p0_.id | 1 | NULL |
| 1 | SIMPLE | p8_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.c7_.id | 1 | NULL |
| 1 | SIMPLE | l9_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.c7_.id | 1 | NULL |
| 1 | SIMPLE | c10_ | ref | IDX_A653A52A232D562B | IDX_A653A52A232D562B | 5 | adsum.c7_.id | 1 | NULL |
| 1 | SIMPLE | c32_ | ref | IDX_5879B28C7EACE855 | IDX_5879B28C7EACE855 | 4 | adsum.p0_.id | 1 | Using index |
| 1 | SIMPLE | c11_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.c32_.category_id | 1 | NULL |
| 1 | SIMPLE | c12_ | ref | IDX_1C60F915232D562B | IDX_1C60F915232D562B | 5 | adsum.c11_.id | 1 | NULL |
| 1 | SIMPLE | p33_ | ref | IDX_720979277EACE855 | IDX_720979277EACE855 | 4 | adsum.p0_.id | 1 | Using index |
| 1 | SIMPLE | p13_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p33_.place_id | 1 | NULL |
| 1 | SIMPLE | p14_ | ref | IDX_64E377A92FD6B47 | IDX_64E377A92FD6B47 | 5 | adsum.p0_.id | 4 | NULL |
| 1 | SIMPLE | p15_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p14_.parentId | 1 | NULL |
| 1 | SIMPLE | p16_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p15_.id | 1 | NULL |
| 1 | SIMPLE | s17_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p15_.id | 1 | NULL |
| 1 | SIMPLE | p18_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p15_.id | 1 | NULL |
| 1 | SIMPLE | e19_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p15_.id | 1 | NULL |
| 1 | SIMPLE | r20_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p15_.id | 1 | NULL |
| 1 | SIMPLE | s21_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p15_.id | 1 | NULL |
| 1 | SIMPLE | p22_ | ref | IDX_64E377A910EE4CEE | IDX_64E377A910EE4CEE | 5 | adsum.p0_.id | 2 | NULL |
| 1 | SIMPLE | p23_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p22_.childId | 1 | NULL |
| 1 | SIMPLE | p24_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p23_.id | 1 | NULL |
| 1 | SIMPLE | s25_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p23_.id | 1 | NULL |
| 1 | SIMPLE | p26_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p23_.id | 1 | NULL |
| 1 | SIMPLE | e27_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p23_.id | 1 | NULL |
| 1 | SIMPLE | r28_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p23_.id | 1 | NULL |
| 1 | SIMPLE | s29_ | eq_ref | PRIMARY | PRIMARY | 4 | adsum.p23_.id | 1 | NULL |
| 1 | SIMPLE | p30_ | ref | IDX_9478F0F7232D562B | IDX_9478F0F7232D562B | 5 | adsum.p23_.id | 1 | NULL |
+----+-------------+-------+--------+------------------------------------------------------------+----------------------+---------+------------------------+------+-------------+
Here I think I have a really bad thing in my first request
The way LEFT JOIN is executed in MySQL is by executing two nested loops: in the outer loop it traverses the left table, and in the inner loop it traverses the inner table. If inner table has many rows, and there's an index on the columns from the ON condition, it will use that index to speed up the inner loop. Note that index on the outer table cannot help speed up the outer loop because it needs to traverse all the rows of the left table no matter what.
In the explain you provided the right table has only a single row. MySQL smartly concludes that it does not need any index, because full table scan on that table will be just as fast (or even faster due to avoiding overhead associated with using the index) as using the index, so it chooses to perform a full scan of the table (that is, a full scan of a single row) on each iteration.
If your right table had more rows, then MySQL would have chosen to use that index.
Finally note, that if your join is an INNER JOIN, not a LEFT JOIN, MySQL might be smart enough to reverse the order of the tables, and use your feature_translations as the outer table, in which case it will be able to leverage the index on the inner Feature table.