Skip to content

Multiple COUNT () in a single SQL query

chronometre performance - Multiple COUNT () in a single SQL query

During the development of a site or an application it is very likely to have to count certain information with COUNT (). For example a community website can count the number of new messages and the number of notifications when the user logs in. This article explains how and why you need to perform a single query for all COUNT ().

Interest

Making several calls to a database takes a relatively long time. Calls should be reduced as much as possible. Therefore, for better performance it is advisable to avoid carrying out these 3 promptings:

SELECT COUNT(*) FROM table1;
SELECT COUNT(*) FROM table2;
SELECT COUNT(*) FROM table2 WHERE is_valid = 1;

Syntax for grouping several queries into one

The 3 queries presented below can be grouped into 1 by using the following syntax:

SELECT
(SELECT COUNT(*) FROM table1) as count1,
(SELECT COUNT(*) FROM table2) as count2,
(SELECT COUNT(*) FROM table2 WHERE is_valid = 1) as count3

This query will return a record containing the values ​​count1, count2 and count3.

Utility

Here is a small non-exhaustive list of all the features that can be counted within this single SQL query:

  • Count the number of registered members
  • Count the number of connected members
  • Count the number of connected visitors
  • Count new unread messages from a member
  • Count the number of notifications for a member

Go further

To go a little more and reduce the server load, it is also possible to cache these values. But this no longer reflects SQL but the rest of the application.

This content was posted in Best Practices.

About the author: Tony Archambeau

With several years of experience in web development, Tony shares his knowledge on various projects including the infowebmaster.fr site. It is possible to follow it on Twitter.

window.___gcfg = {lang: 'fr'}; (function() { var po = document.createElement('script'); po.type="text/javascript"; po.async = true; po.src="https://apis.google.com/js/plusone.js"; var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(po, s); })();

var _gaq = _gaq || []; _gaq.push(['_setAccount', 'UA-1599467-30']); _gaq.push(['_trackPageview']); _gaq.push(['_trackPageLoadTime']); (function() { var ga = document.createElement('script'); ga.type="text/javascript"; ga.async = true; ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js'; var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s); })();