Chris wrote:
Rob Adams wrote:
I have a query that I run using mysql that returns about 60,000 plus
rows. It's been so large that I've just been testing it with a limit
0, 10000 (ten thousand) on the query. That used to take about 10
minutes to run, including processing time in PHP which spits out xml
from the query. I decided to chunk the query down into 1,000 row
increments, and tried that. The script processed 10,000 rows in 23
seconds! I was amazed! But unfortunately it takes quite a bit longer
than 6*23 to process the 60,000 rows that way (1,000 at a time). It
takes almost 8 minutes. I can't figure out why it takes so long, or
how to make it faster. The data for 60,000 rows is about 120mb, so I
would prefer not to use a temporary table. Any other suggestions?
This is probably more a db issue than a php issue, but I thought I'd
try here first.
Sounds like missing indexes or something.
Use explain: http://dev.mysql.com/doc/refman/4.1/en/explain.html
If that were the case I wouldn't expect limiting the number of rows
returned to make a difference since the actual query is the same.
Chances are it's purely a data transfer delay. Do a test with the same
query but only grab one of the fields - something relative small like a
integer field - and see if that's significantly quicker. I'm betting it
will be.
If that is the problem you need to be looking at making sure you're only
getting the fields you need. You may also want to look into changing the
cursor type you're using although I'm not sure if that's possible with
MySQL nevermind how to do it.
-Stut
--
http://stut.net/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
