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
Docker Stack deployment method steps for web cluster
Docker is becoming more and more mature and its f...
Detailed tutorial on deploying Django project under CentOS
Basic Environment Pagoda installation service [Py...
How to find the my.ini configuration file in MySQL 5.6 under Windows
Make a note so you can come back and check it lat...
Linux tac command implementation example
1. Command Introduction The tac (reverse order of...
Echarts Bar horizontal bar chart example code
Table of contents Horizontal bar chart Dynamicall...
Detailed installation tutorial of mysql 5.7.11 under Win7 system
Operating system: Win7 64-bit Ultimate Edition My...
CSS3 realizes particle animation effect when matching kings
When coding, you will find that many things have ...
Detailed explanation of Linux system software installation commands based on Debian (recommended)
Introduction to Debian Debian in a broad sense re...
Detailed explanation of Mencached cache configuration based on Nginx
Introduction Memcached is a distributed caching s...
MySQL 8.0.15 installation and configuration method graphic tutorial under win10 home version 64
As a super rookie, I just started learning MySQL ...
Simple steps to write custom instructions in Vue3.0
Preface Vue provides a wealth of built-in directi...
mysql5.7.21 utf8 encoding problem and solution in Mac environment
1. Goal: Change the value of character_set_server...
Solve the problem that MySQL read-write separation causes data not to be selected after insert
MySQL sets up independent writing separation. If ...
React concurrent function experience (front-end concurrent mode)
React is an open-source JavaScript library used b...
MySQL establishes efficient index example analysis
This article uses examples to describe how to cre...