The use of distinct and count (*) in Mysql is compared _mysql

First of all, some uses of MySQL's distinct keywords:

1. This can be used when count does not duplicate records, such as SELECT COUNT (DISTINCT ID) from tablename , or how many records with different IDs in the Talbebname table are counted.

2, you can use when you need to return a specific value for a different ID, such as a SELECT DISTINCT ID from tablename , and return a specific value for a different ID in the Talbebname table.

3. The above scenario 2 is ambiguous when you need to return the results of more than 2 columns in the MySQL table, such as the SELECT DISTINCT ID, type from tablename , which actually returns the result that the ID and type are not the same. That is, distinct also action two fields, must have ID and Tyoe are the same before being excluded, and we expect the result is not the same.

4. This is the time to consider using the GROUP_CONCAT function for elimination, but this MySQL function is in MYSQL4. More than 1 to support.

5. In fact, there is another way to solve this problem is to use theSELECT ID, type, COUNT (DISTINCT ID) from TableName, Although this return result is a lot of useless count data (perhaps you need this useless data I said), the result is that only the ID of the different results and the above 4 types can be complementary to use, is to see what kind of data you need.
efficiency of distinct:

Select ID, type, COUNT (DISTINCT ID) from tablename; This return result is a list of useless count data (perhaps you'll need this useless data I'm talking about), theselect ID, Type from tablename group by ID; So it looks like it, and when you use DISTINCT, if it has an index, MySQL will turn it into a group by in the way it executes.

The MySQL database will have different results for the different processing of count (*), for example,

Execution:SELECT COUNT (*) from TableName, and MySQL can return results very quickly even for tens data.
Execute: SELECT COUNT (*) from tablename WHERE ...; MySQL's query time began to climb.

Online data: When there is no where statement for the entire MySQL table for the count operation, the MyISAM type of table to save the total number of rows, and when the addition of where to define the statement MySQL needs to retrieve the entire table, so as to get the value of count, Therefore, the query speed with the where condition will be very slow.
The above about MySQL database distinct and the use of count (*) is introduced here, I hope this introduction can bring you some harvest.