How can I improve SQL Count performance?

Question

One of my SQL queries is very slow. I need to COUNT on a table with a total of close to 300,000 records, but it takes 8 seconds for the query to return results.

SELECT oc_subject.*, 
      (SELECT COUNT(sid) FROM oc_details 
       WHERE DATE(oc_details.created) > DATE(NOW() - INTERVAL 1 DAY) 
             AND oc_details.sid = oc_subject.id) as totalDetails 
FROM oc_subject 
WHERE oc_subject.status='1' 
ORDER BY created DESC LIMIT " . (int)$start . ", " . (int)$limit;

In this way: total 50, Query 8.5837 second

SELECT oc_subject.* 
FROM oc_subject 
WHERE oc_subject.status='1' 
ORDER BY created DESC LIMIT 0, 50 

Without Count: total 50, Query 0.0457 second

Answer 1

Lots of improvements possible:

• Firstly, let's talk about the outer query (main SELECT query) on the oc_subject table. This query can take the benefit of ORDER BY Optimization by using the composite index: (status, created). So, define the following index (if not defined already):

ALTER TABLE oc_subject ADD INDEX (status, created);

• Secondly, your subquery to get Count is not Sargeable, because of using Date() function on the column inside WHERE clause. Due to this, it cannot use indexes properly.

Also, DATE(oc_details.created) > DATE(NOW() - INTERVAL 1 DAY) simply means that you are trying to consider those details which are created on the current date (today). This can be simply written as: oc_details.created >= CURRENT_DATE . Trick here is that even if created column is of datetime type, MySQL will implictly typecast the CURRENT_DATE value to CURRENT_DATE 00:00:00.

So change the inner subquery to as follows:

SELECT COUNT(sid) 
FROM oc_details 
WHERE oc_details.created >= CURRENT_DATE
      AND oc_details.sid = oc_subject.id

• Now, all the improvements on inner subquery will only be useful when you have a proper index defined on the oc_details table. So, define the following Composite (and Covering) Index on the oc_details table: (sid, created). Note that the order of columns is important here because created is a Range condition, hence it should appear at the end. So, define the following index (if not defined already):

ALTER TABLE oc_details ADD INDEX (sid, created);

• Fourthly, in case of multi-table queries, it is advisable to use Aliasing, for code clarity (enhanced readability), and avoiding unambiguous behaviour.

So, once you have defined all the indexes (as discussed above), you can use the following query:

SELECT s.*, 
      (SELECT COUNT(d.sid) 
       FROM oc_details AS d
       WHERE d.created >= CURRENT_DATE
             AND d.sid = s.id) as totalDetails 
FROM oc_subject AS s
WHERE s.status='1' 
ORDER BY s.created DESC LIMIT " . (int)$start . ", " . (int)$limit;