MySQL Query stuck on 'Sending Data', doesn't seem to ever finish

Question

I have a query that goes through each hour of my book_records table and gets the number of books that are taken (value = 1) vs the number of books that are free (value = 0):

SELECT sr.time AS h,
       COUNT(CASE WHEN sr.value = 1 THEN 1 END) AS taken,
       COUNT(CASE WHEN sr.value = 0 THEN 1 END) AS free,
       sr.libID AS libID
FROM `book_records` AS sr
WHERE sr.time BETWEEN '2017-01-01 00:00:00' AND '2017-01-01 23:00:00' 
AND MINUTE(sr.time) = 0
AND libID = 0 
GROUP BY h, libID
ORDER BY h, libID

The main line to look at here is this:

WHERE sr.time BETWEEN '2017-01-01 00:00:00' AND '2017-01-01 23:00:00' 

This takes about 0.03s to query.

If I change it to this (5 days):

WHERE sr.time BETWEEN '2017-01-01 00:00:00' AND '2017-01-05 23:00:00' 

It takes about 0.15s.

10 days:

WHERE sr.time BETWEEN '2017-01-01 00:00:00' AND '2017-01-10 23:00:00'

It takes about 0.28s

But at 15 days:

WHERE sr.time BETWEEN '2017-01-01 00:00:00' AND '2017-01-15 23:00:00'

At the time of writing this, the query is still going. SHOW FULL PROCESSLIST tells me the query's state is set to 'Sleep' with the time going up.

So what gives? Is there something in my MySQL 5.7 configuration that might be causing this? The query speed is fine at 1, 5, and 10 days, but 5 days more (15) and the query gets put in a Sleep state? Why?

EDIT: SHOW FULL PROCESSLIST output:

| 2234 | phpmyadmin | localhost | NULL    | Sleep   |     4 |              | NULL
| 2235 | root       | localhost | library | Query   |     4 | Sending data | 
SELECT SQL_CALC_FOUND_ROWS sr.time AS h, 
COUNT(CASE WHEN sr.value = 1 THEN 1 END) AS taken, 
COUNT(CASE WHEN sr.value = 0 THEN 1 END) AS free, sr.libID AS libID FROM `book_records` AS sr
WHERE sr.time BETWEEN '2017-01-01 00:00:00' AND '2017-01-15 23:00:00' 
AND MINUTE(sr.time) = 0
AND libID = 0 
GROUP BY h, libID
ORDER BY h, libID LIMIT 1 |

Answer 1

Let's take a look at your query.

SELECT sr.time AS h, 
       COUNT(CASE WHEN sr.value = 1 THEN 1 END) AS taken, 
       COUNT(CASE WHEN sr.value = 0 THEN 1 END) AS free, 
       sr.libID AS libID
  FROM `book_records` AS sr
WHERE sr.time BETWEEN '2017-01-01 00:00:00' AND '2017-01-01 23:00:00' 
  AND MINUTE(sr.time) = 0
  AND libID = 0 
GROUP BY h, libID
ORDER BY h, libID

First, you need a compound index on book_records (libID, time) for your query to be sargable. Unsargable queries need full table scans, which are slow.

Next, your selection criterion sr.time BETWEEN '2017-01-01 00:00:00' AND '2017-01-01 23:00:00' is a little strange. To get all the records for the day 1-July-2017. you need to use these criteria

       sr.time >= '2017-01-01 00:00:00'
   AND sr.time <  '2017-01-02 00:00:00'

Why? You want all records with times starting at midnight on 1-July, up until but not including midnight on 2-July.

Next, you have this selection criterion. MINUTE(sr.time) = 0. It removes many records from your result set, and keeps others. For example it removes a record with the time 10:03:00 and keeps a record with the time 10:00:59. That is probably not what you want. Eliminate that criterion; it makes the query non-sargable as well as selecting a strange set of rows.

Next, it looks like you are trying to present your results summarized by hour of the day. To do that you need GROUP BY HOUR(sr.time). That will give you the number of records by hour of the day, even when you include multiple days in your filter range.

Fourth, and less importantly, you can make this query even faster by creating an index on three columns (libID, time, value) rather than just two. That's called a covering index because it contains all the rows needed by your query. The query can be satisfied entirely from the index, so MySQL doesn't need to read both the index and the table. Create this index using

   CREATE INDEX book_records_id_time_val ON book_records (libID, time, value);

At the end of this your query looks like

SELECT HOUR(sr.time) AS h, 
       COUNT(CASE WHEN sr.value = 1 THEN 1 END) AS taken, 
       COUNT(CASE WHEN sr.value = 0 THEN 1 END) AS free, 
       sr.libID AS libID
  FROM `book_records` AS sr
 WHERE sr.time >= '2017-01-01 00:00:00'
   AND sr.time <  '2017-01-02 00:00:00'
   AND libID = 0 
 GROUP BY HOUR(sr.time), libID
 ORDER BY HOUR(sr.time), libID

Those changes should make your query more accurate and much much faster.

By the way to see a running query you must issue SHOW FULL PROCESSLIST from a different MySQL connection (another client program or another query window) than the one running the query. When a connection shows sleep it's not doing anything.