MySQL Count (*), distinct usage and efficiency studies

When working on a large data volume database
It suddenly turns out that MySQL's different handling of count (*) can result in different results

such as execution
SELECT Count (*) from TableName
Even for TENS data, MySQL can return results very quickly.
And for
SELECT Count (*) from TableName WHERE .....
MySQL query time starts to climb

Read the manual carefully and find out that when there is no where statement for the count operation of the entire MySQL table
The total number of rows is saved in a table of type MyISAM, and MySQL needs to retrieve the entire table when a where-qualified statement is added
To derive the value of Count

A lot of new PHP programs that suddenly come to mind are not very well aware of the handling of count.
Record the

By the way, MySQL's distinct keyword has a lot of useful things you can't imagine.
1. Can be used when count does not duplicate records
For example, select COUNT (DISTINCT ID) from TableName;
is to calculate how many records have different IDs in the Talbebname table.

2, you can use it when you need to return a specific value for a different ID.
For example select DISTINCT ID from tablename;
Returns the specific value of a different ID in the Talbebname table

3. The above scenario 2 will be ambiguous if you need to return more than 2 columns in the MySQL table
For example, select DISTINCT ID, type from tablename;
In fact, the ID and type are not the same result, that is, distinct function two fields at the same time, it must be the same ID and Tyoe are excluded, and we expect the result is not the same

4. You may consider using the GROUP_CONCAT function to exclude, but this MySQL function is supported in the mysql4.1

5. In fact, there is another way to solve this problem is to use
SELECT ID, type, COUNT (DISTINCT ID) from tablename
Although such a return results in a column of useless count data (maybe you need this useless data I said)
The result returned is that all results with a different ID and the above 4 types can be used in a complementary way, just to see what kind of data you need.

Ps:
There are more and more details that need to be explored,
More and more I find myself writing words really messy,
To study the efficiency of distinct under a free time

MySQL Count (*), distinct usage and efficiency studies