Mysql MAX and GROUP BY very slow

Question

I'm trying to figure our why my query is very slow using MAX, Where and GROUP by.

Table size: 2246096 rows

Indexes:

created_at_machine_id_idx(created_at,machine_id)

Query:

select
     MAX(id) AS id
    ,machine_id
FROM `table`
WHERE `table`.`machine_id` IN (30, 31, 43, 44, 46, 50, 51, 53, 55, 56...)
AND `created_at` <= '2021-11-14 07:45:00'
GROUP BY `machine_id`;

Explained:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	table	range	created_at_machine_id_idx	created_at_machine_id_idx	13	NULL	1123048	Using where; Using index; Using temporary; Using filesort

It takes now between 3 and 4 seconds. I tried more combinations of indexes but without any luck.

Basically I want to get the last id for each machine lower than 2021-11-14 07:45:00.

id is Primary key.

How many machine_id in the IN? – P.Salmon Nov 15 '21 at 07:56 — P.Salmon, Nov 15 '21 at 07:56

score 0 · Answer 1 · answered Nov 14 '21 at 18:04

0

I would expect this to help it run faster:

INDEX(machine_id, created_at, id)

answered Nov 14 '21 at 18:04

Rick James

135,179
13
127
222

@radu paraleste Has your performance improved? – Wilson Hauck Nov 15 '21 at 01:45
hello all. sorry I was out of the city for few days. Unfortunately no. but I found another stackoverflow question that solved my issue. this one: https://stackoverflow.com/questions/21439342/improving-speed-of-sql-query-with-max-where-and-group-by-on-three-different-co . Thank you – radu paraleste Nov 16 '21 at 16:33
@raduparaleste - That other SO query is not identical to yours. Please state which index workers best for your query. Use `EXPLAIN SELECT` to discover it. And did you use a "self join" like they did? Also, to use their query, you have to assume that ids are in `created_at` order. While that may happen to be true, it is not necessarily the case. – Rick James Nov 16 '21 at 17:14

Mysql MAX and GROUP BY very slow

1 Answers1