search

Home  >  Q&A  >  body text

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

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粉218775965P粉218775965291 days ago485

reply all(1)I'll reply

  • P粉561438407

    P粉5614384072024-04-05 15:52:08

    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.

    reply
    0
  • Cancelreply