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!

CodePudding user response：

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