Re: table design and query opimization

[Sebastien Caisse]

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?