CODEWITHSUNDEEP
mysqlsqlperformancedatabase-performancesqlperformance
marlboroone
marlboroone

Reputation: 39

How can I improve SQL Count performance?

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

Upvotes: 2

Views: 137

Answers (2)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

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;

Upvotes: 3

ScaisEdge
ScaisEdge

Reputation: 133360

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 .

Upvotes: 2

Related Questions