What keys should be indexed here to make this query optimal

Question

I have a query that looks like the following:

SELECT * from foo
  WHERE days >= DATEDIFF(CURDATE(), last_day)

In this case, days is an INT. last_day is a DATE column.

so I need two individual indexes here for days and last_day?

Since you are using a function, last_day can't be used as an index. Days could but the performance gain really depend on the variance of the data. — Guillaume F., Jul 06 '16 at 22:20

score 5 · Accepted Answer · edited May 23 '17 at 12:22

This query predicate, days >= DATEDIFF(CURDATE(), last_day), is inherently not sargeable.

If you keep the present table design you'll probably benefit from a compound index on (last_day, days). Nevertheless, satisfying the query will require a full scan of that index.

Single-column indexes on either one of those columns, or both, will be useless or worse for improving this query's performance.

If you must have this query perform very well, you need to reorganize your table a bit. Let's figure that out. It looks like you are trying to exclude "overdue" records: you want expiration_date < CURDATE(). That is a sargeable search predicate.

So if you added a new column expiration_date to your table, and then set it as follows:

 UPDATE foo SET expiration_date = last_day + INTERVAL days DAY

and then indexed it, you'd have a well-performing query.

This is incredibly helpful. Thank you. – randombits Jul 06 '16 at 22:52 — randombits, Jul 06 '16 at 22:52

score 0 · Answer 2 · answered Jul 06 '16 at 22:19

0

You must be careful with indexes, they can help you reading, but they can reduce performance in insert.

You may consider to create a partition over last_day field.

I should try to create only in last_day field, but, I think the best is making some performance tests with different configurations.

answered Jul 06 '16 at 22:19

duardito

107
2
8

I'm not worried about INSERT performance on this table. It'll mostly be read queries. – randombits Jul 06 '16 at 22:20

score 0 · Answer 3 · answered Jul 06 '16 at 22:25

Since you are using an expression in the where criteria, mysql will not be able to use indexes on any of the two fields. If you use this expression regularly and you have at least mysql v5.7.8, then you can create a generated column and create an index on it.

The other option is to create a regular column and set its value to the result of this expression and index this column. You will need triggers to keep it updated.

What keys should be indexed here to make this query optimal

3 Answers3