The leftmost principle of MySql composite index is invalid.

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.

Mysql
Oct.29,2021

the mysql optimizer thinks that a full table scan is less expensive than using an index, so it gives up using an index.

if it is 

EXPLAIN SELECT activity_id FROM `testp` WHERE activity_id=17 ;
Indexes should always be used if

, because there is no need to return to the table

< hr >

give an extreme example where forcibly moving an index may be slower than a direct full table scan

for the table in question, if the data in the table is as follows 

  
 Oracle officially states that whether to go through the index depends on whether the data to be queried is less than 30% of the whole table. It is obvious that the number of rows in activity_id=17 has exceeded 30% of the total. 
 secondly, the use of the index involves the optimizer's judgment, and you need to understand that the Mysql optimizer is based on cost, that is, the execution plan is based on whichever cost is low.

Previous: What does this statement mean in go language?

Next: What is the problem with setting the root password by mysql?

css mysql arrays josn react html typescript webpack npm sass R objective-c .net sql-server jquery python-3.x angularjs django angular excel regex iphone ajax linux xml pandas vba spring database wordpress string wpf xcode windows bash postgresql oracle multithreading eclipse list firebase algorithm macos forms image scala visual-studio azure bootstrap spring-boot react-native python-2.7 docker performance function winforms matlab powershell apache dataframe api sqlite numpy rest shell selenium flutter dart maven loops qt swing android-studio csv express file class tensorflow sorting codeigniter perl
MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1b3ef8a-409e2.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1b3ef8a-409e2.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?