optimize TIMESTAMPDIFF in mysql query

Question

I need help optimizing a query for large data table when I test manually it shows fast , but in my slow query log it is logged as taken 10s+ for some reason .

SELECT  q.id, q.village_id, q.to_player_id, q.to_village_id,
        q.proc_type, TIMESTAMPDIFF(SECOND, NOW(),q.end_date) remainingTimeInSeconds
    FROM table
   

I expect to output the results that's time is ended , meaning time left must be 0 or less , in ASC order .

it order by the end time itself ,because when we have many attacks we must arrange them according to which attack suppose to arrive first ,then process them one by one

Answer 1

You need to make the search criteria sargable.

That is, do a calculation on end_date, execution_time, threads, store the result in the table and index it.

Then the optimiser can quickly determine which rows are relevant, without scanning the whole table every time.

The following automatically generates and expiry column and keeps it up to date if the row is modified.

Then that column is indexed and used in a much simpler WHERE clause.

ALTER TABLE
  p_queue
ADD COLUMN
  expiry TIMESTAMP GENERATED ALWAYS
    AS (end_date - INTERVAL (execution time * (threads-1)))
  STORED
;

ALTER TABLE
  p_queue
ADD INDEX
  (expiry)
;

SELECT
  q.id, q.player_id, q.village_id, q.to_player_id, q.to_village_id,
  q.proc_type, q.building_id, q.proc_params, q.threads,
  q.execution_time, TIMESTAMPDIFF(SECOND, NOW(),q.end_date) remainingTimeInSeconds
FROM
  p_queue q
WHERE
  q.expiry <= NOW()
ORDER BY
  q.expiry ASC

Answer 2

TIMESTAMPDIFF(SECOND, NOW(),q.end_date) <= 0 can be optimized by changing to to q.end_date >= NOW(), but the longer expression cannot be optimized.

See sargable (See @MatBailie's comment.)

The table would need INDEX(end_date).

For the more complex test, this might help: INDEX(end_date, execution_time, threads))