sql文のwhere条件順序とインデックスの順序

表を作る

CREATE TABLE `Problem_submit` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `status` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `Username` varchar(30) NOT NULL,
  `problem_id` int(11) NOT NULL,
  `time_used` int(11) NOT NULL,
  `memory_used` int(11) NOT NULL,
  `submit_date` datetime NOT NULL,
  `ip` char(15) NOT NULL,
  `compiler_id` int(11) NOT NULL,
  `source` longtext NOT NULL,
  `code_length` int(11) NOT NULL,
  `error_message` longtext,
  `contest_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`,`submit_date`),
  KEY `contest_id,problem_id,status         ` (`contest_id`,`problem_id`,`status`) USING BTREE,
  KEY `problem_id   id  ` (`problem_id`,`id`) USING BTREE COMMENT 'id      ',
  KEY `status   id  ` (`status`,`id`) USING BTREE COMMENT 'id      ',
  KEY `compiler_id   id  ` (`compiler_id`,`id`) USING BTREE COMMENT 'id      ',
  KEY `user_id,status         ` (`user_id`,`status`,`id`) USING BTREE,
  KEY `user_id   id  ` (`user_id`,`id`) USING BTREE,
  KEY `contest_id   id  ` (`contest_id`,`id`) USING BTREE,
  KEY `contest_id,problem_id       ` (`contest_id`,`problem_id`,`id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=1060256 DEFAULT CHARSET=utf8;

クエリの実験:

select * from Problem_submit where user_id=200 and status =1

select * from Problem_submit where status =1 and user_id=200

select * from Problem_submit where status =2 and user_id=4358 and compiler_id=1

select * from Problem_submit where status =2 and compiler_id=1 and user_id=4358はインデックスを移動します.Mysqlはクエリの計画最適化

を行うからです.