The difference analysis of the select count of Mysql technique _mysql

1. Test environment

Os:linux
db:mysql-5.5.18
TABLE:INNODB Storage Engine

The table is defined as follows:

2. Test scenario and analysis "number of records Group_message"

(1) Select COUNT (*) method

(2) Select COUNT (1) method

(3) Select COUNT (col_name) method

Used separately

Select COUNT (group_id)

Select COUNT (user_id)

Select COUNT (col_null)

You can see from the above test results that both select COUNT (*) and select COUNT (1) Use the shortest two-level index of group_id. Some people may ask why not use a shorter primary key index "int type", this is mainly because the InnoDB storage engine, the primary key index in essence contains the index and data, scanning primary key index is actually scanning physical records, the cost is the most substantial. Take a look at several select count (col_name), and Count (group_id) uses the shortest two-level index because the column is an indexed column, and COUNT (USER_ID) uses a combined index, because user_id cannot use the index. But scanning the index can also get the number of records, and less than the cost of scanning physical records, this should be a MySQL optimization; count (col_null) can not use the index, because the column contains null values, so the least efficient. In addition, for rows that contain null values, COUNT (col_null) does not actually count, which is inconsistent with the original intention of the number of tables you want to record, such as the test table has 852,226 records, but the Col_null column is only 1 rows is not empty, then the statistical results are as follows:

3. Test conclusion

In MySQL, it's good to use COUNT (*) or COUNT (1) When you need to record the number of SELCT count tables.