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
Summary of tips for making web pages
Preface This article mainly summarizes some of th...
Example code for configuring monitoring items and aggregated graphics in Zabbix
1. Install Zabbix Agent to monitor the local mach...
Introduction to the difference between OBJECT and EMBED tags used to display flash content
1. Introduction: If we want to display flash conte...
Win2008 Server Security Check Steps Guide (Daily Maintenance Instructions)
The document has been written for a while, but I ...
Detailed explanation of the problems and solutions caused by floating elements
1. Problem Multiple floating elements cannot expa...
Vue recursively implements custom tree components
This article shares the specific code of Vue recu...
Minimalistic website design examples
Web Application Class 1. DownForEveryoneOrJustMe ...
Vue data responsiveness summary
Before talking about data responsiveness, we need...
Use label tag to select the radio button by clicking the text
The <label> tag defines a label (tag) for an...
How to prevent website content from being included in search engines
Usually the goal of building a website is to have...
Implementation of installing and uninstalling CUDA and CUDNN in Ubuntu
Table of contents Preface Install the graphics dr...
How to use not in to optimize MySql
Recently, when using select query in a project, I...
Remove the a label and button and add the background image dotted line/shadow perfect solution
When a user registers, they will click on a label...
Installation and use of mysql on Ubuntu (general version)
Regardless of which version of Ubuntu, installing...
How to install MySQL database on Ubuntu
Ubuntu is a free and open source desktop PC opera...