[n8kindt]

hi, i have this query that works perfectly aside from the 4 sec load times. this query is for a webstore that loads the 10most popular items purchased within the last week whose status meets a few conditions in the products table and compares it with the sums of the total quantity purchased... there isn't any way i can restructure this as to speed the query time is there? i'm specifically wondering if there is a better way to search for orders within the last week... as it is, i am using a subquery and TO_DAYS

Expand|Select|Wrap|Line Numbers

1. SELECT t.`prod_id` , p.`name` , p.`category` , SUM( t.`prod_qty` ) AS `SumOfprod_qty`
2. FROM `store_products` p
3. INNER JOIN `store_tranlog` t ON p.`id` = t.`prod_id`
4. WHERE p.`wholesale_tf` = "1"
5. AND t.`status` > "0"
6. AND p.`status` = "1"
7. AND p.`catalog` = "NON"
8. AND TO_DAYS( NOW( ) ) -7 < (
9. SELECT TO_DAYS( MAX( `date` ) ) AS `seven`
10. FROM `store_products` )
11. GROUP BY `prod_id`
12. ORDER BY `SumOfprod_qty` DESC
13. LIMIT 0 , 10

i suppose i could restructure the tables but seeing as i wasn't the one who originally designed the store there may be more problems than i can imagine with this idea. the only other option would be to use a bunch of UPDATE queries that ran all the time but i'm hoping i can find a quick fix somehow. thanks for your time, guys

[Atli]

Try replacing your sub-query with something like:

Expand|Select|Wrap|Line Numbers

1. AND `date` BETWEEN NOW() AND DATE_SUB(NOW(), INTERVAL 7 DAY)
2.  

If that doesn't work, try adding a EXPLAIN in front of your query. That should show you what is causing the delay.

[n8kindt]

Originally Posted by Atli

Try replacing your sub-query with something like:

Expand|Select|Wrap|Line Numbers

1. AND `date` BETWEEN NOW() AND DATE_SUB(NOW(), INTERVAL 7 DAY)
2.  

If that doesn't work, try adding a EXPLAIN in front of your query. That should show you what is causing the delay.

thanks for the quick reply!
i'm trying a simple version of that query and i cannot get it to return any results... can you see what i'm doing wrong?

Expand|Select|Wrap|Line Numbers

1. SELECT t.`prod_id`, t.`date`
2. FROM `store_tranlog` t 
3. WHERE CAST(t.`date` AS DATETIME) BETWEEN NOW() AND DATE_SUB(NOW() , INTERVAL 7 DAY)
4. LIMIT 0,10

as for the EXPLAIN this is what it returned
(i'm attaching a zipped htm file with the details)

[Atli]

O yea sorry, forgot. The lower number (or the older date) must be first in the BETWEEN clause.

As for the EXPLAIN, the first table, the one you named t, doesn't seem to have any indexes that can be used in your query.
Try adding an index to the "status" field of the "store_tranlog" table.

You can do that by using ALTER TABLE:

Expand|Select|Wrap|Line Numbers

1. ALTER TABLE `store_tranlog`
2. ADD INDEX (`status`)
3.  

If there is any valuable data in there, I would back it up first tho.

[n8kindt]

Originally Posted by Atli

O yea sorry, forgot. The lower number (or the older date) must be first in the BETWEEN clause.

As for the EXPLAIN, the first table, the one you named t, doesn't seem to have any indexes that can be used in your query.
Try adding an index to the "status" field of the "store_tranlog" table.

You can do that by using ALTER TABLE:

Expand|Select|Wrap|Line Numbers

1. ALTER TABLE `store_tranlog`
2. ADD INDEX (`status`)
3.  

If there is any valuable data in there, I would back it up first tho.

alright i added the index and... on average it shaved 0.2 - 0.3 seconds off the time :)...

then i changed my query to:

Expand|Select|Wrap|Line Numbers

1. SELECT t.`prod_id`,  p.`name`, p.`category`, SUM(t.`prod_qty`) AS `SumOfprod_qty`
2. FROM `store_products` p INNER JOIN `store_tranlog` t ON p.`id` = t.`prod_id`
3. WHERE p.`wholesale_tf`="1" AND t.`status`>"0" AND p.`status` = "1"  AND p.`catalog`="NON" AND CAST(t.`date` AS DATETIME) BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW()
4. GROUP BY `prod_id`
5. ORDER BY `SumOfprod_qty` DESC
6. LIMIT 0,10

and BOOM! it took only 0.04 seconds! seems like a significant enough difference for me ;-)

thank you!!!!

[Atli]

Glad I could help :)

[coolsti]

Yes it is amazing! I once changed a 9 second query to under 0.1 seconds by adding only one index :)