MariaDB/MySQL: Performances of COUNT()

Versione italiana

How fast is COUNT() execution? Well, it depends from the Storage Engine.

Try to create an Aria or MyISAM table, INSERT some data, and execute an EXPLAIN similar to the following:

MariaDB [(none)]> EXPLAIN SELECT COUNT(*) FROM test.t1;
+------+-------------+-------+------+---------------+------+--------+------+------+------------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+------+-------------+-------+------+---------------+------+--------+------+------+------------------------------+
|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+------+-------------+-------+------+---------------+------+--------+------+------+------------------------------+
1 row in set (0.10 sec)

Optimized away in this case means that there is no need to access the table data, because the number of rows is stored in table’s metadata. So, the results are always retrieved instantly.

For COUNT(col_name), the table is not optimized away – unless the column is NOT NULL and you are not using DISTINCT, but that syntax is equivalent to COUNT(*).

InnoDB cannot optimize away the tables from these queries, because it has a more complex way to handle data. But of course, it can use an index to get the number of values in a column.

The Storage Engines which optimize away the COUNT() operations are Aria, MyISAM, MEMORY, and the ugly thing called MRG_MyISAM. The Storage Engine which have to access the tables are InnoDB, XtraDB, Archive and CSV. (I only made a quick test on MariaDB 5.5 default engines)

Is this a problem for you? Before switching to Aria/MyISAM, consider an alternative: keeping the row count in a very small MEMORY table without changing the original table’s engine.

Enjoy!

About these ads

Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s