Shulou(Shulou.com)05/31 Report--

This article mainly explains "introduction to the usage of count () sentence in mysql". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the introduction to the usage of the count () sentence in mysql.

View the table structure:

Mysql > show create table coupon_use_test\ gateway * 1. Row * * Table: coupon_use_testCreate Table: CREATE TABLE `coupon_use_ test` (`id` int (11) NOT NULL DEFAULT '0mm, `user_ id` varchar (40) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL `use_ code 'varchar (40) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT'', `status` varchar (2) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT'00', `use_ time` datetime DEFAULT NULL, `remark1` varchar (200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `remark2` varchar (200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `remark3` varchar (200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `create_ time` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `remarkid` varchar `update_ time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY `idx_create_ time` (`create_ time`) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

Check the number of rows with blank create_time field

Mysql > select * from coupon_use_test where create_time is null;Empty set (0.00 sec)

Change the record create_time with id 1 to empty

Mysql > update coupon_use_test set create_time = null where id = 1 row affected query OK, 1 row affected (6.56 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > select count (*) from coupon_use_test where create_time is null;+-+ | count (*) | +-+ | 1 | +-+ 1 row in set (0.00 sec)

Count (*)

Mysql > select count (*) from coupon_use_test;+-+ | count (*) | +-+ | 1800000 | +-+ 1 row in set (0.69 sec) mysql > explain select count (*) from coupon_use_test +-- + -+-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+-+ | 1 | SIMPLE | coupon_use_test | NULL | index | NULL | idx _ create_time | 5 | NULL | 1771323 | 100.00 | Using index | +-- +- -+ 1 row in set 1 warning (0.00 sec)

You can see that count (*) has taken the index idx_create_time of the create_time field.

Count (1)

Mysql > select count (1) from coupon_use_test;+-+ | count (1) | +-+ | 1800000 | +-+ 1 row in set (0.63 sec) mysql > explain select count (1) from coupon_use_test +-- + -+-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+-+ | 1 | SIMPLE | coupon_use_test | NULL | index | NULL | idx _ create_time | 5 | NULL | 1771323 | 100.00 | Using index | +-- +- -+ 1 row in set 1 warning (0.00 sec)

Count (create_time)

Mysql > select count (create_time) from coupon_use_test;+-+ | count (create_time) | +-+ | 1799999 | +-+ 1 row in set (0.73 sec) mysql > explain select count (create_time) from coupon_use_test +-- + -+-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+-+ | 1 | SIMPLE | coupon_use_test | NULL | index | NULL | idx _ create_time | 5 | NULL | 1771323 | 100.00 | Using index | +-- +- -+ 1 row in set 1 warning (0.00 sec)

Count (*) and count (1) can query the total number of rows in the whole table, and the number of rows queried by count (create_time) does not include null.

Count (1) compares with count (*):

1 > if the data table does not have a primary key, then count (1) is faster than count (*)

2 > if there is a primary key, the primary key (federated primary key) is also faster as a count condition than count (*).

3 > if your table has only one field, then count (*) is the fastest

Without WHERE restrictions, COUNT (*) and COUNT (COL) can basically be considered equivalent, but in the case of WHERE constraints, COUNT (*) is much faster than COUNT (COL).

COUNT (*) usually scans the index of the primary key, while COUNT (COL) is not necessarily. In addition, the former is the total number of records of all matching records in the statistical table, while the latter is the number of records of all COL matches in the calculation table.

The comparison between count (*) and count (1) mainly depends on the data field corresponding to count (1). If count (1) is a clustered id, it must be count (1) fast, but the difference is very small, because count (*) automatically optimizes which field is assigned, so there is no need to count (?) Optimization will be completed automatically with count (*) sql

1 > SELECT COUNT (*) FROM tablename is the best choice in any case

2 > minimize queries like SELECT COUNT (*) FROM tablename WHERE COL = 'value'

3 > stop the emergence of SELECT COUNT (COL) FROM tablename

At this point, I believe you have a deeper understanding of "introduction to the usage of count () sentence in mysql". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.