Use and efficiency of COUNT (*) and DISTINCT in MySQL database

MySQLForCOUNT (*)For example,

Execute: select count (*) FROM tablename. Even for mysql with tens of millions of data, results can be returned very quickly.

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.

Some Usage of MySQL DISTINCT keywords:

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.

Here is an introduction to the use of MySQL database COUNT (*) and DISTINCT. I hope this introduction will bring you some benefits. Thank you!