title: Mysql query with inner subquery and where,order,group not use indexes
body: <p>all.
  I have 2 mysql tables type myisam</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>Dump can be downloaded <a href="https://drive.google.com/file/d/0B7LDFMVGJQZfOWpnWjNqNkhfM2s/view?usp=sharing" rel="nofollow">here</a></p>
  
  <p>I want all users to select and sort the list by rating2 and rating2 must be > 1000</p>
  
  <pre><code>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 &gt; 1000 
  ORDER by ur.rating2
  </code></pre>
  
  <p>Without such an index the query is executed for 9 seconds.</p>
  
  <p>All possible codes, which I thought was allowed to reduce the time of the request to 1-2 seconds.</p>
  
  <p>Help the right to place indexes</p>