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. -- 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
