Optimize the query to add content to the current time in WHERE

Question

I have a query that is doing the following:

AND TIME(NOW() + INTERVAL li.tzOffset HOUR - INTERVAL li.dst HOUR) BETWEEN '08:00:00' AND '20:00:00'

The problem is that its performance is not very good, but I don't know if there is a way to improve its performance. There is no index on either tzOffset or dst. Would indexing them help?

I'm currently running MySQL 5.6, but anticipate that I may be upgrading to 5.7 in a week or two. If MySQL 8.0 has features that can speed up queries, it would also be good to know about that so I can justify spending time looking into the issue.

P粉561438407 · Answer

SeeSearchable

Worse yet, you’ve basically

WHERE function(variable1, variable2) BETWEEN ...

If there is some way to combine li.tzOffset - li.dst into a single column, then you can do this

WHERE new_column BETWEEN ...

Which may be able to use INDEX involving new_column

I think the index generating columns were added in 5.7. If there is no "generated column" you will need to add a new column and calculate the difference in it.