Best practice on index usage with timestamp



  • I have a query to find period tasks, it runs calculation and scans the whole table.

    UPDATE task SET status = ?
    WHERE cron_hour != 0
    AND (status = ? OR status = ?)
    AND UNIX_TIMESTAMP(NOW()) - end_time > cron_hour * 3600
    

    I think the last condition will not use the index on end_time, what's the best practice here?



  • create an index on ( cron_hour * 3600 + end_time )

    then write check in the where clause as

    AND UNIX_TIMESTAMP(NOW()) > cron_hour * 3600 + end_time 
    

    but you might find an index on status to be more useful


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2