MySQL Forums
Forum List  »  Optimizer & Parser

Re: table design and query opimization
Posted by: Sebastien Caisse
Date: April 27, 2006 11:27AM

Here's another way to thing of it, avoiding a useless division too:
instead of dividing time to get (what looks like an) "age": unix_timestamp( dob ) ) / ( 31536000 ))between 18 and 99

Precalc the range:
18 * 31536000 = 567648000
99 * 31536000 = 3122064000

So you'd get
unix_timestamp( dob ) between 567648000 and 3122064000

Now, quite frankly, do you need to check < 99 years?? wouldn't a > 18 years suffice?

unix_timestamp( dob ) > 567648000

Note I eliminated the floor() call too.

Not to mention a year isn't REALLY 365 days but that's another issue altogether...

More seriously (as a single division optimization probably won't change your query time mych), though, could you not precalc the changed assigned values on the client side before sending the query? If not, should we assume that the values embeded in single quotes are the values you supply?

Options: ReplyQuote

Written By
April 19, 2006 02:11AM
April 19, 2006 10:25PM
Re: table design and query opimization
April 27, 2006 11:27AM

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.