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

This article shows you how to use COUNT in MySQL. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

COUNT () vs COUNT (COL): they are logically different. In MySQL, COUNT (COL) does not count the NULL column. For example, in the following pet table, COUNT () is different from COUNT (owner):

For Example

Select * from pet

+-+ +

| | owner | species |

+-+ +

| | Benny | bird |

| | Diane | bird |

| | Gwen | cat |

| | Harold | cat |

| | Adrian | dog |

| | NULL | dog |

+-+ +

Count (*) query result

SELECT species, COUNT (*) FROM pet GROUP BY species

+-+ +

| | species | COUNT (*) |

+-+ +

| | bird | 2 | |

| | cat | 2 | |

| | dog | 2 | |

+-+ +

Count (col) query results

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.

The above is how to use COUNT in MySQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.