Question 21580 in Mysql Question posted in
The official MySQL documentation can be found here.

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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search