usert4jju7 usert4jju7 - 7 months ago 15
SQL Question

SQL subquery causing overall query to go slow

The below query takes several minutes(never completes sometimes) to execute. I'm using

MySQL
database.

select
customer_name as cust,
SUM(num_visits) AS visits
from
visit_history
where
category = "middleMan"
and eve_date >= '2014-07-01' and eve_date <= '2015-07-01
and eve_type='XCG'
and eve_master IN (select eve_name from master_type_ahj where category = "middleMan" and eve_date >= '2014-07-01' and eve_date <= '2015-07-01')
group by
cust
order by
visits desc
limit
50


The database table contains over million records. The data is partitioned. If I were to remove the subquery -
and eve_master IN (select eve_name from master_type_ahj where category = "middleMan" and eve_date >= '2014-07-01' and eve_date <= '2015-07-01')
, the query completes in a few min.

The columns that go into
WHERE
clause are indexed.

Is there anyway I could tune this? Any help here would help me tune a bunch of related queries too. Could I ask for help here please?

Answer

Here's another way using a derived table

select 
    customer_name as cust,
    sum(num_visits) as visits
from visit_history
join (
    select distinct eve_name from master_type_ahj 
    where category = "middleMan"  
    and eve_date >= '2014-07-01' 
    and eve_date <= '2015-07-01'
) t on t.eve_name = visit_history.eve_master
where category = "middleMan"
    and eve_date >= '2014-07-01' and eve_date <= '2015-07-01'
    and eve_type='XCG'
group by cust
order by visits desc 
limit 50
Comments