Performance of SQL queries in a subquery
I am wondering if there is a way to make this query and get the result faster than it is built now. Maybe on the left side or so? not sure?
SELECT cs_plz plz
,
cs_place
place,
cs_companies cmp
,
cs_businesscat
bc,
cs_trgtappm
goal,
cs_repeated rpt
,
(SELECT Count(call_id)
FROM calls
WHERE time_of_call > cs_assigndate
AND time_of_call < cs_lastactivate
AND call_sector = cs_id)
calls,
(SELECT Count(app_id)
FROM appointments
WHERE app_created > cs_assigndate
AND app_created < cs_lastactivate
AND app_callingnr IN ((SELECT c.calling_number
FROM calls c
WHERE c.time_of_call > cs_assigndate
AND c.time_of_call <
cs_lastactivate
AND c.call_sector = cs_id)))
appm,
(SELECT Count(sale_id)
FROM sales
WHERE sale_created > cs_assigndate
AND sale_created < cs_lastactivate
AND sale_callingnr IN ((SELECT c1.calling_number
FROM calls c1
WHERE c1.time_of_call > cs_assigndate
AND c1.time_of_call <
cs_lastactivate
AND c1.call_sector = cs_id)))
sales,
(SELECT Count(subsc_id)
FROM subscription
WHERE subsc_date > cs_assigndate
AND subsc_date < cs_lastactivate
AND subsc_callingnr IN ((SELECT c2.calling_number
FROM calls c2
WHERE c2.time_of_call > cs_assigndate
AND c2.time_of_call <
cs_lastactivate
AND c2.call_sector = cs_id)))
subsc
FROM area_sector
WHERE cs_lastactivate > Date_sub(Now(), interval 1 month)
AND cs_repeated >= 1
source to share
Your query structure is fine and maybe even better than left join
s. This query will benefit from composite indexes. The obvious is area_sector(cs_lastactivate, cs_repeated)
or area_sector(cs_repeated, cs_lastactivate)
, whichever column is more selective (i.e., selects the least number of rows).
Another obvious Index: calls(call_sector, time_of_call, calling_number)
.
Then indices on appointments(app_created, app_callingnr)
and similar ones on sales
should be useful. It's a bit tricky to tell because your columns don't have table aliases, so it's not clear which tables they come from.
source to share
First, the request is very messy. I suggest you change these sub queries to views. Then you can use connections (the type of connection depends on what you want to get). Anyway, If you change it to joins, the query will be much faster, because if you are using subqueries, each subquery has to be evaluated for every row in the main query.
At the end, consider adding indexes to the columns you use in the join operations, it should also be a good performance improvement if you have a lot of data in those tables.
source to share