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

Mysql must be familiar to many programmers. Many people are obsessed with the use of count, how to query the best results. Today, let's talk about some of the editor's views, just for reference.

1. Let's first prepare to build a table and prepare the test data to facilitate the next step of testing.

Take the InnoDB engine table as an example

The statement of the table 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

The execution table statement is shown in the following figure

3. Then prepare the test data and simply check if there is any data, as shown in the following figure

4. Then start the test.

In the absence of where

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

We'll know as soon as we try. Please take a look at the picture below.

According to the results in the figure, it is obvious that count (field) is faster.

5. What about the situation where there are where conditions? Which is faster, count (*) or count (field)?

Please take a look at the execution effect of the following figure

Count (*) is faster, maybe the amount of data is too small to see the obvious effect.

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

Precede the executed sql with desc or explain, as shown in the following figure

Finally, let's sum up 1. In the absence of where, it is recommended that count (field) 2. Under the condition of where, it is suggested that count (*) summarize 1. In the absence of where, it is recommended that count (field) 2. Under where conditions, count (*) is recommended.

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.