To count the number of rows in a table

Queries such as select count(*) from tables for applications that need to import data in real-time are not problematic when the number of data is small, but the more data you get, the greater the performance problem. This is usually resolved by placing a separate table to store the total number of data.

For example, in a bulletin board application, a bulletin board count table is created to get a bulletin board list, and every time you insert a post, you do +1 and -1 when you delete it. And you have to combine the post insert query and the count update query into a single transaction to ensure data integrity.

The exact number of rows cannot be obtained with the method you wrote down.

Based on the question, I think the storage engine is innodb and the only way to accurately count the lows is select count(*) from table

Even if you endure inaccuracies, it is difficult to be used for paging because you cannot select lows that meet certain conditions.

For InnoDB tables, the row count is only a rough estimate used in SQL optimization. (This is also true if the InnoDB table is partitioned.)

Information-functions

Usually, when you're worried about this, it may depend on the answer to the compatibility or portability issue first, in this case, "Is it possible to operate with a database other than MySQL in the future?"

If you're only going to use MySQL, the second thing to worry about is, "Are you curious about the total number of records?" or "Are you curious about the number of results retrieved under certain conditions?"

First of all, only "total number of records" can be known for the query contained in the question. Because you cannot determine the number of records in the table that meet certain conditions (for example, a statement with a "keyword" in the title), you face the same problem again.

Simply put, if there's no chance of implantation (or giving up) and knowing the total number of records is an important goal, you can use it as it is.

Correction answer

https://dev.mysql.com/doc/refman/5.7/en/tables-table.html

For InnoDB tables, the row count is only a rough estimate used in SQL optimization. (This is also true if the InnoDB table is partitioned.)

In the case of InnoDB type, the document says that it provides rough (roughly) calculated (estimated) information for performance reasons.

TABLE_ROWS (and perhaps some other columns in INFORMATION_SCHEMA.TABLES) may get out of sync with the current table contents, but you can update it by running ANALYZE.

And when you run the ANALYZE statement, it's said to renew it again.

And refer to the next article.

http://www.tocker.ca/2013/05/02/fastest-way-to-count-rows-in-a-table.html