RE: [PHP-DB] RE: query is returning over 74,000 results and taking 30 seconds - HELP!
| From: | Aaron Wolski | Date: | Mon, 04 Aug 2003 18:18:08 +0000 |
| Subject: | RE: [PHP-DB] RE: query is returning over 74,000 results and taking 30 seconds - HELP! | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-29666@lists.php.net to get a copy of this message | ||
Ok.. I echo'd out the queries AND the num_rows returned for each loop
and this is what I got:
Manufacturer Query is: SELECT DISTINCT manufacturer FROM kcs_threads
ORDER BY manufacturer
Manufacturer Rows Returned Are: 5
Left Join Query is: select t.type, t.newUrlType, g.threadType,
g.groupName, g.groupNameUrl FROM kcs_threads t LEFT JOIN
kcs_threadgroups g ON t.type = g.threadType WHERE t.manufacturer = 'DMC'
ORDER BY t.type, g.groupName
Left Join Rows Returned Are: 77022
Left Join Query is: select t.type, t.newUrlType, g.threadType,
g.groupName, g.groupNameUrl FROM kcs_threads t LEFT JOIN
kcs_threadgroups g ON t.type = g.threadType WHERE t.manufacturer =
'Gentle Arts Sampler Threads' ORDER BY t.type, g.groupName
Left Join Rows Returned Are: 94
Left Join Query is: select t.type, t.newUrlType, g.threadType,
g.groupName, g.groupNameUrl FROM kcs_threads t LEFT JOIN
kcs_threadgroups g ON t.type = g.threadType WHERE t.manufacturer =
'Kreinik' ORDER BY t.type, g.groupName
Left Join Rows Returned Are: 309
Left Join Query is: select t.type, t.newUrlType, g.threadType,
g.groupName, g.groupNameUrl FROM kcs_threads t LEFT JOIN
kcs_threadgroups g ON t.type = g.threadType WHERE t.manufacturer =
'Rainbow Gallery' ORDER BY t.type, g.groupName
Left Join Rows Returned Are: 23
Left Join Query is: select t.type, t.newUrlType, g.threadType,
g.groupName, g.groupNameUrl FROM kcs_threads t LEFT JOIN
kcs_threadgroups g ON t.type = g.threadType WHERE t.manufacturer = 'The
Caron Collection' ORDER BY t.type, g.groupName
Left Join Rows Returned Are: 495
Total elapsed time = 21.905001 seconds. Total results: 77943
What do I make of this? I see where the bottle neck is.. but how to I
refine the LEFT JOIN query to prevent this?
For the DMC Left Join.. is should have returned: 2067 rows in set (1.63
sec)
ANY idea's?
Aaron
-----Original Message-----
From: Nelson Goforth [mailto:ngoforth@earthnet.net]
Sent: August 4, 2003 2:16 PM
To: php-db@lists.php.net
Subject: [PHP-DB] RE: query is returning over 74,000 results and taking
30 seconds - HELP!
Have you tried the MySQL EXPLAIN command to look at your statement?
That can provide some insight when nothing else works.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php