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.
Answer :
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.
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)