Need some support on MySQL Query

Question

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.

Try adding indexes where the "fixed" columns are first. Example: favourite (module,created_by,deleted, record_id) — ypercubeᵀᴹ, Jul 16 '13 at 07: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 07:02
All MyISAM and i have already key buffer of 1GB and all indexes are of 917MB. — Abdul Manaf, Jul 16 '13 at 07:05
The 2 joins to cldetails and accounts seem redundant. Is that your SELECT list (or you have changed it for posting here)? — ypercubeᵀᴹ, Jul 16 '13 at 07:07
cldetails have relation with accounts we need to join these tables. — Abdul Manaf, Jul 16 '13 at 07:10
@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 07:17
I have Updated the query.I am searching billing_address_state from accounts and leads have relation with cldetails and cldetails have relation with accounts that is why i have joined them — Abdul Manaf, Jul 16 '13 at 07:21

score 2 · Answer 1 · edited May 23 '17 at 12:40

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.

redguy · Answer 2 · 2013-07-17T09:12:05.750

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)

Need some support on MySQL Query

2 Answers2

Linked