Hi guys, I’m a new member; however, not new to the community ) BTW, my name is Sam. I’ve a question regarding query optimization and would like to seek your help.
Here’s my query:
SELECT COUNT(item.itemId) as itemCount, stores.storeId, stores.name FROM bus_stores stores LEFT JOIN bus_items item ON item.businessId = stores.businessId and item.status=‘completed’ WHERE stores.status=‘active’ GROUP BY stores.storeId ORDER BY itemCount DESC LIMIT 0, 20
Explain:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE stores index PRIMARY 8 47 Using where; Using temporary; Using filesort
1 SIMPLE item ref i_businessid_status i_businessid_status 10 aceonline.stores.businessid,const 9 Using where
Index Information:
bus_stores: storeId is PK; Table size: 3000 records
bus_items: itemId is the PK, KEY i_businessid_status(businessid, status); Table size: 1 million records
The above query is taking about 5 sec to execute, which is a real shame.
Could anyone, plese, shed some light on how to optimize this query for better performance?
The only thing that I can debate (and it is bit slim) is the COUNT(item.itemId).
Depending on storage engine I think (big disclaimer ) that a COUNT(item.businessId) would perform better. Since that column is part of the index it doesn’t have to read that information from the table.
So instead we have to look at what your server variables and server looks like.
So what does the output from “SHOW VARIABLES” look like and which storage engine are you using (SHOW CREATE TABLE …)?
[B]sterin71 wrote on Fri, 16 October 2009 15:03[/B]
The only thing that I can debate (and it is bit slim) is the COUNT(item.itemId).
Depending on storage engine I think (big disclaimer ) that a COUNT(item.businessId) would perform better.
You are correct; it will work for both InnoDB and MyISAM.
It now suffices to read the key on (businessid, status). The corresponding row does not need to be accessed for retrieving additional data (such as item.Itemid). Moreover, because businessid is the first part of the key, finding all entries where businessid equals stores.businessid is fast.
That sounds very sensible. I had a slightly different notion; however, clarifired - thanks to your clarification.
I truly appreciate the response I had on this forum and it seems to be promising in here. I would certainly look forward to take part in the forum actively…
Cheers
) that a COUNT(item.businessId) would perform better. Since that column is part of the index it doesn’t have to read that information from the table.