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

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

Expected Result

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

CodePudding user response：

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.