How to use COUNT in MySQL
MySQL how to use COUNT, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.
COUNT () vs COUNT (COL): they are not used logically. In MySQL, COUNT (COL) does not count the NULL column. For example, in the following pet table, COUNT () is different from COUNT (owner):
Select * from pet
+-+ +
| | owner | species |
+-+ +
| | Benny | bird |
| | Diane | bird |
| | Gwen | cat |
| | Harold | cat |
| | Adrian | dog |
| | NULL | dog |
+-+ +
SELECT species, COUNT (*) FROM pet GROUP BY species
+-+ +
| | species | COUNT (*) |
+-+ +
| | bird | 2 | |
| | cat | 2 | |
| | dog | 2 | |
+-+ +
SELECT species, COUNT (owner) FROM pet GROUP BY species
+-+ +
| | species | COUNT (owner) |
+-+ +
| | bird | 2 | |
| | cat | 2 | |
| | dog | 1 | |
+-+ +
The difference of COUNT (*) in different engines: the MyISAM storage engine saves the total number of rows, and without the WHERE condition, the performance of the MyISAM table is significantly better than that of the INNODB table. The official manual.
COUNT () vs COUNT (VAL): they are equivalent; full table scans are required in INNODB and appropriate indexes are used. INNODB to see whether the table has a secondary index, if there is a secondary index, count () operation will overwrite the index, go to overwrite the index will be faster, using EXPLAIN can see Using index. COUNT () is semantically different from SELECT (), and SELECT () is not recommended because it does not use an overlay index. MySQL query Analyzer optimizes count (*) after version 5.6
COUNT (*) vs COUNT (COL) vs COUNT (VAL):
In the absence of WHERE conditions: COUNT (*) is approximately equal to COUNT (competitive) better than COUNT (non-primary key indexed) better than COUNT (non-primary key without index).
Unless you want to count the total number of non-null values of a column, use COUNT (*) in any case to let the query analyzer automatically select the index to achieve high efficiency.
Unless there is a special need, COUNT () do not add the WHERE condition, which will seriously affect the efficiency. If the conditional COUNT () and COUNT (competitive) efficiency are the same, the COUNT (non-primary key) efficiency is very low.
In the case of multi-table queries, MySQL does not support COUNT (TABLENAME.*) writing.
Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.
