MySQL query optimization

Question

Could you please help me optimize this query. I've spent lots of time and still cannot rephrase it to be fast enough (say running in the matters of seconds, not minutes as it is now).

The query:

SELECT m.my_id, m.my_value, m.my_timestamp
  FROM (
    SELECT my_id, MAX(my_timestamp) AS most_recent_timestamp
      FROM my_table
      WHERE my_timestamp < '2011-03-01 08:00:00'
      GROUP BY my_id
  ) as tmp
LEFT OUTER JOIN my_table m
ON tmp.my_id = m.my_id AND tmp.most_recent_timestamp = m.my_timestamp
ORDER BY m.my_timestamp;

my_table is defined as follows:

CREATE TABLE my_table (
   my_id INTEGER NOT NULL,
   my_value VARCHAR(4000),
   my_timestamp TIMESTAMP default CURRENT_TIMESTAMP NOT NULL,
   INDEX MY_ID_IDX (my_id),
   INDEX MY_TIMESTAMP_IDX (my_timestamp),
   INDEX MY_ID_MY_TIMESTAMP_IDX (my_id, my_timestamp)
);

The goal of this query is to select the most recent my_value for each my_idbefore some timestamp. my_table contains ~100 million entries and it takes ~8 minutes to perform it.

explain:

+----+-------------+-------------+-------+------------------------------------------------+-------------------------+---------+---------------------------+-------+---------------------------------------+
| id | select_type | table       | type  | possible_keys                                  | key                     | key_len | ref                       | rows  | Extra                                 |
+----+-------------+-------------+-------+------------------------------------------------+-------------------------+---------+---------------------------+-------+---------------------------------------+
|  1 | PRIMARY     | <derived2>  | ALL   | NULL                                           | NULL                    | NULL    | NULL                      | 90721 | Using temporary; Using filesort       |
|  1 | PRIMARY     | m          | ref   | MY_ID_IDX,MY_TIMESTAMP_IDX,MY_ID_TIMESTAMP_IDX | MY_TIMESTAMP_IDX        | 4       | tmp.most_recent_timestamp |    1  | Using where                           |
|  2 | DERIVED     | my_table    | range | MY_TIMESTAMP_IDX                               | MY_ID_MY_TIMESTAMP_IDX  | 8       | NULL                      | 61337 | Using where; Using index for group-by |
+----+-------------+-------------+-------+------------------------------------------------+-----------------------+---------+---------------------------+------+---------------------------------------+

Answer 1

If I understand correctly, you should be able to drop the nested select completely, and move the where clause to the main query, order by my_timestamp descending and limit 1.

SELECT my_id, my_value, max(my_timestamp)
FROM my_table
WHERE my_timestamp < '2011-03-01 08:00:00'
GROUP BY my_id

*edit - added max and group by

Answer 2

I notice in the explain plan that the optimizer is using the MY_ID_MY_TIMESTAMP_IDX index for the sub-query, but not the outer query.

You may be able to speed it up using an index hint. I also updated the ON clause to refer to tmp.most_recent_timestamp using its alias.

SELECT m.my_id, m.my_value, m.my_timestamp
  FROM (
    SELECT my_id, MAX(my_timestamp) AS most_recent_timestamp
      FROM my_table
      WHERE my_timestamp < '2011-03-01 08:00:00'
      GROUP BY my_id
  ) as tmp
LEFT OUTER JOIN my_table m use index (MY_ID_MY_TIMESTAMP_IDX)
ON tmp.my_id = m.my_id AND tmp.most_recent_timestamp = m.my_timestamp
ORDER BY m.my_timestamp;

Answer 3

a trick to get a most recent record can be to use order by together with 'limit 1' instead of max aggregation together with "self" join

somthing like this (not tested):

SELECT m.my_id, m.my_value, m.my_timestamp
FROM my_table m
WHERE my_timestamp < '2011-03-01 08:00:00'
ORDER BY m.my_timestamp DESC
LIMIT 1
;

update above doesn't work because a grouping is required...
other solution that has WHERE-IN-SubSelect instead of the JOIN you've used.
could be faster. please test with your data.

SELECT m.my_id, m.my_value, m.my_timestamp
FROM my_table m
WHERE ( m.my_id, m.my_timestamp ) IN (
  SELECT i.my_id, MAX(i.my_timestamp)
  FROM my_table i
  WHERE i.my_timestamp < '2011-03-01 08:00:00'
  GROUP BY i.my_id
  )
ORDER BY m.my_timestamp;