MySQL Forums
Forum List  »  Newbie

Re: deriving and reusing a variable within query
Posted by: Rick James
Date: May 30, 2014 01:39PM

> This query works, but it seems pretty inefficient, with the number of times I make the same 'duration' calculation:

Not the case. Evaluating the query has to do a lot of things; computing TIMEDIFF 4 times is only a minor part of the total.

One simple (albeit cryptic) thing you could do is
ORDER BY TIME_TO_SEC(TIMEDIFF(FinishTime,StartTime)) DESC
-->
ORDER BY 1 DESC

Since GROUP BY and ORDER BY let you reference the parts of SELECT by number.

Another:
AND TIME_TO_SEC(TIMEDIFF(FinishTime,StartTime)) > 0
AND TIME_TO_SEC(TIMEDIFF(FinishTime,StartTime)) < 86400
-->
AND TIME_TO_SEC(TIMEDIFF(FinishTime,StartTime)) BETWEEN 1 AND 86399

The final optimization would probably make it slower:
SELECT diff ...
FROM ( SELECT TIME_TO_SEC(TIMEDIFF(FinishTime,StartTime)) AS diff ... ) AS y
...;

INDEX(Completed, FinishTime, StartTime)
_might_ make any variant of the query run faster. This is especially likely if the table is large and Completed=7 is rare. (11K rows is not very big; nearly half the tables discussed here are bigger than that.)

StartTime IS NOT NULL AND FinishTime IS NOT NULL
_may_ be unnecessary because (I think) if either is NULL then this will be NULL:
AND TIME_TO_SEC(TIMEDIFF(FinishTime,StartTime)) > 0
and NULL will lead to FALSE, thereby having the same effect.

Does the query take "too long" now? Will the table grow from 11K rows to 11M? (I'm fishing for how important this optimization is or is not.)

Keep in mind, when optimizing, that there are a lot of different directions to take. ;)

Options: ReplyQuote


Subject
Written By
Posted
Re: deriving and reusing a variable within query
May 30, 2014 01:39PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.