Comparison of usage methods of distinct and count of * in MySQL

First of all for MySQL DISTINCT keywords 1 some usage:

1. Can be used when count does not repeat records, such as SELECT COUNT (DISTINCT id) FROM tablename; This is to calculate how many different id records there are in the talbebname table.

2. It can be used when it is necessary to return the specific value of different id records, such as SELECT DISTINCT id FROM tablename; Returns the specific value of the different id in the talbebname table.

3. Case 2 above is ambiguous when it is necessary to return results with more than 2 columns in mysql table, such as SELECT DISTINCT id, type FROM tablename. In fact, what is returned is the result that id and type are different at the same time, that is, DISTINCT affects both fields at the same time, so it must be the same as id and tyoe to be excluded, which is not the same as the expected result.

4. Consider using the group_concat function for exclusion, but the mysql function is only supported above mysql4.1.

5. Actually there is another kind of solution, 1 is used, SELECT id, type, count (DISTINCT id) FROM tablename, although such return results for more than 1 column useless count data (perhaps you need the useless data) I said, all the returned result is only id different results and the above 4 types can be used complementary, is to see what kind of data you need.
Efficiency of DISTINCT:

SELECT id, type, count (DISTINCT id) FROM tablename; Although this returns one more column of useless count data (or maybe you need this useless data), SELECT id, type from group by id; This seems to work as well. When using distinct, if it has an index, mysql will convert it to group by.

Different processing of MySQL (*) in the MySQL database will result in different results, for example,

Implementation: SELECT COUNT (*) FROM tablename; mysql returns results very quickly even for ten-million level data.
Implementation: SELECT COUNT (*) FROM tablename WHERE... . ; Query times for mysql began to climb.

According to online information search, when WHERE statement is not used for count operation of the entire mysql table, the total number of rows in the TABLE of MyISAM type is preserved, while when WHERE qualified statement is added, Mysql needs to retrieve the whole table, thus obtaining the value of count. Therefore, the query speed with where condition will be very slow.
Above on MySQL database distinct and count(*) use is introduced here, I hope this introduction can bring you 1 harvest.