Announcement

Announcement Module
Collapse
No announcement yet.

Query Optimization - Indexes

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Query Optimization - Indexes

    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?

    Thanks

  • #2
    You have done most of the things correct.

    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 ...)?

    Comment


    • #3
      sterin71 wrote on Fri, 16 October 2009 15:03


      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.

      Comment


      • #4
        Hi Sterin71 & gmouse,
        Many thanks for the feedback. I'm using MyISAM. itemId is the Primarykey on bus_items table. shouldn't it be faster doing COUNT on the PK? Just wondering.
        cheers

        Comment


        • #5
          Thanks guys! I've changed to COUNT(item.businessid) and the response time is 0.17 secongs. Great.

          Thanks for you input. Nevertheless, I'm little wondering how did this impact the performance! Is it just because businessid is already indexed?

          Comment


          • #6
            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.

            Comment


            • #7
              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

              Comment

              Working...
              X