Summary of methods for improving mysql count
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.
