1

I have a table

create table test
(
  id int auto_increment,
  createdAt TIMESTAMP not null,
  primary key (id)
);
create index createdAt
  on test (createdAt);

And here is my query

SELECT MAX(id) FROM test WHERE DATE(createdAt) <= '2020-02-07';

Explain says Using where; Using index But slow query log says 1 - 10+ seconds.

Important notice: Old rows with createdAt < NOW() - 1 MONTH periodically removed by separate cron job. Average rows count 100k-10m.

As I understand, MAX() function compares each row, so results are so volatile.

Question: Is there any possible optimizations for such aggregation function?

2
  • Can you share your full EXPLAIN result?
    – watery
    Feb 7, 2020 at 15:28
  • 1
    does id column have the same order as createAt? or is it possible that greater id has less createAt value? Feb 7, 2020 at 16:52

3 Answers 3

1

DATE(), not MAX() is the villain...

DATE(createdAt) <= '2020-02-07' is not "sargable".

This has the equivalent effect, and can use INDEX(created_at): created_at <= '2020-02-07'. Yes, that works for datatypesDATE,DATETIME, andTIMESTAMP`.

1
  • That is correct, but does not really affect a query speed. What I made - is additional condition that minimized a number of rows, so execution time became acceptable for me. Feb 25, 2020 at 10:23
0

You're using a function, DATE(), in your WHERE, this prevents the database to use the index for the WHERE step, I guess it is using the index for the MAX() step.

Create an additional column with the result of DATE(createdAt), and keep it updated with a trigger; then add the index to this computed query.

Once you fixed your indexed data, you may try and see wether an ORDER BY id DESC LIMIT 1 instead of MAX() will give any performance gain.

6
  • ORDER BY id DESC LIMIT 1 will use primary index by id, and will process all rows to compare createdAt column. Feb 7, 2020 at 15:50
  • I think, that the only way to optimize query performance is to minimize date range, so MAX() aggregation will compare less id values. Feb 7, 2020 at 15:53
  • @AnatoliyGusarov "will process all rows to compare createdAt column.", this is caused by you using a function over the column; my ORDER BY was a suggestion, but to be applied after you fixed index usage. I'll clarify.
    – watery
    Feb 7, 2020 at 15:54
  • 1
    Assuming a recent version of MySQL, a functional index can be created over DATE(createdAt), whithout the overhead of a new column. The main issue is that a range filter and an order by/max will likely be incompatible for a perfect speedup due to how b+tree indexes work, unless other transformations are done to the query.
    – jynus
    Feb 8, 2020 at 10:18
  • 1
    Note it was already possible, in a way, on 5.7 with virtual columns, that is why I just specified "recent versions".
    – jynus
    Feb 10, 2020 at 13:03
0

Answering the question. The best way to optimize query is to make where clause more precise. And the best way to do it is to split request into two super fast queries.

Both EXPLAIN with Extra "Select tables optimized away".

So in the first query we should select MAX. Not id, but createdAt:

SELECT MAX(createdAt) FROM test WHERE createdAt <= '2020-02-07';

The result is precise value with hours minutes and seconds. For example "2020-02-07 23:33:33" In the next query we will have strict comparison of this date to get final result:

SELECT MAX(id) FROM test WHERE createdAt = ?;

Where "?" is "2020-02-07 23:33:33"

Or you can do it in one query using subquery:

SELECT MAX(id) FROM test WHERE createdAt = (SELECT MAX(createdAt) FROM test WHERE createdAt <= '2020-02-07')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.