How to optimize MYSQL query?

Question

I want to get latest records of the table with some settings_ids.

id	settings_id	added_date
1	7	2022-08-23 01:44:24
2	9	2022-08-23 01:44:24
3	11	2022-08-23 01:44:24
4	7	2022-08-25 01:44:24
5	9	2022-08-25 01:44:24
6	11	2022-08-25 01:44:24
7	7	2022-08-26 01:44:24
8	9	2022-08-26 01:44:24
9	11	2022-08-26 01:44:24

SELECT id, settings_id, MAX(added_date) 
FROM data_rows 
WHERE settings_id IN (7,9,11) 
GROUP BY settings_id;

Expected Result

id	settings_id	added_date
7	7	2022-08-26 01:44:24
8	9	2022-08-26 01:44:24
9	11	2022-08-26 01:44:24

I am getting the result I want but the thing is it taking more than a minute to get the data.

Is there a way to reduce the time taken by this query?

Thanks

Answer 1

On MySQL 8+, your requirement is easily met using ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY settings_id ORDER BY addedDate DESC) rn
    FROM data_rows 
    WHERE settings_id IN (7, 9, 11)
)

SELECT id, settings_id, addedDate
FROM cte
WHERE rn = 1
ORDER BY settings_id;

As for optimizing the above query, an index on (settings_id, dateAdded DESC) should help:

CREATE INDEX idx ON data_rows (settings_id, dateAdded);

This index, if used, should let MySQL rapidly compute the required row number.

Edit:

On MySQL 5.7, use this query:

SELECT d1.id, d1.settings_id, d1.addedDate
FROM data_rows d1
INNER JOIN
(
    SELECT settings_id, MAX(addedDate) AS maxAddedDate
    FROM data_rows
    WHERE settings_id IN (7, 9, 11)
    GROUP BY settings_id
) d2
    ON d2.settings_id = d1.settings_id AND
       d2.maxAddedDate = d1.addedDate
WHERE
    d1.settings_id IN (7, 9, 11)
ORDER BY
    d1.settings_id;

Use the same index as suggested above.