Just a wild guess. If you are only using TO_DAYS(somedate) for the queries, why don't you create the column 'adate' as INT and index it? When you need the real DATE, use the FROM_DAYS() function.
Just a wild guess that I think could improve your queries speed. Adolfo On Sat, 2002-12-21 at 17:50, John Hinton wrote: > I keep getting weird return messages from the list.. not sure if this > made it through...... > ---------------- > > 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. -- Adolfo Bello <[EMAIL PROTECTED]> --------------------------------------------------------------------- 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
