Explain plan in mysql performance using Using temporary; Using filesort ; Using index condition

Question

I read various blogs and documents online but just wanted to know how i can optimize the query. I am unable to decide if we have to rewrite the query or add indexes in order to optimize.

Adding create table structure also

CREATE TABLE `dsr_table` (
  `DSR_VIA` CHAR(3) DEFAULT NULL,
  `DSR_PULLDATA_FLAG` CHAR(1) DEFAULT 'O',
  `DSR_BILLING_FLAG` CHAR(1) DEFAULT 'O',
  `WH_FLAG` CHAR(1) DEFAULT 'O',
  `ARCHIVE_FLAG` CHAR(1) NOT NULL DEFAULT 'O',
  `DSR_BOOKING_TYPE` INT(2) DEFAULT NULL,
  `DSR_BRANCH_CODE` CHAR(3) NOT NULL,
  `DSR_CNNO` CHAR(12) NOT NULL,
  `DSR_BOOKED_BY` CHAR(1) NOT NULL,
  `DSR_CUST_CODE` VARCHAR(7) NOT NULL,
  `DSR_CN_WEIGHT` DECIMAL(8,3) NOT NULL,
  `DSR_CN_TYPE` CHAR(3) NOT NULL,
  `DSR_DEST` CHAR(3) NOT NULL,
  `DSR_MODE` CHAR(2) NOT NULL,
  `DSR_NO_OF_PIECES` DECIMAL(3,0) NOT NULL,
  `DSR_DEST_PIN` DECIMAL(6,0) DEFAULT NULL,
  `DSR_BOOKING_DATE` DATE NOT NULL,
  `DSR_AMT` DECIMAL(10,2) DEFAULT NULL,
  `DSR_STATUS` CHAR(1) NOT NULL,
  `DSR_POD_RECD` CHAR(1) DEFAULT NULL,
  `DSR_TRANSMF_NO` VARCHAR(10) NOT NULL,
  `DSR_BOOKING_TIME` CHAR(8) NOT NULL,
  `DSR_DOX` CHAR(1) NOT NULL,
  `DSR_SERVICE_TAX` DECIMAL(4,2) DEFAULT NULL,
  `DSR_SPL_DISC` DECIMAL(10,2) DEFAULT NULL,
  `DSR_CONTENTS` VARCHAR(255) DEFAULT NULL,
  `DSR_REMARKS` VARCHAR(25) DEFAULT NULL,
  `DSR_VALUE` VARCHAR(20) DEFAULT NULL,
  `DSR_INVNO` VARCHAR(10) DEFAULT NULL,
  `DSR_INVDATE` DATE DEFAULT NULL,
  `MOD_DATE` DATE DEFAULT NULL,
  `OFFICE_TYPE` CHAR(2) DEFAULT NULL,
  `OFFICE_CODE` CHAR(3) DEFAULT NULL,
  `DSR_REFNO` VARCHAR(50) DEFAULT NULL,
  `MOD_TIME` DECIMAL(4,0) DEFAULT NULL,
  `NODEID` VARCHAR(20) DEFAULT NULL,
  `USERID` VARCHAR(7) DEFAULT NULL,
  `TRANS_STATUS` CHAR(1) DEFAULT NULL,
  `DSR_ACT_CUST_CODE` VARCHAR(11) DEFAULT NULL,
  `DSR_MOBILE` VARCHAR(25) DEFAULT NULL,
  `DSR_EMAIL` VARCHAR(50) DEFAULT NULL,
  `DSR_NDX_PAPER` CHAR(1) DEFAULT NULL,
  `DSR_PICKUP_TIME` DECIMAL(4,0) DEFAULT NULL,
  `DSR_VOL_WEIGHT` DECIMAL(8,3) DEFAULT NULL,
  `DSR_CAPTURED_WEIGHT` DECIMAL(8,3) DEFAULT NULL,
  `DSR_PRODUCT` CHAR(3) DEFAULT NULL,
  `DSR_TRANS_STATUS_XI` CHAR(1) DEFAULT 'A',
  `DSR_TRANS_ID` DECIMAL(10,0) DEFAULT NULL,
  `DSR_ID_TYPE` INT(2) DEFAULT NULL,
  `DSR_ID_NUM` VARCHAR(50) DEFAULT NULL,
  `DSR_WT_CAPTURED` INT(1) DEFAULT NULL,
  `DSR_PUP_FRCODE` VARCHAR(7) DEFAULT NULL,
  `DSR_TS_AMT` DECIMAL(10,2) DEFAULT NULL,
  `DSR_FSC_PER` DECIMAL(4,2) DEFAULT NULL,
  `DSR_RATE_ID` DECIMAL(8,0) DEFAULT NULL COMMENT 'Used to store RateID by which final rate is calculated',
  `RECORD_ENTRY_DATETIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Record_creation_date` DATETIME DEFAULT NULL,
  `Record_Arrival_date` DATETIME DEFAULT NULL,
  `DSR_CONSIGNEE_MOBILE` VARCHAR(11) DEFAULT NULL,
  `DSR_PREV_WEIGHT` DECIMAL(8,3) DEFAULT NULL,
  `DSR_PREV_AMT` DECIMAL(10,2) DEFAULT NULL,
  `DSR_GOR_REMARKS` VARCHAR(50) DEFAULT NULL,
  `DSR_GOR_VOL_WT` DECIMAL(8,3) DEFAULT NULL,
  PRIMARY KEY (`DSR_CNNO`),
  KEY `idx_bcd_cnno_bkdate` (`DSR_BRANCH_CODE`,`DSR_CNNO`,`DSR_BOOKING_DATE`),
  KEY `idx_dsr_bkdate` (`DSR_BOOKING_DATE`),
  KEY `idx_dsr_custcode` (`DSR_CUST_CODE`),
  KEY `idx_dsr_invoiceno` (`DSR_INVNO`),
  KEY `idx_dsr_mdate` (`MOD_DATE`),
  KEY `idx_dsr_bookdby_ccd_cnno` (`DSR_BOOKED_BY`,`DSR_CUST_CODE`,`DSR_CNNO`),
  KEY `idx_dsr_bkdby_ccd_bkd_stat` (`DSR_BOOKED_BY`,`DSR_CUST_CODE`,`DSR_BOOKING_DATE`,`DSR_STATUS`),
  KEY `idx_dsr_refno` (`DSR_REFNO`),
  KEY `idx_dsr_txi_stat_mdate` (`DSR_TRANS_STATUS_XI`,`DSR_STATUS`,`MOD_DATE`),
  KEY `idx_dsr_trans_id` (`DSR_TRANS_ID`),
  KEY `idx_dsr_inv_date` (`DSR_INVDATE`),
  KEY `IDX_Create_arr_date` (`Record_creation_date`,`Record_Arrival_date`)
) ENGINE=INNODB DEFAULT CHARSET=latin1

