Mysql – Super slow MariaDB 5.5 count(*) on large simple table

countindexmariadbMySQL

I'm doing some testing on a dedicated mediocre server (Core 2 2G ram). Before I upgrade to 8G ram, I am concerned something is very wrong here.

Simple queries have normal performance: 

MariaDB [testing]> select count(*) from domains where domain like 'super%';
+----------+
| count(*) |
+----------+
|    10868 |
+----------+
1 row in set (0.26 sec)

MariaDB [testing]> select count(domain) from domains where domain like 'slow%';
+---------------+
| count(domain) |
+---------------+
|           582 |
+---------------+
1 row in set (0.07 sec)

However count(*) and count(domain) are ridiculously slow:

MariaDB [testing]> select count(domain) from domains;
+---------------+
| count(domain) |
+---------------+
|      12802537 |
+---------------+
1 row in set (59 min 45.86 sec)

Explain claims it is using INDEX, yet it still shows full table scan.

MariaDB [testing]> explain select count(domain) from domains;
+------+-------------+---------+-------+---------------+--------+---------+------+----------+-------------+
| id   | select_type | table   | type  | possible_keys | key    | key_len | ref  | rows     | Extra       |
+------+-------------+---------+-------+---------------+--------+---------+------+----------+-------------+
|    1 | SIMPLE      | domains | index | NULL          | domain | 120     | NULL | 12762541 | Using index |
+------+-------------+---------+-------+---------------+--------+---------+------+----------+-------------+
1 row in set (0.00 sec)

Schema:

CREATE TABLE `domains` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `domain` char(120) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `domain` (`domain`)
) ENGINE=InnoDB AUTO_INCREMENT=14782143 DEFAULT CHARSET=latin1;

 MariaDB [testing]> desc domains;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| domain | char(120)        | NO   | UNI |         |                |
+--------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

my.cnf is pretty much default, except for

innodb_buffer_pool_size = 1600M

I am using deadline scheduler. No other queries are running. Nothing else is using the disk. Linux is not swapping.

Aside from ditching InnodDB, is there something else I can try?

Best Answer

It's reading the entire 12M rows in the index. Yes, it is doing an index scan, not a table scan, but that is really the table (only 2 columns), then the index is as big as the data.

Do not use CHAR(...) unless the data is constant length. I suspect the average length for a "domain" is somewhere around 20. Changing to VARHAR(120) will shrink the data and the index by a factor of maybe 5. This shrinkage will probably speed up the full scan by a factor of 5, and help the other queries some.

What was the setting of innodb_buffer_pool_size? It should not have been more than about 500M. The data is about 2GB, as is the index. So you had to read most or all of the index from disk. That is, the buffer_pool could not have cached it all, so you were I/O-bound.

Switching to 8GB of RAM and increasing innodb_buffer_pool_size to, say, 5500M, will perhaps let it cache the entire index, thereby running the full query in 6 seconds instead of 60. Add to that, my suggestion about CHAR, and it might go below 2 seconds.

Related Question