Usage of the count function in mysql

The count function is a function used to count records in tables or arrays. Here we will introduce the usage and Performance Comparison of the count function in mysql.

Count (*) returns the number of rows to be retrieved, whether or not it contains a NULL value.
When SELECT is retrieved from a table, but no other columns are retrieved, and there is no WHERE clause, COUNT (*) is optimized to the fastest return speed.

For example:

The Code is as follows:	Copy code
Mysql> select count (*) FROM student;

This optimization only applies to MyISAM tables because these table types store the exact number of records returned by a function and are very easy to access.
For transaction-type storage engines (InnoDB and BDB), there are many problems with storing a precise number of rows because multiple transactions may occur,
However, each row may have an impact on the number of rows.

COUNT (DISTINCT field)
Returns the number of non-NULL values.
If no matching item is found, COUNT (DISTINCT) returns 0.

Example

Create a data table for testing for count statistics:

The Code is as follows:	Copy code
Create table 'user '( 'Id' int (5) unsigned not null AUTO_INCREMENT, 'Name' varchar (10) default null, 'Password' varchar (10) default null, Primary key ('id ') ) ENGINE = MyISAM AUTO_INCREMENT = 4 default charset = latin1 Test data: 1 name1 123456 2 name2 123456 3 name3 123456 4 name4 NULL

Note the returned results of the following query:
 

The Code is as follows:	Copy code
1, select count (*) from 'user' 2, select count (name) from 'user' 3, select count (password) from 'user'

Output results: 4, 3

Cause analysis:

1, count (*) is to count the number of rows, so the result is 4.
2. count (column_name) is used to count rows not empty in the column. Therefore, count (name) = 4, and count (password) = 3.
When using the count function, pay attention to the preceding two points.

If you use group by to GROUP all records of each owner without it, you will get the error message:

The Code is as follows:	Copy code
Mysql> SELECT owner, COUNT (*) FROM pet; ERROR 1140 (42000): Mixing of GROUP columns (MIN (), MAX (), COUNT (),...) With no GROUP columns is illegal if there is no group by clause

COUNT () and group by classify your data in various ways. The following example shows different methods for conducting an animal census.

Quantity of each animal:

The Code is as follows:	Copy code
Mysql> SELECT species, COUNT (*) FROM pet group by species; + --------- + ---------- + | Species | COUNT (*) | + --------- + ---------- + | Bird | 2 | | Cat | 2 | | Dog | 3 | | Hamster | 1 | | Snake | 1 | + --------- + ---------- +

Number of animals for each gender:

The Code is as follows:	Copy code
Mysql> SELECT sex, COUNT (*) FROM pet group by sex; + ------ + ---------- + | Sex | COUNT (*) | + ------ + ---------- + | NULL | 1 | | F | 4 | | M | 4 | + ------ + ---------- + (In this output, NULL indicates "unknown gender ".)

Number of animals by type and gender:

The Code is as follows:	Copy code
Mysql> SELECT species, sex, COUNT (*) FROM pet group by species, sex; + --------- + ------ + ---------- + | Species | sex | COUNT (*) | + --------- + ------ + ---------- + | Bird | NULL | 1 | | Bird | f | 1 | | Cat | f | 1 | | Cat | m | 1 | | Dog | f | 1 | | Dog | m | 2 | | Hamster | f | 1 | | Snake | m | 1 | + --------- + ------ + ---------- +

If you use COUNT (), you do not have to retrieve the entire table. For example, in the previous query, when only a dog or a cat is executed, it should be:

The Code is as follows:	Copy code
Mysql> SELECT species, sex, COUNT (*) FROM pet -> WHERE species = 'Dog' OR species = 'cat' -> Group by species, sex; + --------- + ------ + ---------- + | Species | sex | COUNT (*) | + --------- + ------ + ---------- + | Cat | f | 1 | | Cat | m | 1 | | Dog | f | 1 | | Dog | m | 2 | + --------- + ------ + ---------- +

Or, if you only need to know the number of sex-based animals with known gender:

The Code is as follows:	Copy code
Mysql> SELECT species, sex, COUNT (*) FROM pet -> WHERE sex IS NOT NULL -> Group by species, sex; + --------- + ------ + ---------- + | Species | sex | COUNT (*) | + --------- + ------ + ---------- + | Bird | f | 1 | | Cat | f | 1 | | Cat | m | 1 | | Dog | f | 1 | | Dog | m | 2 | | Hamster | f | 1 | | Snake | m | 1 | + --------- + ------ + ---------- +

By the way, mysql's DISTINCT keywords are of a lot of unexpected use.

1. It can be used when count does not repeat records
For example, select count (DISTINCT id) FROM tablename;
Calculate the number of records with different IDs in the talbebname table.

2. You can use
For example, select distinct id FROM tablename;
Returns the specific values of different IDs in the talbebname table.

3. In case 2 above, there will be ambiguity when you need to return results with more than two columns in the mysql table
For example, select distinct id, type FROM tablename;
In fact, the returned result is a result of different IDs and types, that is, DISTINCT serves two fields at the same time. It must be the same id and tyoe to be excluded. It is different from the expected result.

4. At this time, you can use the group_concat function for troubleshooting. However, this mysql function is supported only after mysql4.1.

5. Another solution is to use
SELECT id, type, count (DISTINCT id) FROM tablename
Although such a returned result contains a column of useless count data (You may need this useless data)
The returned result is that only the results with different IDS can be used in concert with the above four types. It depends on what data you need.