This is the query which i am trying to execute. Please help me on this.

select ob.BRANCH_CITY orig_city,dstb.BRANCH_CITY dest_city,round(sum(CASE WHEN left(dsr_cnno, 1) IN ('V', 'E', 'X') THEN
IFNULL(value, 0) ELSE 0 END),2) Premium,
round(sum(CASE WHEN left(dsr_cnno, 1) NOT IN ('V', 'E', 'X') THEN  IFNULL(value, 0) ELSE 0 END),2) Non_Premium
from ( select DSR_BRANCH_CODE,dsr_cnno,DSR_AMT,
((dsr_amt) +((((dsr_amt)-ifnull((select max(ndsr_ins_amt) from ndx_dsr_table where ndsr_cnno=dsr_cnno ),0)-ifnull((select max(ndsr_serv_charge) from ndx_dsr_table where ndsr_cnno=dsr_cnno ),0) -ifnull((select sum(dr_extra_amt) from dsr_revenue where dr_cnno=dsr_cnno ),0))-(((dsr_amt)-ifnull((select max(ndsr_ins_amt) from ndx_dsr_table where ndsr_cnno=dsr_cnno ),0) -ifnull((select max(ndsr_serv_charge) from ndx_dsr_table where ndsr_cnno=dsr_cnno ),0)
-ifnull((select sum(dr_extra_amt) from dsr_revenue where dr_cnno=dsr_cnno ),0) )*ifnull(((select fr_discount from fr_mas where fr_Code=dsr_cust_code)),0)/100)) *ifnull(((select (case when dsr_invdate <'2017-05-01' then ifnull(fr_fsc_per,0) else 30 end) from fr_mas where fr_code=dsr_cust_code limit 1)),0)/100)) as value, dsr_cust_code
,dsr_dest_pin from dsr_table d where  dsr_booking_date BETWEEN '2017-05-01' AND '2017-06-30' AND LENGTH(dsr_cnno)=9 
AND DSR_BOOKED_BY ='F' AND dsr_status<>'R' AND dsr_cnno NOT LIKE 'J%' AND dsr_cnno NOT LIKE '@%'
AND dsr_cnno NOT LIKE '576%' AND dsr_cnno NOT LIKE 'I3%' AND dsr_cnno NOT LIKE '7%'
AND dsr_cnno NOT LIKE 'N%' and d.dsr_dest_pin>0) zz
inner join fr_mas f on f.FR_CODE=zz.dsr_cust_code
inner join branch_mas ob on ob.BRANCH_CODE=zz.dsr_branch_code and ob.BRANCH_LOC='L'
inner join serv_dest_mas dm on dm.SERV_PIN=zz.dsr_dest_pin 
inner join branch_mas dstb on dstb.BRANCH_CODE=dm.SERV_BRANCH and dstb.BRANCH_LOC='L'
where dstb.BRANCH_CITY in ('HYD','DEL','AMD','GGN','BLR','PNQ','MUM','CHE','CCU','NOD')
and ob.BRANCH_CITY in('HYD','DEL','AMD','GGN','BLR','PNQ','MUM','CHE','CCU','NOD')
group by orig_city,dest_city ;

