Search code examples
mysqldatabase-performance

Select / order by desc / limit query speed

I see a big difference in speed between these two queries; the first one runs in 0.3 seconds, and the second in 76 seconds.

The first query only selects the key, whereas the second query select an additional field, which is an int(11). I can substitute the second field for any other field, same result. Selecting only the key is much faster for some reason? Can anyone possibly explain the huge difference in speed? I'm stumped by this.

Q1:

SELECT  ID 
FROM TRMSMain.tblcalldata  
WHERE  (
         CallStarted BETWEEN '2014/06/13' AND '2014/06/13 23:59:59') 
ORDER BY ID DESC LIMIT 0 , 50

Q2:

SELECT ID, Chanid 
FROMTRM SMain.tblcalldata  
WHERE (
       CallStarted BETWEEN '2014/06/13' AND '2014/06/13 23:59:59') 
ORDER BY ID DESC LIMIT 0 , 50

Regards

Solution

  • I suppose you have an index on your CallStarted column and ID is the primary key. Your first query can do a so-called range scan on that index, and retrieve the row identities quickly because secondary indexes on InnoDB also include the primary key.

    So it ends up doing just a little bit of work.

    Your second query has to fetch data from the main table. In particular it has to fetch the ChanID variable. It then has to sort the whole mess, grab 50 values from the end, and discard the rest of the sort.

    Do a deferred join to pick up the extra columns. That is, just sort the ID numbers, then grab the rest of the data you need from the table. That way you only have to grab 50 rows' worth of data.

    Like so:

    SELECT a.ID, a.Chanid, a.WhatEver, a.WhatElse
  FROM TRMSMain.tblcalldata a
  JOIN (
         SELECT ID
           FROM TRMSMain.tblcalldata
          WHERE CallStarted BETWEEN '2014/06/13' AND '2014/06/13 23:59:59'
          ORDER BY ID DESC
          LIMIT 0, 50
       ) b ON a.ID = b.ID 
 ORDER BY a.ID DESC

    You know the inner query is fast; you've proven that. The JOIN merely exploits that quickness (based on good use of indexes) to get the detail data for the rows it needs.

    Pro tip: Avoid BETWEEN for date/time ranges, because as you know it handles the end of the range poorly. This will perform just as well and avoid the 59:59 nonsense.

     WHERE CallStarted >= '2014/06/13' 
   AND CallStarted <  '2014/06/13' + INTERVAL 1 DAY

    It grabs records starting at midnight on June 13, and gets them all up to but not including (<) midnight on the next day.