CODEWITHSUNDEEP
phpmysqlsqldatabaseoptimization
captain_a
captain_a

Reputation: 3287

optimize my log report query

I am making a log report , in which i have written a query for getting results from the database , but it is too slow, the results come very slow. someone optimize it for me, and also tell me some links for studying about the optimization. the query is here :

select '$variable' as cell,
COALESCE(( SELECT count(distinct cellno) FROM smsreports s WHERE s.cellno = '$variable' AND fmonth = '2015-01')) as jan,
COALESCE(( SELECT count(distinct cellno) FROM smsreports s WHERE s.cellno = '$variable' AND fmonth = '2015-02')) as feb,
COALESCE(( SELECT count(distinct cellno) FROM smsreports s WHERE s.cellno = '$variable' AND fmonth = '2015-03')) as mar,
COALESCE(( SELECT count(distinct cellno) FROM smsreports s WHERE s.cellno = '$variable' AND fmonth = '2015-04')) as apr,
COALESCE(( SELECT count(distinct cellno) FROM smsreports s WHERE s.cellno = '$variable' AND fmonth = '2015-05')) as may,
COALESCE(( SELECT count(distinct cellno) FROM smsreports s WHERE s.cellno = '$variable' AND fmonth = '2015-06')) as jun,
COALESCE(( SELECT count(distinct cellno) FROM smsreports s WHERE s.cellno = '$variable' AND fmonth = '2015-07')) as jul,
COALESCE(( SELECT count(distinct cellno) FROM smsreports s WHERE s.cellno = '$variable' AND fmonth = '2015-08')) as aug,
COALESCE(( SELECT count(distinct cellno) FROM smsreports s WHERE s.cellno = '$variable' AND fmonth = '2015-09')) as sep,
COALESCE(( SELECT count(distinct cellno) FROM smsreports s WHERE s.cellno = '$variable' AND fmonth = '2015-10')) as oct,
COALESCE(( SELECT count(distinct cellno) FROM smsreports s WHERE s.cellno = '$variable' AND fmonth = '2015-11')) as nov,
COALESCE(( SELECT count(distinct cellno) FROM smsreports s WHERE s.cellno = '$variable' AND fmonth = '2015-12')) as dec

optimize this query to get results , remember i want month wise results here.

Upvotes: 0

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You want to do conditional aggregation:

select '$variable' as cell,
        count(distinct case when fmonth = '2015-01' then cellno end) as jan,
        count(distinct case when fmonth = '2015-02' then cellno end) as feb,
        . . . 
        count(distinct case when fmonth = '2015-12' then cellno end) as dec
FROM smsreports s
WHERE s.cellno = '$variable';

Upvotes: 1

Related Questions