And this is my explain plan:

      id  select_type         table          type    possible_keys                                                       key                       key_len  ref                             rows  Extra                                                                
------  ------------------  -------------  ------  ------------------------------------------------------------------  ------------------------  -------  -------------------------  ---------  ---------------------------------------------------------------------
     1  PRIMARY             ob             range   PRIMARY,FK_BRM_CITYMAS                                              FK_BRM_CITYMAS            4        (NULL)                            10  Using index condition; Using where; Using temporary; Using filesort  
     1  PRIMARY             <derived2>     ref     <auto_key2>                                                         <auto_key2>               3        billingdb.ob.BRANCH_CODE      319051  Using where                                                          
     1  PRIMARY             f              eq_ref  PRIMARY                                                             PRIMARY                   9        zz.dsr_cust_code                   1  Using index                                                          
     1  PRIMARY             dm             ref     PRIMARY,IDX_SDM_SERVPIN_STATUS1                                     IDX_SDM_SERVPIN_STATUS1   3        zz.dsr_dest_pin                    2  Using index                                                          
     1  PRIMARY             dstb           eq_ref  PRIMARY,FK_BRM_CITYMAS                                              PRIMARY                   3        billingdb.dm.SERV_BRANCH           1  Using where                                                          
     2  DERIVED             d              ref     idx_dsr_bkdate,idx_dsr_bookdby_ccd_cnno,idx_dsr_bkdby_ccd_bkd_stat  idx_dsr_bookdby_ccd_cnno  1        const                      182365315  Using index condition; Using where                                   
    10  DEPENDENT SUBQUERY  fr_mas         eq_ref  PRIMARY                                                             PRIMARY                   9        billingdb.d.DSR_CUST_CODE          1  (NULL)                                                               
     9  DEPENDENT SUBQUERY  fr_mas         eq_ref  PRIMARY                                                             PRIMARY                   9        billingdb.d.DSR_CUST_CODE          1  (NULL)                                                               
     8  DEPENDENT SUBQUERY  dsr_revenue    ref     PRIMARY                                                             PRIMARY                   12       billingdb.d.DSR_CNNO               1  (NULL)                                                               
     7  DEPENDENT SUBQUERY  ndx_dsr_table  eq_ref  PRIMARY                                                             PRIMARY                   12       billingdb.d.DSR_CNNO               1  (NULL)                                                               
     6  DEPENDENT SUBQUERY  ndx_dsr_table  eq_ref  PRIMARY                                                             PRIMARY                   12       billingdb.d.DSR_CNNO               1  (NULL)                                                               
     5  DEPENDENT SUBQUERY  dsr_revenue    ref     PRIMARY                                                             PRIMARY                   12       billingdb.d.DSR_CNNO               1  (NULL)                                                               
     4  DEPENDENT SUBQUERY  ndx_dsr_table  eq_ref  PRIMARY                                                             PRIMARY                   12       billingdb.d.DSR_CNNO               1  (NULL)                                                               
     3  DEPENDENT SUBQUERY  ndx_dsr_table  eq_ref  PRIMARY                                                             PRIMARY                   12       billingdb.d.DSR_CNNO               1  (NULL)

