How to speed up slow query using GROUP BY and CASE?

I'm not very experienced with MySQL and and trying to figure out how to speed up a slow query using GROUP BY and CASE statements.

CASE 1 -- GROUP BY and CASE

SELECT SQL_NO_CACHE m.id, m.sku, m.movie_url 
FROM movie.movies m               
WHERE m.s_id = 1                   
GROUP BY m.mg_id, CASE WHEN m.mg_id IS NULL THEN m.id ELSE 0 END
LIMIT 100

Execution times: 1.025s, 1.042s, 0.946s

Explain output:

+----+-------------+-------+------+--------------------+----------+---------+-------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys      | key      | key_len | ref   | rows  | Extra                                        |
+----+-------------+-------+------+--------------------+----------+---------+-------+-------+----------------------------------------------+
|  1 | SIMPLE      | m     | ref  | ind_mg_id,ind_s_id | ind_s_id | 4       | const | 39941 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------+--------------------+----------+---------+-------+-------+----------------------------------------------+

CASE 2 -- GROUP BY without CASE

I realized the CASE statement in the GROUP BY might slow things down, so I tried removing it for testing purposes (it has to be there for production use, though):

SELECT SQL_NO_CACHE m.id, m.sku, m.movie_url 
FROM movie.movies m               
WHERE m.s_id = 1                   
GROUP BY m.mg_id#, CASE WHEN m.mg_id IS NULL THEN m.id ELSE 0 END
LIMIT 100

Execution times: 0.258s, 0.149s, 0.193s

Explain output:

```
+----+-------------+-------+-------+--------------------+-----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+--------------------+-----------+---------+------+------+-------------+
| 1 | SIMPLE | m | index | ind_mg_id,ind_s_id | ind_mg_i

Please create the following index

ALTER TABLE movie.movies ADD INDEX sid_mgid_ndx (s_id,mg_id);

This index will help the WHERE and GROUP BY

Perhaps a different index would help

ALTER TABLE movie.movies
    DROP INDEX sid_mgid_ndx,
    ADD INDEX sid_mgid_id_ndx (s_id,mg_id,id)
;

and adjust the query

SELECT SQL_NO_CACHE m.id, m.sku, m.movie_url 
FROM movie.movies m               
WHERE m.s_id = 1                   
ORDER BY m.mg_id, m.id;
LIMIT 100;

I figured you should change the GROUP BY to ORDER BY because you are not doing any actual aggregation (i.e., you not doing SUM(), COUNT(), AVG(), or any other summations)

StackExchange Database Administrators Q#86056, answer score: 2