Shulou(Shulou.com)06/01 Report--

This article is about the difference between count(*), count(1) and count(col) in MySQL. Xiaobian thinks it is quite practical, so share it with everyone for reference. Let's follow Xiaobian and have a look.

count function

COUNT(expression): Returns the total number of records for the query, where the expression parameter is a field or an *.

test

MySQL version: 5.7.29

Create a user table and insert a million entries, with half a million rows for null in the gender field

CREATE TABLE `users`( `Id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',`name` varchar(32) DEFAULT NULL COMMENT ' name',`gender` varchar(20) DEFAULT NULL COMMENT 'gender',`create_date` datetime DEFAULT NULL COMMENT ' creation time', PRIMARY KEY (`Id`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT ='user table'; count(*)

Prior to MySQL 5.7.18, InnoDB processed statements by scanning clustered indexes. SELECT COUNT( *) Starting with MySQL 5.7.18, InnoDB processes SELECT COUNT( *) statements by traversing the smallest available secondary index, unless the index or optimizer prompt instructs the optimizer to use a different index. If the secondary index does not exist, the clustered index is scanned.

If there is a secondary index, use the secondary index. If there are multiple secondary indexes, choose the smallest secondary index first to reduce the cost. If there is no secondary index, use the clustered index.

Here are some tests to test these ideas.

First, query the execution plan when there is only one primary key index Id,

As you can see, type is index, which means index is used, key is PRIMARY, which means primary key index is used, key_len=8.

Next, add an index to the name field, and use the execution plan again to view

You can see that the index is also used, but the index is the index of the name field, key_len=99.

Then add an index to the create_date field while keeping the name field index, and look at the execution plan again

You can see that this time the index of the create_date field is used, key_len=6.

No matter which index is used, the total number of queries is one million, regardless of whether they contain NULL values.

count(1)

Count(1), like count(*), returns one million items, regardless of whether they contain NULL values.

count(col)

count(col) counts the value of a column, which is divided into three cases:

count(id): statistic id

And count(*) execution query results are the same, the final return is also a million data.

count(index col): count indexed fields

Query with count(name), execution plan is as follows:

You can see that the index field is used for statistics, and the index also hits.

Set the name field in a column to NULL, and then perform a count query. The result returns 99999.

Then set the NULL value of this column to an empty string, and then perform a count query, and the result returns 1000000.

Therefore, in summary, the simple use of index field count rows can hit the index, and only count rows that are not NULL values.

count(normal col): count fields without indexes

Counting fields without indexes does not use indexes, and only counts rows that are not NULL values.

count(1) and count(*)

InnoDB handles SELECT COUNT( *) and SELECT COUNT(1) operations in the same way. There is no performance difference.

InnoDB handles SELECT COUNT( *) and SELECT COUNT(1) operations in the same way, with no performance difference.

For MyISAM tables, COUNT(*) is optimized to return very quickly if retrieved from a table, no other columns are retrieved and no clauses are present, this optimization applies only to MyISAM tables because the exact number of rows is stored for this storage engine and can be accessed very quickly. COUNT(1) The same optimization is performed only if the first column is defined as NOT NULL. ---- From MySQL official website

These optimizations are based on the premise that there is no where and group by.

It is also mentioned in the Ali development specification

So count (*) is used instead of count(*) in development.

Thank you for reading! About "MySQL count(*), count(1), count(col) what is the difference" This article is shared here, I hope the above content can be of some help to everyone, so that we can learn more knowledge, if you think the article is good, you can share it to let more people see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.