Update

I tried adding indexes also on INDEX(DSR_BOOKED_BY, dsr_booking_date, dsr_cnno, dsr_status, dsr_cnno, dsr_dist_pin, id) but no luck.

The RAM size of the server is 16GB and innodb bufferpool size is 12GB

This is the query which is taking almost 8 hrs but no result in the end

UPDATE

The query which works for a specific date:

SELECT  ob.BRANCH_CITY orig_city, 
    (
        SELECT  LEFT(branch_code,1)
            FROM  branch_mas c,serv_dest_mas b
            WHERE  c.branch_loc='L'
              AND  LEFT(c.branch_code,1) IN ('K','H','C','B','M','A',
                        'P','N','S','L'
                       )
              AND  c.branch_code=b.serv_branch
              AND  b.serv_pin=zz.dsr_dest_pin
        LIMIT  1
    ) dest_city,
        ROUND(SUM(CASE WHEN LEFT(dsr_cnno, 1) IN ('V', 'E', 'X')
                       THEN IFNULL(VALUE,
                        0) ELSE 0 END),2) Premium,
        ROUND(SUM(CASE WHEN LEFT(dsr_cnno,
       1) NOT IN ('V', 'E', 'X') THEN IFNULL(VALUE,
       0) ELSE 0 END),2
             ) Non_Premium FROM
    (
        SELECT  DSR_BRANCH_CODE,dsr_cnno,DSR_AMT,
                    ((dsr_amt) +((((dsr_amt) -IFNULL( (
               SELECT  MAX(ndsr_serv_charge)
                   FROM  ndx_dsr_table
                   WHERE  ndsr_cnno=dsr_cnno ),0) -IFNULL(
           (
               SELECT  SUM(dr_extra_amt)
                   FROM  dsr_revenue
                   WHERE  dr_cnno=dsr_cnno ),0))-(((dsr_amt) -IFNULL(
                   (
                       SELECT  MAX(ndsr_ins_amt)
                           FROM  ndx_dsr_table
                           WHERE  ndsr_cnno=dsr_cnno ),0
                          ) -IFNULL(
                   (
                       SELECT  MAX(ndsr_serv_charge)
                           FROM  ndx_dsr_table
                           WHERE  ndsr_cnno=dsr_cnno ),0
                          ) -IFNULL(
                   (
                       SELECT  SUM(dr_extra_amt)
                           FROM  dsr_revenue
                           WHERE  dr_cnno=dsr_cnno ),0) 
                          ) *IFNULL((
                   (
                       SELECT  fr_discount
                           FROM  fr_mas
                           WHERE  fr_Code=dsr_cust_code)),0)/100)) *
                           IFNULL(( (
       SELECT  (CASE WHEN dsr_invdate <'2017-05-01'
                     THEN IFNULL(fr_fsc_per,
                               0) ELSE 30 END )
           FROM  fr_mas
           WHERE  fr_code=dsr_cust_code
           LIMIT  1)),0)/100)
                    ) AS VALUE, dsr_cust_code, dsr_dest_pin
            FROM  dsr_table d
            WHERE  dsr_booking_date = '2017-04-30'
              AND  LENGTH(dsr_cnno)=9
              AND  DSR_BOOKED_BY ='F'
              AND  dsr_status<>'R'
              AND  d.dsr_dest_pin>0 
    ) zz
    INNER JOIN  fr_mas f  ON f.FR_CODE=zz.dsr_cust_code
    INNER JOIN  branch_mas ob  ON ob.BRANCH_CODE=zz.dsr_branch_code
      AND  ob.BRANCH_LOC='L'
    WHERE  ob.BRANCH_CITY IN('HYD','DEL','AMD','GGN','BLR','PNQ',
                'MUM','CHE','CCU','NOD'
                          )
      AND  d.dsr_cnno = f.dsr_cnno

The above code which does not work for a date range if we specify for a month:

