randombits randombits - 4 months ago 9
SQL Question

What keys should be indexed here to make this query optimal

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
?

Answer

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.

Comments