Will the index be used in the MySQL query condition?
|
When an employer asks you whether an index will be used in a MySQL query condition, how should you answer?
Let’s test it out 1. Create a table and index the field port
CREATE TABLE `pre_request_logs_20180524` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ip` char(16) NOT NULL COMMENT 'Proxy IP',
`port` int(8) NOT NULL COMMENT 'Port number',
`status` enum('success','failure') NOT NULL COMMENT 'status',
`create_time` datetime NOT NULL COMMENT 'Creation time',
`update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_port` (`port`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COMMENT='Proxy IP request log';Insert test data INSERT INTO ``(`id`, `ip`, `port`, `status`, `create_time`, `update_time`) VALUES (1, '192.168.1.199', 53149, 'Failed', '2018-05-24 14:55:34', '2018-11-16 10:58:13'); INSERT INTO ``(`id`, `ip`, `port`, `status`, `create_time`, `update_time`) VALUES (2, '192.168.1.100', 10653, 'Success', '2018-05-24 14:55:54', '2018-11-16 10:58:13'); INSERT INTO ``(`id`, `ip`, `port`, `status`, `create_time`, `update_time`) VALUES (3, '192.168.1.112', 50359, 'Failed', '2018-05-24 14:56:00', '2018-11-16 10:58:13'); INSERT INTO ``(`id`, `ip`, `port`, `status`, `create_time`, `update_time`) VALUES (4, '192.168.1.67', 30426, 'Failed', '2018-05-24 14:56:09', '2018-11-16 10:58:13'); INSERT INTO ``(`id`, `ip`, `port`, `status`, `create_time`, `update_time`) VALUES (5, '192.168.1.209', 49323, 'Failed', '2018-05-24 14:56:12', '2018-11-16 10:58:13'); INSERT INTO ``(`id`, `ip`, `port`, `status`, `create_time`, `update_time`) VALUES (6, '192.168.1.209', 51161, 'Success', '2018-05-24 14:56:13', '2018-11-16 10:58:13'); INSERT INTO ``(`id`, `ip`, `port`, `status`, `create_time`, `update_time`) VALUES (7, '192.168.1.12', 54167, 'Success', '2018-05-24 14:56:16', '2018-11-16 10:58:13'); INSERT INTO ``(`id`, `ip`, `port`, `status`, `create_time`, `update_time`) VALUES (8, '192.168.1.64', 20462, 'Success', '2018-05-24 14:56:19', '2018-11-16 10:58:13'); INSERT INTO ``(`id`, `ip`, `port`, `status`, `create_time`, `update_time`) VALUES (9, '192.168.1.53', 22823, 'Failed', '2018-05-24 14:56:31', '2018-11-16 10:58:13'); INSERT INTO ``(`id`, `ip`, `port`, `status`, `create_time`, `update_time`) VALUES (10, '192.168.1.85', 48229, 'Success', '2018-05-24 14:56:32', '2018-11-16 11:01:11'); INSERT INTO ``(`id`, `ip`, `port`, `status`, `create_time`, `update_time`) VALUES (11, '192.168.1.85', 48229, 'Success', '2018-05-24 14:56:32', '2018-11-16 11:01:15'); INSERT INTO ``(`id`, `ip`, `port`, `status`, `create_time`, `update_time`) VALUES (12, '192.168.1.85', 48229, 'Success', '2018-05-24 14:56:32', '2018-11-16 13:34:37'); 2. Test SQL explain select * from pre_request_logs_20180524 where port in (51161,20462,48229); Execution Results
From the results, it seems that the index is not used, but don't jump to conclusions. Let's look at the two SQL statements. select * from pre_request_logs_20180524 where port in (51161,48229); select * from pre_request_logs_20180524 where port in (51161,20462); The execution results are as follows
You can see that the second SQL statement uses an index. The difference between the two SQL statements is that the port value is different. One contains 48229 and the other contains 20462. In fact, the MySQL optimizer will automatically determine whether in uses the secondary index, that is, the index of the port field. Summarize The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. You may also be interested in:
|
Recommend
CSS implements horizontal scrolling navigation bar on mobile devices (also applicable to PC devices)
Function Origin I was recently working on an H5 t...
The solution of html2canvas that pictures cannot be captured normally
question First, let me talk about the problem I e...
HTML simple shopping quantity applet
This article shares a simple HTML shopping quanti...
After installing MySQL, the root account prompt appears when logging in. mysql ERROR 1045 (28000): Access denied for use solution
After installing MySQL, you will find that the ro...
Is your website suitable for IE8?
During the Olympic Games, IE 8 Beta 2 will be rele...
Analysis of two usages of the a tag in HTML post request
Two examples of the use of the a tag in HTML post...
How to implement animation transition effect on the front end
Table of contents Introduction Traditional transi...
Solve MySQL deadlock routine by updating different indexes
The previous articles introduced how to debug loc...
MySQL partitions existing tables in the data table
Table of contents How to operate Operation proces...
Front-end JavaScript housekeeper package.json
Table of contents 1. Required attributes 1. name ...
JS asynchronous code unit testing magic Promise
Table of contents Preface Promise chaining MDN Er...
Setting up a proxy server using nginx
Nginx can use its reverse proxy function to imple...
MySQL gets the current date and time function
Get the current date + time (date + time) functio...
Simple usage of MySQL temporary tables
MySQL temporary tables are very useful when we ne...
Vue Beginner's Guide: Environment Building and Getting Started
Table of contents Initial Vue Building a Vue deve...