SELECT  ob.BRANCH_CITY orig_city, 
    (
        SELECT  LEFT(branch_code,1)
            FROM  branch_mas c,serv_dest_mas b
            WHERE  c.branch_loc='L'
              AND  LEFT(c.branch_code,1) IN ('K','H','C','B','M','A',
                        'P','N','S','L'
                       )
              AND  c.branch_code=b.serv_branch
              AND  b.serv_pin=zz.dsr_dest_pin
        LIMIT  1
    ) dest_city,
        ROUND(SUM(CASE WHEN LEFT(dsr_cnno, 1) IN ('V', 'E', 'X')
                       THEN IFNULL(VALUE,
                        0) ELSE 0 END),2) Premium,
        ROUND(SUM(CASE WHEN LEFT(dsr_cnno,
       1) NOT IN ('V', 'E', 'X') THEN IFNULL(VALUE,
       0) ELSE 0 END),2
             ) Non_Premium FROM
    (
        SELECT  DSR_BRANCH_CODE,dsr_cnno,DSR_AMT,
                    ((dsr_amt) +((((dsr_amt) -IFNULL( (
               SELECT  MAX(ndsr_serv_charge)
                   FROM  ndx_dsr_table
                   WHERE  ndsr_cnno=dsr_cnno ),0) -IFNULL(
           (
               SELECT  SUM(dr_extra_amt)
                   FROM  dsr_revenue
                   WHERE  dr_cnno=dsr_cnno ),0))-(((dsr_amt) -IFNULL(
                   (
                       SELECT  MAX(ndsr_ins_amt)
                           FROM  ndx_dsr_table
                           WHERE  ndsr_cnno=dsr_cnno ),0
                          ) -IFNULL(
                   (
                       SELECT  MAX(ndsr_serv_charge)
                           FROM  ndx_dsr_table
                           WHERE  ndsr_cnno=dsr_cnno ),0
                          ) -IFNULL(
                   (
                       SELECT  SUM(dr_extra_amt)
                           FROM  dsr_revenue
                           WHERE  dr_cnno=dsr_cnno ),0) 
                          ) *IFNULL((
                   (
                       SELECT  fr_discount
                           FROM  fr_mas
                           WHERE  fr_Code=dsr_cust_code)),0)/100)
                          ) *IFNULL((
   (
       SELECT  (CASE WHEN dsr_invdate <'2017-05-01'
                     THEN IFNULL(fr_fsc_per,
                               0) ELSE 30 END )
           FROM  fr_mas
           WHERE  fr_code=dsr_cust_code
           LIMIT  1)),0)/100)
                    ) AS VALUE, dsr_cust_code, dsr_dest_pin
            FROM  dsr_table d
            WHERE  dsr_booking_date = '2017-04-30'
              AND  '2017-05-30'
              AND  LENGTH(dsr_cnno)=9
              AND  DSR_BOOKED_BY ='F'
              AND  dsr_status<>'R'
              AND  d.dsr_dest_pin>0 
    ) zz
    INNER JOIN  fr_mas f  ON f.FR_CODE=zz.dsr_cust_code
    INNER JOIN  branch_mas ob  ON ob.BRANCH_CODE=zz.dsr_branch_code
      AND  ob.BRANCH_LOC='L'
    WHERE  ob.BRANCH_CITY IN('HYD','DEL','AMD','GGN','BLR','PNQ',
                'MUM','CHE','CCU','NOD'
                          )
      AND  d.dsr_cnno = f.dsr_cnno

Rick James · Answer 1 · 2018-08-16T20:19:38.507

1

Please provide SHOW CREATE TABLE.

The main filter seems to be

            where  dsr_booking_date BETWEEN '2017-05-01' AND '2017-06-30'
              AND  LENGTH(dsr_cnno)=9
              AND  DSR_BOOKED_BY ='F'
              AND  dsr_status<>'R'
              AND  dsr_cnno NOT LIKE 'J%'
              AND  dsr_cnno NOT LIKE '@%'
              AND  dsr_cnno NOT LIKE '576%'
              AND  dsr_cnno NOT LIKE 'I3%'
              AND  dsr_cnno NOT LIKE '7%'
              AND  dsr_cnno NOT LIKE 'N%'
              and  d.dsr_dest_pin>0

Probably the only useful index for that is, in this order:

INDEX(DSR_BOOKED_BY, dsr_booking_date)

Things like

ifnull((select max(ndsr_ins_amt)     from ndx_dsr_table where ndsr_cnno=dsr_cnno ),0)-
ifnull((select max(ndsr_serv_charge) from ndx_dsr_table where ndsr_cnno=dsr_cnno ),0) -

should probably be done together. Consider something like

ifnull(mm.max_nia), 0) -
ifnull(mm.max_nsc), 0) .
...
LEFT JOIN ( SELECT max(ndsr_ins_amt)     AS max_nia,
                   max(ndsr_serv_charge) AS max_nsc
                from ndx_dsr_table
          ) AS mm  ON ndsr_cnno=dsr_cnno

