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

@SergioTulentsev yes, SELECT oc_subject.* FROM oc_subject WHERE oc_subject.status='1' ORDER BY created DESC LIMIT 0, 50 — marlboroone, Sep 22 '19 at 08:36

score 3 · Accepted Answer · answered Sep 22 '19 at 08:41

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;

@BurakFidancı have you even defined the indexes that I mentioned ? — Madhur Bhaiya, Sep 22 '19 at 08:49
My God! My first query did not have any action, but I did try again after your comment and the result is incredible. 0.0034 seconds! — marlboroone, Sep 22 '19 at 08:53
@BurakFidancı the power of **Proper** indexing :-) Hope you learnt something new today! — Madhur Bhaiya, Sep 22 '19 at 08:54
@RickJames Date(Now() -Interval 1 day) will yesterday date, but then OP is using `>` operator so he want the date to be more than yesterday date, that means curdate — Madhur Bhaiya, Oct 06 '19 at 03:42

score 2 · Answer 2 · answered Sep 22 '19 at 08:39

Instead of several subquery (one of each row in your oc_subject table) you could try using a join on a single subquery grouped by sid, and date()

  SELECT oc_subject.*, b.count_sid
  FROM oc_subject 
  INNER JOIN  ( 
    SELECT sid, DATE(oc_details.created) date_created, COUNT(sid) count_sid
    FROM oc_details 
    GROUP BY  sid, DATE(oc_details.created)
  ) b on b.sid = oc_subject.id 
      AND b.date_created >  DATE(NOW() - INTERVAL 1 DAY) 
  WHERE oc_subject.status='1' 
  ORDER BY created DESC LIMIT " . (int)$start . ", " . (int)$limit;

anyway be careful using php var for limit .. be sure you use sanited values for avoid sqlinjection .

Thank you it took a total of 0.5658 seconds! – marlboroone Sep 22 '19 at 08:48 — marlboroone, Sep 22 '19 at 08:48

How can I improve SQL Count performance?

2 Answers2