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

Best mysql indexing practices for queries with sums of fields

I’m looking to improve the query performance of the following mysql query:

SELECT * FROM items
     WHERE items.createdAt > ?
       AND items.createdAt + items.duration < ?

What are the best indexes to use here? Should I have one for both (createdAt) and (createdAt, duration)?

Thanks!

Tags:

2

Answers


  1. 0

    Create one index on createdAt

    Then create a generated (aka computed column) column on createdAt + duration and then create an index on the generated column.

    https://dev.mysql.com/doc/refman/8.0/en/create-table-secondary-indexes.html

    Login or Signup to reply.
  2. 0

    You can’t easily sove the problem. Indexes are one-dimensional. You need a 2D index. SPATIAL provides such, but it would be quite contorted to re-frame your data into such. PARTITION sort of gives an extra dimension; but, again, I don’t see that as viable.

    So, but best you can do is

    INDEX(createdAt)

    Or perhaps this is a little better:

    INDEX(createdAt, duration)

    I don’t see that a "generated" column will solve the problem — however it may lead to sometimes running faster for this simple reason:

    • If createdAt > ? filters out most of the table because createdAt is near the end, the INDEX(createdAt) appears to be a good index.
    • When that ? is near the beginning of time, such an index is essentially useless– because most of the table needs to be scanned.
    • A generated index on createdAt + duration has the same problem, just in the other direction.

    If the real query has an ORDER BY and LIMIT, there may be other tricks to play. I discuss that when trying to map IP-addresses to countries or businesses: http://mysql.rjweb.org/doc.php/ipranges

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

Back To Top
Search