Or, if necessary, build a temp table with that subquery, then LEFT JOIN to it.

(Since you have not qualified each column with the table it is in, I can't be more specific.)

Do you have suitable 'composite' indexes for the various JOINs?

According to the EXPLAIN, it is scanning 182M rows of dsr_table. So, my index, above, is likely to help (if you don't already have a similar one.)

I hesitate to suggest such a long index, but this might help:

INDEX(DSR_BOOKED_BY, dsr_booking_date,  -- these first, in this order
      dsr_cnno, dsr_status, dsr_cnno, dsr_dist_pin,  -- in any order
      id)   -- (whatever the PK of the table is); last

Bad problem in second query

        WHERE  dsr_booking_date = '2017-04-30'
          AND  '2017-05-30'

Perhaps you meant 31 days:

        WHERE  dsr_booking_date BETWEEN '2017-04-30'
                                   AND  '2017-05-30'

Or maybe 2 days:

        WHERE  dsr_booking_date IN ('2017-04-30', '2017-05-30')

What you have is

        WHERE  dsr_booking_date = '2017-04-30'  -- test for one day
          AND  true  -- that's how '2017-05-30' is interpreted

edited Aug 16 '18 at 20:19

answered Jul 18 '18 at 02:19

Rick James

135,179
13
127
222

Rick should we rewrite the query? – Pami Jul 18 '18 at 02:26
@Pami - Rewriting the query _may_ help. Currently, I can't get a good feel for it. Why all the ifnulls; why the huge derived query; what the various flags mean; where it is spending most of the time. Etc. – Rick James Jul 18 '18 at 02:30
adding indexes also did not help :( – Pami Jul 18 '18 at 05:35
i have added the create table structure also,. – Pami Jul 18 '18 at 07:29
@Pami - How long does the query take now? How long to do `SELECT dsr_cnno FROM .. WHERE ..` without the subqueries and without the JOINs? – Rick James Jul 18 '18 at 16:20
it takes approximately close to 2 min. Please suggest how i can get the performance faster – Pami Jul 19 '18 at 09:35
This is the query that was fired SELECT `DSR_CNNO` FROM `dsr_table` WHERE dsr_booking_date BETWEEN '2017-05-01' AND '2017-06-30' – Pami Jul 19 '18 at 09:38
@Pami - That simple`SELECT` is optimized by `INDEX(dsr_booking_date, DSR_CNNO)` (or any index _starting_ with those 2 columns _in that order_. But the big query benefits by sticking `DSR_BOOKED_BY` _before_ those 2. – Rick James Jul 19 '18 at 15:58
@RickJames but still after we add the index also the query is not running. How can we make it work? It would be of great help if you can suggest me some thing on this? how can i make things faster – Sathish D Jul 20 '18 at 05:18
Please explain the 6 `LIKEs` in the `WHERE`; maybe we can improve on it by formulating it some other way. – Rick James Jul 20 '18 at 05:45
I have removed likes even then results are not getting fetched, but when I fetching dsr_booking_date = '2017-05-01' for any particular date' I am able to get results. Please advise on this. – Sathish D Jul 24 '18 at 07:18
@SathishD - Are the `JOINs` in place? or absent? when you get no rows? Please provide a _short_ query that works and another, very similar, _short_ that does not work. – Rick James Jul 24 '18 at 15:58
@RickJames i have updated the question with the observation. that you asked. The joins are not in place. Please Advice on this. – Pami Jul 25 '18 at 05:47
@Pami - Please point out what is different between the queries. – Rick James Jul 25 '18 at 15:26
@RickJames WHERE dsr_booking_date = '2017-04-30' works fine. But when WHERE dsr_booking_date = '2017-04-30' AND '2017-05-30' The query takes ages and does not get any results. I have posted both the query in the update section. There is index on dsr_booking_date . Thats the problem – Pami Jul 26 '18 at 06:22
@SathishD also mentioned the same thing which i commented now. – Pami Jul 26 '18 at 06:25
@RickJames can you help on this? – Pami Jul 27 '18 at 06:13
@Pami - (Sorry for the delay) See the addition to my Answer. – Rick James Aug 16 '18 at 20:20

Explain plan in mysql performance using Using temporary; Using filesort ; Using index condition

1 Answers1

Linked

Related