Shulou(Shulou.com)06/01 Report--

When the employee asks you if in will use the index in the MySQL query condition, what should you say?

Answer: an index may be used

Let's test it.

1. Create a table to index the field port

CREATE TABLE `enum 20180524` (`id`int (11) NOT NULL AUTO_INCREMENT, `ip`port` (16) NOT NULL COMMENT 'proxy IP', `port`int (8) NOT NULL COMMENT' port number', `status`enum ('success', 'failure') NOT NULL COMMENT 'status, `create_ datetime NOT NULL COMMENT' creation time', `update_ time`datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_ port` (`port`) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COMMENT=' agent IP request log'

Insert test data

INSERT INTO ``(`id`, `ip`, `port`, `status`, `update_ time`) VALUES (1, '192.168.1.199, 53149,' failure', '2018-05-24 1455 VALUES 34,' 2018-11-16 10 5813') INSERT INTO ``(`id`, `ip`, `port`, `status`, `update_ time`) VALUES (2, '192.168.1.100mm, 10653,' success', '2018-05-24 1455 VALUES 54,' 2018-11-16 10 5813') INSERT INTO ``(`id`, `ip`, `port`, `status`, `update_ time`) VALUES (3, '192.168.1.112, 50359,' failure', '2018-05-24 1456 VALUES,' 2018-11-16 10 5813') INSERT INTO ``(`id`, `ip`, `port`, `status`, `update_ time`) VALUES (4, '192.168.1.67 colors, 30426,' failure', '2018-05-24 1456 VALUES 09 colors,' 2018-11-16 10 5813') INSERT INTO ``(`id`, `ip`, `port`, `status`, `update_ time`) VALUES (5, '192.168.1.209 colors, 49323,' failure', '2018-05-24 1456 VALUES 12 colors,' 2018-11-16 10 5813') INSERT INTO ``(`id`, `ip`, `port`, `status`, `update_ time`) VALUES (6, '192.168.1.209 VALUES, 51161,' success', '2018-05-24 1456 VALUES 13,' 2018-11-16 10 140 58 13') INSERT INTO ``(`id`, `ip`, `port`, `status`, `update_ time`) VALUES (7, '192.168.1.12 percent, 54167,' success', '2018-05-24 1456 VALUES 16,' 2018-11-16 10 5813') INSERT INTO ``(`id`, `ip`, `port`, `status`, `status`) VALUES (8, '192.168.1.64, 20462,' success', '2018-05-24 1456 VALUES 19,' 2018-11-16 10 5813') INSERT INTO ``(`id`, `ip`, `port`, `status`, `update_ time`) VALUES (9, '192.168.1.53 percent, 22823,' failure', '2018-05-24 1456 VALUES 31,' 2018-11-16 10 5813') INSERT INTO ``(`id`, `ip`, `port`, `status`, `update_ time`) VALUES (10, '192.168.1.85 percent, 48229,' success', '2018-05-24 1456 VALUES 32 colors,' 2018-11-16 11 update_ 0111') INSERT INTO ``(`id`, `ip`, `port`, `status`, `status`) VALUES (11, '192.168.1.85 percent, 48229,' success', '2018-05-24 1456 VALUES 32,' 2018-11-16 11 update_ 0115') INSERT INTO ``(`id`, `ip`, `port`, `status`, `update_ time`) VALUES (12, '192.168.1.85 percent, 48229,' success', '2018-05-24 1456 VALUES 32,' 2018-11-16 13 update_ 34u37')

two。 Test sql

Explain select * from pre_request_logs_20180524 where port in (51161, 20462, 48229)

Execution result

Judging from the results, the index is not used, but don't jump to conclusions. Look at the two sql.

Select * from pre_request_logs_20180524 where port in (51161 Magi 48229); select * from pre_request_logs_20180524 where port in (51161 Magi 20462)

The execution results are as follows

You can see that the index is used in the second sql. The difference between the two sql 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 takes the secondary index, that is, the index of the port field.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support.

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.