CODEWITHSUNDEEP
mysqlgreatest-n-per-group
rkrathor
rkrathor

Reputation: 67

How to optimize MYSQL query?

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

Upvotes: 1

Views: 70

Answers (2)

Rick James
Rick James

Reputation: 142278

Be aware that you won't necessarily get the id that matches the MAX. Cf "only_full_group_by".

For this (no id):

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

Simply have INDEX(settings_id, added_date)

For also getting the matching id, see the groupwise max tag. Or ids -- there could be dup dates?

If there are no dup dates (and you want id), then

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

At that point, do you really need id? Consider getting rid of id and changing to

PRIMARY KEY(setting_id, added_date)

(and going back to my first SELECT suggestion)

Do note that a "composite" (multi-column) index is not the same as separate indexes on each column.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

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.

Upvotes: 4

Related Questions