| Any suggestions to a query with multiple joins? [message #1492] |
Tue, 26 June 2007 15:22  |
 tnguyen
Messages: 5
Registered: June 2007 |
Junior Member |
|
|
Hi,
I'm trying to get my query to run quicker if possible. The query I have is:
SELECT COUNT(c.name) AS collectionCount FROM collection_categories AS p2 , collection_categories AS n2, collections c, collection_items d, commerce_items e WHERE n2.lft BETWEEN p2.lft AND p2.rgt AND n2.approved=1 AND p2.lft > 0 AND (n2.categoryid = c.categoryid) AND (p2.categoryid = 40) AND (d.collectionid=c.collectionid) AND (e.itemid=d.itemid) AND e.active=1 AND e.state < 2 AND UNIX_TIMESTAMP(dateExpires)-UNIX_TIMESTAMP(now()) > 0;
The explain table shows:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE p2 ref PRIMARY PRIMARY 4 const 1 Using where
1 SIMPLE e ref itemid,state,commerce_idx commerce_idx 2 const 19375 Using where
1 SIMPLE d eq_ref PRIMARY,collectionid PRIMARY 4 collecti_collectica.e.itemid 1
1 SIMPLE c eq_ref PRIMARY,categoryid,cat_coll,last_upd PRIMARY 4 collecti_collectica.d.collectionid 1
1 SIMPLE n2 ref PRIMARY,approved PRIMARY 4 collecti_collectica.c.categoryid 1 Using where
I'm trying to get the query to run < 1 s where it's now taking 1.5 s for only 50k records (which is not scalable).
Any suggestions would be really appreciative.
|
|
|
Members
Search
Help
Register
Login
Home
