CODEWITHSUNDEEP
mysqlsql
user10705797
user10705797

Reputation:

How to minimize SQL query to give output very fast?

I have two queries from which I have created one which is giving me full data, but the issue is that it is taking so much time to execute.

There are several cases I am using because of that it is taking so much of time, I just want to know can I minimize it further so that it will not effect my output but execute faster.

Query1

select outlet, sum(netamount) 
from syncbill 
where cancelled<>'Y' 
and year(curdate())=year(billdate) 
and month(curdate())=month(billdate) 
group by OUTLET;

Query2

select outlet,sum(netamount) 
from syncbill 
where cancelled<>'Y' 
and year(curdate())=year(billdate) 
group by OUTLET;

I am merging the above two queries to get result in one go

new query

select  b.CUSTOMERDESCRIPTOR as outlet,
        round(sum(case when year(curdate())=year(a.billdate) and month(curdate())=month(a.billdate) then a.netamount else 0 end)) as monthAmount, 
        ROUND(SUM(CASE WHEN YEAR(CURDATE() + INTERVAL 9 MONTH) = YEAR(billdate + INTERVAL 9 MONTH) THEN netamount ELSE 0 END)) as yearAmount , 
        round(Sum(case when a.billdate >= date(now()) then a.netamount else 0 end)) as Transaction
from syncbill a Inner Join
     ecustomer b
     on a.outlet = b.CUSTOMERIDENTIFIER 
where a.cancelled <> 'Y' and
      outlet in (select CUSTOMERIDENTIFIER from mt_distributrol where mt_distributr_vcdistributrcode = 'AAAA')
group by a.OUTLET

What I am doing in the above query is

Calculating current month data and current year(financial year) data if there is data then data else 0.

But it is taking so much of time which is appearing as very bad user experience. How can I minimize this?

Edit

I have made a new query after creating indexes, but query is showing error. Here is my query:

select outlet,round(sum(case when year(curdate())=year(billdate) and month(curdate())=month(billdate) then netamount else 0 end)) as monthAmount, ROUND(SUM(CASE WHEN YEAR(CURDATE() + INTERVAL 9 MONTH) = YEAR(billdate + INTERVAL 9 MONTH) THEN netamount ELSE 0 END)) as yearAmount , round(Sum(case when billdate >= date(now()) then netamount else 0 end)) as Transaction from syncbill where cancelled<>'Y' and force index (ix_cancelled, ix_outlet) and force INDEX (ix_outlet) FOR group by OUTLET

This is the error I am getting

query with force

select  b.CUSTOMERDESCRIPTOR as outlet,round(sum(case when year(curdate())=year(a.billdate) and month(curdate())=month(a.billdate) then a.netamount else 0 end)) as monthAmount, ROUND(SUM(CASE WHEN YEAR(CURDATE() + INTERVAL 9 MONTH) = YEAR(billdate + INTERVAL 9 MONTH) THEN netamount ELSE 0 END)) as yearAmount , round(Sum(case when a.billdate >= date(now()) then a.netamount else 0 end)) as Transaction from  syncbill a force index (ix_cancelled_OUTLET_billdate) Inner Join ecustomer b on a.outlet = b.CUSTOMERIDENTIFIER where    a.cancelled<>'Y' and  outlet in(select CUSTOMERIDENTIFIER from mt_distributrol where mt_distributr_vcdistributrcode = 'AAAA')  group by   a.OUTLET

error I am getting from new query after creating indexes

final query result

after running the query with explain

table structure in database

Upvotes: 1

Views: 141

Answers (2)

shabnamz
shabnamz

Reputation: 48

For improving the performance of new_Query, you should have one index on three Columns (cancelled, OUTLET) with this order that I write; because in the query execution, at first where expression executes then group by expression and at the end the select statement; if the index data pages were created with columns that used in this order, the speed of data seek is increased.

CREATE  INDEX ix_cancelled_OUTLET ON syncbill(cancelled, OUTLET);

Upvotes: 1

DDS
DDS

Reputation: 2478

I suggest you to create a couple of indexes: one on 'outlet' for the group by and one on cancelled to improve the filter.

CREATE  INDEX ix_outlet ON syncbill(outlet);

and

CREATE  INDEX ix_cancelled ON syncbill(cancelled);

then force mysql to use those

select a,b,c
from my_table
force index (ix_cancelled, ix_outlet)
force INDEX (ix_outlet) FOR GROUP BY
where a = condition_1
group by a,b

Upvotes: 1

Related Questions