I am using mysql. And create index on 'playCount' 'desc' in table D. However, it did not apply. So, I create index on 'aId ASC, playCount DESC' in table D. But, it did not apply too.

Order by is so slow, please tell me how to create an index on my code.

explain SELECT `A`.`id` AS `id`, `A`.`title` AS `title`, `A`.`img` AS `img`
FROM `A` `A` 
INNER JOIN `B` `B` ON `B`.`aId`=`A`.`id`  
INNER JOIN `C` `C` ON `C`.`id`=`B`.`cId`  
LEFT JOIN `D` `D` ON `D`.`aId`=`A`.`id` 
GROUP BY `A`.`id` 
ORDER BY `D`.`playCount` DESC 
LIMIT 10;

CodePudding user response：

There may be at least 2 reasons why the ORDER BY may be ignored.

That query will be performed this way:

1. Join together all valid combinations (based on the ONs) of rows among those tables. This generates a potentially large temp table. This temp table will include a bunch of columns -- title, etc.
2. Perform the GROUP BY. This probably requires sorting the temp table above. This may shrink back down to a smaller temp table. Since this GROUP BY must be performed before the ORDER BY, no index relating to the ORDER BY can help.
3. Sort (again) to get the desired ORDER BY.
4. Deliver the first 10 rows. This effectively tosses any bulky thing (title?) (except for the first 10) that had been carried around since step 1.

If there were a WHERE clause, the addition of an INDEX might help.

INDEX(aId ASC, playCount DESC) -- Well, I need to ask what version of MySQL you are using. Mixing ASC and DESC has always been allowed, and the sorting has always worked correctly. But DESC has been ignored in the index until version 8.0. (Still, as I have already pointed out, the index cannot be used.)

If you want to discuss this further, please provide SHOW CREATE TABLE for each table, EXPLAIN SELECT ..., the approximate size of each table, and whether the tables are related 1:1 or many:many or many:1.