Summary of methods to improve mysql count

I believe many programmers are familiar with MySQL. Many people are struggling with the usage of count and how to get the best query results. Let’s talk about some of my opinions today, for reference only.

1. Let's first build a table and prepare test data to facilitate subsequent testing steps

Take the InnoDB engine table as an example

The table creation statement is as follows

CREATE TABLE test.test

(

    a VARCHAR(50) NOT NULL COMMENT 'ddfdf',

    b VARCHAR(15) NOT NULL COMMENT 'fds',

    c VARCHAR(20) NOT NULL COMMENT 'asda',

    d VARCHAR(8) NOT NULL COMMENT 'ads',

    e longblob NOT NULL COMMENT 'asda',

    f VARCHAR(2000) COMMENT 'ads',

    g VARCHAR(8) NOT NULL COMMENT 'assd',

    h DATE NOT NULL COMMENT 'adsad',

    z VARCHAR(10) NOT NULL COMMENT 'adsd'

)

ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Log in to MySQL and change the database

Execute the table creation statement as shown below

3. Then prepare the test data and simply check whether there is data, as shown in the figure below

4. Next, start testing

In the absence of a where condition

Some people think count(*) is faster than count(field), while others think count(field) is faster than count(*)?

So which one is faster? Let's try it and see. Please see the picture below.

According to the results in the figure, count(field) is obviously faster.

5. What about the case where there is a where condition ? Which is faster, count(*) or count(field)?

Please see the execution effect in the figure below

count(*) is faster, but the data size may be too small to see obvious results.

6. Of course, you can analyze it by viewing the execution plan

Just add desc or explain before the executed SQL, as shown in the following figure

Finally, let's summarize 1. If there is no where condition, it is recommended to count(field) 2. If there is a where condition, it is recommended to count(*) Finally, let's summarize 1. If there is no where condition, it is recommended to count(field) 2. If there is a where condition, it is recommended to count(*)