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 wrote: > OK.. no takers the first time... I'll try to give more/better > information.. > > I am running into a system wall here. I have at the moment about 2600 > rows of data totaling 650K. I expect this to grow at a rate of about an > additional 1200-1500 rows per week. I am using PHP to format the returns > into webspace. > > I have a field named 'adate' which is a mysql 14 character timestamp > (yes, I need HHMMSS data for other stuff). I am creating an array based > on a distinct return from the database. I then am in turn looping > through that array of about 25 entries, (which will remain at about 25 > with time) and running each through 10 queries.... all based on date. > The queries are really only two, with the exception of choosing separate > intervals of time to return, one having distinct fields parsed, the > other all rows parsed. The following are the two snippets of code which > get repeated five more times with only the time interval changed. > > $table, $user_net are PHP variables and $page[$i] is the array of 25 > entries. > > 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 > SELECT adate, mask FROM $table WHERE mask NOT LIKE '$user_net' AND page > = '$page[$i]' AND TO_DAYS(adate) >= TO_DAYS(NOW()) - 6 > > All I need is the count from each query. > > So, these ten queries are being run 25 times on 2600 rows of data and it > is taking about 4-6 seconds. I plan to collect data up to a limit of > about 70,000 rows. If I can expect the query time to grow linearly, it > would take about 2 minutes to generate this data. I need to get that > down to maybe 15 seconds <fingers crossed> or as little as possible. > > I have indexed 'adate', but don't think the index really works within > the functions? Maybe I'm stuck thinking inside of a box here? Perhaps > there is one blindingly great solution which I have not considered. This > is the first time I have ever created anything that really taxed a > system... therefore I am new at thinking in many of these terms. Perhaps > I should be rolling the data off into a temp file or something and > running the results using PHP? I really don't know what direction to > take, but I do see what appears to be a lot of repeating work, with only > little changes in time chunks. > > Should I perhaps create a 'date' field, grabbing only YYYYMMDD and > working from there? What am I not thinking about here? > > Any suggestions are very much welcome. > > 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
