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

Mysql – Query fails due timeout as table holds numerous records

I have following query which returns most recent records (grouped) from a table keeping history of devices; FIELD_A being a serial number; DateFIELD_B being a date, one record a day per device.

Only most recent occurrence per device interests me but it is not necessarily the same date for all devices.

SELECT t1.*
FROM TABLE_A t1
WHERE t1.DateFIELD_B = (SELECT MAX(t2.DateFIELD_B)
    FROM TABLE_A t2
    where t2.FIELD_A = t1.FIELD_A
    group by FIELD_A);

Query works fine as such (when limiting for test to a few specific serials) but fails due a timeout when using full range. Table has 4,75 millions records. Fields are indexed.

Would you have some advice to optimize this ?

Thank you,

Tags:

2

Answers


  1. 0

    For MySQL 5.x:

    SELECT t1.*
FROM table_a t1
JOIN ( SELECT field_a, MAX(t2.datefield_b) datefield_b
       FROM table_a t2
       GROUP BY field_a ) t3 USING (field_a, datefield_b);

    For MySQL 8+:

    WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY field_a ORDER BY datefield_b DESC) rn
    FROM table_a
    )
SELECT *   -- replace with columns list for to remove `rn` column
FROM cte
WHERE rn = 1;

    The index by (field_a, datefield_b) must improve.

    Login or Signup to reply.
  2. 0

    Try

    SELECT t1.*
FROM TABLE_A t1
WHERE t1.DateFIELD_B = (SELECT t2.DateFIELD_B
    FROM TABLE_A t2
    where t2.FIELD_A = t1.FIELD_A
    order by t2.DateFIELD_B desc
    limit 1);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search