带有内部子查询和where、order、group的Mysql查询不使用索引

Question

all。我有两个mysql表类型myisam

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
CREATE TABLE `users_ratings` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `created_date` varchar(255) DEFAULT NULL,
  `user_id` mediumint(9) DEFAULT NULL,
  `rating1` mediumint(9) DEFAULT NULL,
  `rating2` mediumint(9) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

转储可以在这里下载

我希望所有用户都按评分2选择和排序列表，评分2必须大于1000

SELECT * FROM users as u 
INNER JOIN (SELECT rating2, user_id,MAX(created_date) as maxdate FROM `users_ratings` GROUP BY user_id) as x ON x.user_id = u.id 
INNER JOIN users_ratings as ur ON(ur.created_date = x.maxdate and ur.user_id = u.id) 
WHERE x.rating2 > 1000 
ORDER by ur.rating2

如果没有这样的索引，查询将执行9秒。

所有可能的代码，我认为这些代码可以将请求的时间缩短到1-2秒。

帮助放置索引的权利

Answer 1

我怀疑您想要以下查询。假设您这样做了，并且性能仍然是一个问题，请提供相同的EXPLAIN。。。

(这假设user_id,[rating2,]created_date上有一个唯一密钥，但实际上尚未指定(

SELECT u.*
     , x.*
  FROM users u 
  JOIN users_ratings x
    ON x.user_id = u.id
  JOIN 
     ( SELECT user_id
            , MAX(created_date) maxdate 
         FROM users_ratings
        WHERE rating2 > 1000
        GROUP 
           BY user_id
     ) y 
    ON y.user_id = x.user_id 
   AND y.maxdate = x.created_date
 ORDER 
    BY x.rating2;