MySQL InnoDB count (*) slow and inaccurate solution

The InnoDB engine is statistically different from MyISAM, and the MyISAM has a built-in counter, so when using select COUNT (*) from table, the data can be fetched directly from the counter. The InnoDB must be scanned all the time in order to get the total number. To solve this problem initially, we need to do some work that is different from MyISAM:

1, use the second index (generally do not use the primary key index), and add a where condition, such as:

Copy CodeThe code is as follows:
Select COUNT (*) from product where comp_id>=0;
Show index from product;
ID PRIMARY Key
COMP_ID Index

2. If you need only rough statistics, you can also use

Show status from product; To get the approximate value
This method can be used in data paging!

3, the use of external counters, such as the establishment of a trigger to count or on the program to use the cache on a timed count, the flaw is that these methods will consume some additional resources!

===========================

We know that when MySQL uses InnoDB, the Count table record count will change at any time, especially when the delete and insert are more frequent, this problem has been noticed before, but because of a long time no attention, forget, today especially on the server tested a bit, An outrageous statistic appears each time count (select COUNT (*) from user)

There are several ways to solve the problem, please add:
1. Query full table data by adding an indexed field after the where to count the full table records, such as: select count (tid) from posts where tid>0; TID is not a primary key, and Tid has an index

2, using InnoDB table to insert data, do not delete, delete data through a field to identify, isdel=1 means to delete 0 is normal, so that select count (*) from posts data is relatively accurate some

3, through the counter to record the table data total number of rows, add is 1, delete minus 1; So the number of table records is the fastest, and is the most complex

MySQL InnoDB count (*) slow and inaccurate solution