5

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

EXPLAIN: Query #1 EXPLAIN

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

EXPLAIN: enter image description here

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 and GROUP 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

6
  • Firstly, please don't use screenshots - use formatted text instead. Secondly, could you give us the output of SHOW CREATE TABLE blah for your tables. Have you tried without STRAIGHT_JOIN? – Vérace Oct 28 '19 at 4:32
  • So I've been continuing to work on this and I believe it's 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 and GROUP BY expressions." – Dan Oct 28 '19 at 4:39
  • My actual original query is not nearly as simple as the example provided. 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: stackoverflow.com/questions/7381828/… – Dan Oct 28 '19 at 4:42
  • Addendum: 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 does not apply because the GROUP BY is being ignored due to only 1 table being selected from – Dan Oct 28 '19 at 4:48
  • Could you put extra/new information into the question using the edit option - makes life much easier for those trying to help. – Vérace Oct 28 '19 at 5:47
1

Query 1: Since id is the PRIMARY KEY, it is unique. Hence the GROUP BY id does nothing. Remove it. This may make it run faster.

Query 2 does not use any columns other than id from car_makes. The only thing that the JOIN does is to verify that there is a row in car_makes for the make_id. You probably don't need that check, so get rid of car_makesin that query. That will simplify things. Note that currently there is a "filesort". Withoutcar_makes`, that step will probably go away.

As for "why can't it use the same index" -- The STRAIGHT_JOIN forces it to look at the other table first. This effectively turns the second table into

WHERE     make_id = ...
GROUP BY  id
ORDER BY  horsepower_peak DESC

To optimize such, it must first filter on make_id.

5
  • (I added an answer to your first question.) – Rick James Aug 29 '20 at 22:38
  • I thought the point of the STRAIGHT_JOIN was to force the optimizer to look at the left table first? – Dan Aug 31 '20 at 2:21
  • @Dan - Yes, that is the point of STRAIGHT_JOIN. But that is preventing it from using the given index on trims. For that query, it would be better to find the 200 rows, then JOIN; STRAIGHT_JOIN prevents such. Look at "Rows" in the two EXPLAINs. – Rick James Aug 31 '20 at 3:02
  • As in create a temporary table using Query #1 and then joining car_makes on that temporary table to get the result set that I want? (Again, ultimately I want to be able to get GROUP_CONCAT values from car_makes) – Dan Sep 1 '20 at 1:14
  • Group_concat? That will require a separate analysis. Start another Question, show a short sample of data, plus the desired output. (And repeat the CREATE TABLEs) – Rick James Sep 1 '20 at 4:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.