FYI... Yes, I created a new field called 'd_now' with data inserted as (TO_DAYS(NOW()), updated it to include the proper date format for all the old records and of course then had to paste in the old timestamp values that automatically updated when I did that update... lots of fun on 2800 records. Anyway, I then created an index on the 'd_now' field and changed from:
SELECT TO_DAYS(adate), mask FROM $table WHERE mask NOT LIKE '$user_net' AND page LIKE '$page[$i]' AND TO_DAYS(adate) >= TO_DAYS(NOW()) - 6 where 'adate' is the mysql timestamp to: SELECT d_now, mask FROM $table WHERE mask NOT LIKE '$user_net' AND page = '$page[$i]' AND d_now >= TO_DAYS(NOW()) - 6 I reduced the number of times the statement was run by 20% and I wound up with a reduction in time of 70% total!!! Excellent!!!! Very excellent!!!!! This indexing stuff is really sweet. The whole trouble I had with indexing the timestamp, was the inability to create a clean use of the indexed field, as it was inside of a TO_DAYS() function. Or at least that is my understanding of it. Anyway, this might be just totally boring stuff to many, but I'm feeling pretty good right now. I was about to the point of creating this additional field, and the responses from this list really confirmed that I needed this. Joseph Bueno wrote: > > Instead of: > TO_DAYS(adate) >= TO_DAYS(NOW()) - 6 > you can try: > adate >= unix_timestamp(now()) - 6 * 24 * 3600 > This way, you don't need to apply a function to 'adate'. > > You should also note that these expressions are not exactly > equivalent since yours compares day numbers but mine compares > seconds. > If you really need to work on day numbers, it would > be more efficient to use a separate column where you explicitely > insert TO_DAYS(NOW()). This way you can index it and efficiently > use it on SELECTs. > > Hope this helps > Joseph Bueno > -- John Hinton - Goshen, VA. http://www.ew3d.com Those who dance are considered insane by those who can't hear the music.... --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
