Mysql Count (*), distinct efficiency study

Source: Internet
Author: User
Tags mysql query mysql tutorial php tutorial

I suddenly find that the MySQL tutorial will produce different results for the different processing of count (*)

such as execution
Select COUNT (*) from TableName
MySQL can return results very quickly even for tens other data.
And for
Select COUNT (*) from TableName where .....
MySQL query time starts to climb

Check out the tired manual and find that when there is no where statement for the entire MySQL table to count
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
Which results in the number of the count.

Suddenly remembered to see many of the emerging PHP tutorial program for the handling of count is not very well aware of this
Record the

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

2, when you need to return a record of the specific values of different IDs can be used
For example, select Distinct ID from tablename;
Returns the specific values of different IDs in the Talbebname table

3. The above situation 2 is ambiguous when you need to return the results of more than 2 columns in the MySQL table.
For example, select Distinct ID, type from tablename;
Actually returns the result that ID and type are not the same at the same time, that is, distinct two fields at the same time, must have ID and Tyoe are the same to be excluded, and we expect the result is not the same

4. You can consider using the GROUP_CONCAT function to exclude this time, but this MySQL function is supported above mysql4.1

5. In fact, there is another way to solve the problem is to use
Select ID, type, COUNT (distinct ID) from TableName
Although the result of this return is a list of useless count data (perhaps you need this useless data I said)
The result of the return is that all the results with different IDs and 4 types above can be used in a complementary way to see what kind of data you need.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.