Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a query

SELECT leads.id, sfav.id is_favorite
FROM leads  
  INNER JOIN teams tst 
    ON  tst.team_set_id = leads.team_set_id 
  INNER JOIN members members 
    ON  tst.team_id = members.team_id
    AND members.user_id = 'a14447cf-a997-bdad-a0f8-51111616c23c'
    AND members.deleted=0   
  LEFT JOIN  favourite sfav
    ON sfav.record_id=leads.id
    AND sfav.module ='Leads'
    AND sfav.created_by='a14447cf-a997-bdad-a0f8-51111616c23c'
    AND sfav.deleted=0
  LEFT JOIN cldetails bidders 
    ON  bidders.lead_id = leads.id
    and bidders.deleted = 0
  LEFT JOIN accounts
    ON accounts.id = bidders.account_id
    AND accounts.deleted = 0
WHERE leads.deleted = 0
  AND 
  ( ( accounts.billing_address_state IN  ( "AL","AK","AZ","AR","CA","CO","CT","DE","DC","FL","GA","HI","ID","IL","IN","IA","KS","KY","LA","ME","MD","MA","MI","MN","MS","MO","MT","NE","NV","NH","NJ","NM","NY","NC","ND","OH","OK","OR","PA","RI","SC","SD","TN","TX","UT","VT","VA","WA","WV","WI","WY") ) 
    OR leads.assigned_user_id = "a14447cf-a997-bdad-a0f8-51111616c23c" 
  ) 
  AND leads.parent_lead_id IS NULL 
GROUP BY COALESCE(leads.parent_lead_id, leads.id)

Which is taking around 4 seconds the Explain plan is as

id   select_type     table               type        possible_keys                                       key                 key_len     ref                                             rows    Extra                                       
1    SIMPLE          leads               ref         idx_leads_tmst_id,idx_del_user                      idx_del_user       2    const                                  16097    Using where                                 
1    SIMPLE          sfav                ref         idx_abdul_test                                      idx_abdul_test     375 db.leads.id,const,const,const   1                                                
1    SIMPLE          bidders             ref         idx_abdul_test                                      idx_abdul_test     111  db.leads.id,const              5                                                
1    SIMPLE          tst                 ref         idx_ud_set_id,idx_ud_team_id,idx_ud_team_set_id     idx_ud_set_id      109  db.leads.team_set_id           3    Using where; Using index                    
1    SIMPLE          members             index       idx_teammemb_team_user,idx_abdul_test               idx_abdul_test     220  NULL                                   8    Using where; Using index; Using join buffer 
1    SIMPLE          accounts            eq_ref      PRIMARY,idx_accnt_id_del,idx_accnt_assigned_del     PRIMARY            108  db.bidders.account_id          1   

Indexes are as

teams
  PRIMARY KEY (`id`),
  KEY `idx_ud_set_id` (`team_set_id`,`team_id`),
  KEY `idx_ud_team_id` (`team_id`),

members
  PRIMARY KEY (`id`),
  KEY `idx_abdul_test` (`team_id`,`user_id`,`deleted`)

favourite
  PRIMARY KEY (`id`),
  KEY `idx_abdul_test` (`record_id`,`module`,`created_by`,`deleted`)

cldetails
  PRIMARY KEY (`id`),
  KEY `idx_abdul_test` (`lead_id`,`deleted`)

leads
  PRIMARY KEY (`id`),
  KEY `idx_del_user` (`deleted`,`assigned_user_id`),

What can i do minimize execution time of query.

UPDATE : If i run this query with LIMIT clause it will run in 0.00 sec,But this is a part of some other query so i can't use LIMIT clause over there.

share|improve this question
    
@ypercube : Can you please look into this ? –  Abdul Manaf Jul 16 '13 at 6:41
1  
Try adding indexes where the "fixed" columns are first. Example: favourite (module,created_by,deleted, record_id) –  ypercube Jul 16 '13 at 7:00
    
