MySQL simple count query (no WHERE clause)

A common query in SQL queries should be a count operation. There is not much to say about queries with a where clause. If there is an index, an index will be used, and no index will be available.
Simple count queries without where clauses are different for different storage engines.

 

Assume a requirement:
We have a table that stores a large amount of user information. A page needs to display the user data list. We naturally cannot immediately select all the data and place it on the page. Therefore, we need to support page turning. For example, only 100 data entries are displayed on each page. Of course, this can be easily implemented through limit. However, after page turning is supported, another requirement is introduced. We need to display the page number and total number of pages. This requires you to know the total number of current users. We may need to execute such a simple count query SQL: Select count (*) from t_userinfo

 

Comparison of simple count queries on different storage engines

The original idea was: for such count queries without a where clause, MySQL should be able to return a value quickly without having to count the total number of rows.
After explaining the two engines, we found that they were not exactly the same.
ForInnoDBEngine. The explain result is as follows:

Mysql> explain select count (*) from t_userinfo;
+ ---- + ------------- + ------------------ + ------- + --------------- + --------- + ------ + ------------- +
| ID | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
+ ---- + ------------- + ------------------ + ------- + --------------- + --------- + ------ + ------------- +
| 1 | simple | t_userinfo | index | null | primary | 4 | null | 481 | using index |
+ ---- + ------------- + ------------------ + ------- + --------------- + --------- + ------ + ------------- +

Obviously, InnoDB uses indexes because t_userinfo contains indexes, but even so, results are returned after the indexes are traversed.

ForMyISAMEngine. The explain result is as follows:

Mysql> explain select count (*) from t_userinfo;
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + -------------------------------- +
| ID | select_type | table | type | possible_keys | key | key_len | ref | rows | extra |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + -------------------------------- +
| 1 | simple | null | select tables optimized away |
+ ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + -------------------------------- +

The output result is slightly odd. The extra column outputs the "select tables optimized away" statement. This is not mentioned in the MySQL manual, but it can be guessed by reading the data of other columns: the Select operation is no longer optimized (MySQL returns data without traversing the table or indexing ).

The above points are confirmed by turning the MySQL official site to two sections. The original article is as follows:

For explains on simple count queries (I. e. explain select count (*) from people) the extra section will read "select tables optimized away. "this is due to the fact that MySQL can read the result directly from the table internals and therefore does not need to perform the select.

...... ......

Select count (*) is so common so it is partly optimized away.
If you are using MyISAM or heap tables it reads the information directly from the table information which is lightning fast and is actually the information that show Table Status displays.
But for InnoDB tables it actually has to perform an index scan on the primary index which can take a while depending on size of table, innodb_buffer_size, hardware etc.

 

Suggestions

Therefore, if your count query is only intended to be executed on the MyISAM or heap engine, you can directly use the preceding statements without any performance problems and no special optimization is required. If it is executed on InnoDB or you are not sure whether it will be executed on InnoDB in the future, you may need to consider the performance issues.

If not ),The following statement is recommended.:

Show table status like 't_ userinfo ';

For MyISAM or heap engines, the number of rows returned by this query is an accurate value; For InnoDB, the number of rows returned by this query is an approximate value (in fact, for InnoDB, the vast majority of field values returned by this query are approximate values ).

In a case, the number of rows returned by the show table status query in the InnoDB engine is 519, while that returned by the Select count (*) is 476. This table has an auto-increment field FID. The result returned by select max (FID) is 488.
In another example, the number of rows returned by the show table status query in the InnoDB engine is 487, while that returned by the Select count (*) is 480. This table has an auto-increment field FID. The result returned by select max (FID) is 490.
As you can see, this value is basically very close. To learn why such an approximate value is generated, read the InnoDB documentation.

Of course, the "show Table Status" statement is a MySQL-specific statement rather than a standard SQL statement. For some considerations, this solution is unacceptable, so for performanceAnother suggestion is to create a counter table., Stores various count.
A common counting table field is as follows:

{Table_name, where_clause, group_clause, Count}