11

I have a table with more than 15m rows. I need the total number of rows. So:

SELECT COUNT(*) FROM thetable;

Which takes around 50s to complete. Explaining gives me Select tables optimized away. I suppose this means that the result can be found only by using an index, then why does it still take so long? Here are some information about the index on the id column (It's non nullable):

Index Type: BTREE (clustered)

Cardinality: 14623100

Unique: YES

How can I improve the performance of this query? Thanks.

Note: The database is MySQL 5.7.1 and using InnoDB engine.

EDIT:

Create statement:

CREATE TABLE `properties` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `address` varchar(255) DEFAULT NULL,
  `locality` varchar(50) DEFAULT NULL,
  `latitude` decimal(13,9) DEFAULT NULL,
  `longitude` decimal(13,9) DEFAULT NULL,
  `state` varchar(10) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  .....
  PRIMARY KEY (`id`),
  KEY `index_properties_on_address` (`address`),
  KEY `index_properties_on_latitude` (`latitude`),
  KEY `index_properties_on_longitude` (`longitude`),
  KEY `index_properties_on_state` (`state`),
  KEY `index_properties_on_created_at` (`created_at`),
  .....
) ENGINE=InnoDB AUTO_INCREMENT=28267712 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED;

Note: I omitted some lines, there are 44 columns.

Explain plan:

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered |            Extra             |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1
  • @lunr InnoDB count without any condition is inherently slower as it has to go and count the rows one by one and check transactional visibility for each of them.
    – jkavalik
    Feb 29, 2016 at 13:58

4 Answers 4

8

Back when mysql was not transactionally sound by default (when people regularly used myISAM tables instead of InnoDB because that was the default or, going further back in time, because it didn't exist yet) "SELECT * FROM some_table" without any filtering clauses was one of the query types that peopel banged on about mySQL being much faster at that other database engines.

In a transactionally safe environment generally speaking the database engine will need to check every row and make sure that it should be visible to the current session (i.e. it isn't part of a transaction that is not yet committed (or wasn't committed at the start of this sessions active transaction) or is currently being rolled back) - checking every row implies needing to perform a table scan or (where one is present ) a clustered index scan.

It would be possible for the engine to keep track of the number of rows visible in each object for every active session/transaction, but presumably the designers have not judged this to be worth the extra processing involved so I assume it is not generally considered practical - I can imagine there would be some fairly complex locking requirements to deal with concurrency that would harm performance of other operations too much. You could implement this yourself by keeping a table in which is recorded the count of the rows in the table of interest, and have all your code meticulously maintain that value, but this would be quite some hassle and may be overly prone to errors due to bugs meaning that the count would drift from true over time (and you are probably adding a potential deadlock source and/or locking bottleneck at the application layer).

Situations where row-level security is in use complicate this even more - as well as needing to check the status of a row/page with respect to the current transaction, then engine needs to check again the current user too and as the security rules are dynamic it would be impractical to cache this information further necessitating the scan every time just-in-case. Row-level security is being added to MS SQL Server in the next release (https://msdn.microsoft.com/en-us/library/dn765131.aspx) and is already present in postgres (http://www.postgresql.org/docs/9.5/static/ddl-rowsecurity.html), I don't know about its status in other RDBMSs.

5

Complementing @david-spillett answer, you could change your query just by replacing the count(*) with a count(id) on your query, becoming:

SELECT COUNT(id) FROM thetable;

Being because the id column not null, indexed (actually it's the primary key), which means that it's not null for all the rows and, as so, there are as many ids as there are rows.

But, even if you replace count(*) with count(0), or count("Hi, I'm a row") you would have the same performance, because internally they result in the same operation. You can check it comparing the result of a EXPLAIN EXTENDED ... on all the queries:

EXPLAIN EXTENDED SELECT COUNT(*) FROM thetable;
EXPLAIN EXTENDED SELECT COUNT(id) FROM thetable;
EXPLAIN EXTENDED SELECT COUNT(0) FROM thetable;
EXPLAIN EXTENDED SELECT COUNT("Hi, I'm a row") FROM thetable;

Currently for InnoDB, select count(<whatever>) from table_name ;, without any conditions, is not the best practice.

This type of query performs better when:

  1. Your smallest index on the table is on a very small column (a tinyint, for example) instead of a composed index or on a big column (like a varchar(200)), but don't add it just to improve this type of selects. That's because with a smaller index, InnoDB has less data to scan;
  2. You add a WHERE criteria, narrowing the rows to count. This is your best option.
7
  • 4
    No. Check EXPLAIN EXTENDED select count(*) from table_name; show warnings; and you will see that count(*) is transformed to count(0) which is equivalent to count(PK) (or other not-null index). Actually any secondary InnoDB index can be used for that query (as they all contain the PK values) and it is usually faster than using the PK directly (less data to read).
    – jkavalik
    Mar 1, 2016 at 7:22
  • Learning every day. But what's specifically wrong with my answer? Mar 1, 2016 at 10:33
  • 1
    Well, the modification is not supposed to improve anything and imho count(*) is not a bad practice in itself. But reading it again if you meant count without conditions (no matter if * or id) then that would be a bad one :)
    – jkavalik
    Mar 1, 2016 at 11:23
  • Thank you very much for your comments. I've improved my response, or at least I tried, given your input. Mar 1, 2016 at 15:26
  • 2
    Another thing that is very easy to test in InnoDB. Make a wide table (say with some VARCHAR columns), a table_id int PRIMARY KEY and then add another index on (table_id). The count queries would use that index instead of the PK index - because by definition that's the narrowest index you can have in an InnoDB table. Mar 1, 2016 at 16:41
2

Make a new table (properties_count (id, count)), and use trigger for insert (increment count) and for delete (decrement count).

After, you can use: select count from properties_count.

0

if you could profile this query then we might have got more info on this issue. One thing for sure, the since the storage engine is InnoDB, the innodb buffers do have an impact.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.