The execution difference between count (1), COUNT (*) and Count (column name)

Source: Internet
Author: User

Execution effect:

1. Count (1) and COUNT (*) when the table has a larger amount of data, using count (1) is more time-consuming than using count (*) when analyzing the table. From the execution plan, the effect of count (1) and COUNT (*) is the same.    However, after the table has been parsed, count (1) will be less time-consuming than count (*) (1w of data), but not much. If Count (1) is a clustered index, ID, that must be count (1) fast.  But the difference is very small. Because Count (*), automatically optimizes the assigned field. So there is no need to go to count (1), with Count (*), SQL will help you complete the optimization therefore:count (1) and COUNT (*) are basically no different。 2. The main difference between count (1) and count (field) is (1) count (1) The number of records in the table,records containing NULL in fields。 (2) count (field) counts the number of times the field appears in the table, ignoring the case of NULL. Thatrecord with no statistics field nullturn from: http://www.cnblogs.com/Dhouse/p/6734837.html

The difference between count (*) and COUNT (1) and Count (column name)
effect on ExecutionCOUNT (*) includes all columns, equivalent to the number of rows, at the time of the statistical results,Column values are not ignored null
Count (1)
This includes ignoring all columns, representing lines of code with 1, and when the results are statisticallycolumn values are not ignored null
Count (column name)
Contains only the column names, and when the results are counted, the column values are ignored (null is not an empty string or 0, but null).that is, when a field value is null, it does not count
efficiency in execution:
The column name is the primary key, and count (column name) is faster than count (1)
The column name is not the primary key, and COUNT (1) is faster than count (column name)
COUNT (1) is more efficient than count (*) If there are multiple columns in the table and there are no primary keys.
Select COUNT (primary key) performs the optimal execution efficiency if there is a primary key
Select COUNT (*) is optimal if the table has only one field.turn from: http://eeeewwwqq.iteye.com/blog/1972576


Example Analysis

Mysql> CREATE TABLE Counttest (name char (1), age char (2)); Query OK, 0 rows affected (0.03 sec) mysql> insert into counttest values-> (' A ', ' '), (' A ', ', '), (' A ', ' 15 ')
),-> (' B ', null), (' B ', ', '),-> (' C ', ', '),-> (' d ', null),-> (' e ', ');
Query OK, 8 rows affected (0.01 sec) records:8 duplicates:0 warnings:0 mysql> select * from Counttest; +------+------+
| name |
Age | +------+------+
| A | 14 | | A | 15 | | A | 15 | | B | NULL | | B | 16 | | C | 17 | | D | NULL | |      e |
| +------+------+ 8 rows in Set (0.00 sec) mysql> Select name, count (name), COUNT (1), COUNT (*), count (age), COUNT (Distin
CT (age))-> to Counttest-> Group by name; +------+-------------+----------+----------+------------+----------------------+
| name | COUNT (name) | COUNT (1) | COUNT (*) | Count (age) |
COUNT (distinct (age)) | +------+-------------+----------+----------+------------+----------------------+
|           A |        3 |        3 |          3 |                    3 | 2 | |           B |        2 |        2 |          2 |                    1 | 1 | |           C |        1 |        1 |          1 |                    1 | 1 | |           D |        1 |        1 |          1 |                    0 | 0 | |           e |        1 |        1 |          1 |                    1 |
1 | +------+-------------+----------+----------+------------+----------------------+ 5 rows in Set (0.00 sec)


Additional references: http://blog.csdn.net/lihuarongaini/article/details/68485838




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.