Comparison of distinct and count (*) in MySQL _ MySQL

This article mainly compares the usage of distinct and count (*) in MySQL, and analyzes the usage and efficiency of the two in detail, interested friends can refer to some usage of MySQL DISTINCT keywords first:

1. it can be used when count records are not repeated. for example, select count (DISTINCT id) FROM tablename; is used to calculate the number of records with different IDs in the talbebname table.

2. it can be used to return the specific values of different IDs, for example, select distinct id FROM tablename; the specific values of different IDs in the talbebname table are returned.

3. in case 2 above, there will be ambiguity when you need to return results with more than two columns in the mysql table, such as select distinct id and type FROM tablename; in fact, the returned results are results with different IDs and types at the same time, that is, DISTINCT applies to two fields at the same time and must have 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. in fact, there is another solution: SELECT id, type, count (DISTINCT id) FROM tablename, although this returned result contains a column of useless count data (maybe you need this useless data ), the returned result is that only the results with different IDs can be used together with the above four types, which is to see what data you need.
Efficiency of DISTINCT:

SELECT id, type, count (DISTINCT id) FROM tablename; although this returned result contains a column of useless count data (maybe you need this useless data ), SELECT id, type from tablename group by id; this is also acceptable. When distinct is used, if it has an index, mysql will convert it to the group by method for execution.

Different MySQL database processing of COUNT (*) results in different results, for example,

Execute: select count (*) FROM tablename; mysql can return results very quickly even for tens of millions of data.
Run: select count (*) FROM tablename WHERE .....; Mysql Query time began to rise.

Online Query Information: When no WHERE statement is used to calculate the count of the entire mysql table, the MyISAM table stores the total number of rows, when a WHERE statement is added, Mysql needs to search the entire table to obtain the count value. Therefore, the query speed with the where condition is slow.
Here we will introduce the use of MySQL database distinct and count (*). I hope this introduction will bring you some benefits.