Question 267292 in Mysql Question posted in
The official MySQL documentation can be found here.

optimize TIMESTAMPDIFF in mysql query

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.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  TIMESTAMPDIFF(SECOND, NOW(),(q.end_date - INTERVAL (q.execution_time*(q.threads-1)) SECOND)) <= 0
    ORDER BY  TIMESTAMPDIFF(SECOND, NOW(),(q.end_date - INTERVAL (q.execution_time*(q.threads-1)) SECOND)) ASC

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

I tried these :

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 - INTERVAL (q.execution_time * (q.threads - 1)) SECOND) AS remainingTimeInSeconds
    FROM  p_queue q
    WHERE  TIMESTAMPDIFF(SECOND, NOW(), q.end_date - INTERVAL (q.execution_time * (q.threads - 1)) SECOND) <= 0
    ORDER BY  TIMESTAMPDIFF(SECOND, NOW(), q.end_date - INTERVAL (q.execution_time * (q.threads - 1)) SECOND) ASC 

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 - INTERVAL (q.execution_time * (q.threads - 1)) SECOND) AS remainingTimeInSeconds
    FROM  p_queue q
    WHERE  TIMESTAMPDIFF(SECOND, NOW(), q.end_date - INTERVAL (q.execution_time * (q.threads - 1)) SECOND) <= 0` 

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) AS remainingTimeInSeconds,
        TIMESTAMPDIFF(SECOND, NOW(), (q.end_date - INTERVAL (q.execution_time * (q.threads - 1)) SECOND)) AS timeDiff
    FROM  p_queue q
    HAVING  timeDiff <= 0
    ORDER BY  timeDiff ASC;

#UPDATE

the code is used to process an online game tasks , wars .

what it will do is that :
it will get every attack that it supposed to arrive now to the village , which clarify the condition TIMESTAMPDIFF(SECOND, NOW(),q.end_date) <= 0

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

Tags:

2

Answers


  1. 0

    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))

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search