What is the function of the max () function in mysql database
This article introduces the relevant knowledge of "what is the function of mysql database max () function". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!
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 NOT NULL DEFAULT CURRENT_TIMESTAMP, `remarkid` varchar `update_ time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)
Test query speed:
Mysql > select max (create_time) from coupon_use_test;+-+ | max (create_time) | +-+ | 2016-06-25 16:44:25 | +-+ 1 row in set (2.01 sec)
View the execution plan:
Mysql > explain select max (create_time) from coupon_use_test +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+ -- +-+ | 1 | SIMPLE | coupon_use_test | NULL | ALL | NULL | 1706101 | 100.00 | NULL | +-+-- -+ 1 row in set 1 warning (0.00 sec)
Create a create_time field index
Mysql > alter table coupon_use_test add index idx_create_time (create_time); Query OK, 0 rows affected (17.49 sec) Records: 0 Duplicates: 0 Warnings: 0
Query again:
Mysql > select max (create_time) from coupon_use_test;+-+ | max (create_time) | +-+ | 0-06-25 16:44:25 | +-+ 1 row in set (0.00 sec)
View the execution plan:
Mysql > explain select max (create_time) from coupon_use_test +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- +-+-+ | 1 | SIMPLE | NULL | Select tables optimized away | + -+- -+ 1 row in set 1 warning (0.00 sec)
The index is ordered, and the speed of fetching max (create_time) becomes faster after the create_time field is indexed.
See other optimization methods, which can be realized by changing the way of SQL query.
Mysql > select create_time from coupon_use_test order by create_time desc limit 1 row in set + | create_time | +-+ | 0-06-25 16:44:25 | +-+ 1 row in set (sec)
View the execution plan:
Mysql > explain select create_time from coupon_use_test order by create_time desc limit 1 +- -+-+ | 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 | 4 | | NULL | 1 | 100.00 | Using index | +-+-- | -+ 1 row in set 1 warning (0.01sec) "what is the function of the mysql database max () function" is introduced here. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!
