Question
Why can't Query #2 use the same (car_trims.horsepower_peak)
index to optimize the sorting of the rows as Query #1? The only difference between the two queries is the addition of the JOIN
in Query #2.
car_trims ~50k rows
PK: (car_trims.id)
, index on (car_trims.horsepower_peak)
car_makes ~100 rows
PK: (car_makes.id)
Query #1
SELECT car_trims.*
FROM car_trims
GROUP BY car_trims.id
ORDER BY car_trims.horsepower_peak DESC
LIMIT 0, 200
Execution time: .0026 seconds
Query #2
SELECT car_trims.*
FROM car_trims
STRAIGHT_JOIN car_makes ON car_makes.id = car_trims.make_id
GROUP BY car_trims.id
ORDER BY car_trims.horsepower_peak DESC
LIMIT 0, 200
Execution time: .2533 seconds
UPDATE:
I've been continuing to work on this and I believe the index is not being utilized in Query #2 because of the mixing of GROUP BY
and ORDER BY
. According to the MySQL Docs,
"In some cases, MySQL cannot use indexes to resolve the ORDER BY ... [for example, when] ... the query has different
ORDER BY
andGROUP BY
expressions."
Query #1 does mix GROUP BY
and ORDER BY
and so theoretically the index should not be used according to the docs but I believe that may not apply if the GROUP BY
is being ignored entirely due to only 1 table being selected from and grouping on the primary key.
Also, my actual original query is not quite as simple as the example provided here. The crucial difference: usage of GROUP_CONCAT
in the SELECT
requiring the aforementioned GROUP BY
in order to prevent grouping on all rows (i.e. getting a 1-row result). The solution to that issue is using a DEPENDENT SUBQUERY
, as discussed here: https://stackoverflow.com/questions/7381828/indexing-with-group-by-order-by-and-group-concat
SHOW CREATE TABLE blah
for your tables. Have you tried withoutSTRAIGHT_JOIN
? – Vérace Oct 28 '19 at 4:32GROUP BY
andORDER BY
. According to the MySQL Docs, "In some cases, MySQL cannot use indexes to resolve the ORDER BY ... [for example, when] ... the query has different ORDER BY and GROUP BY expressions." – Dan Oct 28 '19 at 4:39GROUP_CONCAT
in theSELECT
requiring the aforementionedGROUP BY
in order to prevent grouping on all rows (i.e. getting a 1-row result). The solution to that issue is using aDEPENDENT SUBQUERY
, as discussed here: stackoverflow.com/questions/7381828/… – Dan Oct 28 '19 at 4:42GROUP BY
andORDER BY
and so theoretically the index should not be used according to the docs but I believe that does not apply because theGROUP BY
is being ignored due to only 1 table being selected from – Dan Oct 28 '19 at 4:48