Re: need index date help
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 MMDD 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
Re: need index date help
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 MMDD 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
Re: need index date help
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 MMDD 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
Re: need index date help
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