I will do it..actually query after table favourite starts executing slow upto this table it is running fine, major issue is with table cldetails it has aroud 100000 records. –  Abdul Manaf Jul 16 '13 at 7:02
    
InnoDB or MyISAM tables? –  ypercube Jul 16 '13 at 7:05
2  
@ypercube's point is probably that your query doesn't appear to be selecting anything from cldetails or accounts and thus outer-joining those two tables doesn't change anything (except it may negatively affect the performance). Try removing them and look at the results after running the query without them to see if the results are correct and the performance improved. –  Andriy M Jul 16 '13 at 7:17

2 Answers 2

This question offers a usable answer on stackexchange from KernelM: Indexed Views in MySQL

Specifically this paragraph:

I don't think MySQL supports materialized views which is what you would need, but it wouldn't help you in this situation anyway. Whether the index is on the view or on the underlying table, it would need to be written and updated at some point during an update of the underlying table, so it would still cause the write speed issues.

Your best bet would probably be to create summary tables that get updated periodically.

share|improve this answer

You could try flexviews to create and incrementally update summary tables. The project documentation states, that it can use JOINS and most aggregation functions. It's worth to give it a try.

Also, you could try to split the query on the OR condition to use two SELECTS with a UNION:

    (SELECT leads.id, sfav.id is_favorite
    FROM leads  
      INNER JOIN teams tst 
        ON  tst.team_set_id = leads.team_set_id 
      INNER JOIN members members 
        ON  tst.team_id = members.team_id
        AND members.user_id = 'a14447cf-a997-bdad-a0f8-51111616c23c'
        AND members.deleted=0   
      LEFT JOIN  favourite sfav
        ON sfav.record_id=leads.id
        AND sfav.module ='Leads'
        AND sfav.created_by='a14447cf-a997-bdad-a0f8-51111616c23c'
        AND sfav.deleted=0
      LEFT JOIN cldetails bidders 
        ON  bidders.lead_id = leads.id
        and bidders.deleted = 0
      LEFT JOIN accounts
        ON accounts.id = bidders.account_id
        AND accounts.deleted = 0
    WHERE leads.deleted = 0
      AND 
      accounts.billing_address_state IN  ("AL","AK","AZ","AR","CA","CO","CT","DE","DC","FL","GA","HI","ID","IL","IN","IA","KS","KY","LA","ME","MD","MA","MI","MN","MS","MO","MT","NE","NV","NH","NJ","NM","NY","NC","ND","OH","OK","OR","PA","RI","SC","SD","TN","TX","UT","VT","VA","WA","WV","WI","WY")
      AND leads.parent_lead_id IS NULL 
    GROUP BY COALESCE(leads.parent_lead_id, leads.id)
    ) UNION (
    SELECT leads.id, sfav.id is_favorite
    FROM leads  
      INNER JOIN teams tst 
        ON  tst.team_set_id = leads.team_set_id 
      INNER JOIN members members 
        ON  tst.team_id = members.team_id
        AND members.user_id = 'a14447cf-a997-bdad-a0f8-51111616c23c'
        AND members.deleted=0   
      LEFT JOIN  favourite sfav
        ON sfav.record_id=leads.id
        AND sfav.module ='Leads'
        AND sfav.created_by='a14447cf-a997-bdad-a0f8-51111616c23c'
        AND sfav.deleted=0
      LEFT JOIN cldetails bidders 
        ON  bidders.lead_id = leads.id
        and bidders.deleted = 0
      LEFT JOIN accounts
        ON accounts.id = bidders.account_id
        AND accounts.deleted = 0
    WHERE leads.deleted = 0
      AND 
      leads.assigned_user_id = "a14447cf-a997-bdad-a0f8-51111616c23c"  
      AND leads.parent_lead_id IS NULL 
    GROUP BY COALESCE(leads.parent_lead_id, leads.id)
    )

Also, since you have

AND leads.parent_lead_id IS NULL

in WHERE conditions, you can simplify the GROUP BY clause to:

GROUP BY leads.id

beacause the COALESCE will always result in leads.id (because leads.parent_lead_id has to be null)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.