建表
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会进行查询的计划优化