recently, I was looking at the knowledge of MySQL indexes. When I saw that there was a leftmost principle in combinatorial indexes, I built a simple table to verify it. I encountered something I didn"t understand in the process. I would like to ask for advice here, mysql version 5.7
.CREATE TABLE `testp` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`users_id` int(10) unsigned NOT NULL COMMENT "ID",
`activity_id` int(11) NOT NULL COMMENT "ID",
`phone` varchar(20) NOT NULL COMMENT "",
PRIMARY KEY (`id`),
UNIQUE KEY `idx_activity_users` (`activity_id`,`users_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT="";
-- ----------------------------
-- Records of testp
-- ----------------------------
INSERT INTO `testp` VALUES ("1", "272002", "17", "12345678900");
INSERT INTO `testp` VALUES ("2", "290253", "19", "12345678900");
INSERT INTO `testp` VALUES ("3", "177652", "17", "12345678900");
INSERT INTO `testp` VALUES ("4", "36482", "17", "12345678900");
INSERT INTO `testp` VALUES ("5", "36482", "19", "12345678900");
INSERT INTO `testp` VALUES ("6", "290253", "17", "12345678900");
INSERT INTO `testp` VALUES ("7", "272207", "17", "12345678900");
INSERT INTO `testp` VALUES ("8", "272207", "19", "12345678900"); the structure of the table is as above.
combine activity_id and users_id . The information on navicat is as follows:
sql:
:
key
sql:
:
activity_id=17 activity_id=19:
:
results show that the index is used
that is to say, this has something to do with the value? Please answer, thank you!
because I thought it was the Unique type, I changed Unique to Normal and the result is the same.