title: Optimizing my mysql query to use index for sorting
body: <p>I have a composite index based on 3 columns, two of which are constrained in my query and the 3rd is in order by clause yet mysql doesn't use index for sorting.</p>
  
  <pre>
  explain select * from videos where public_private='public' and approved='yes' order by number_of_views desc;
  
  +----+-------------+--------+------+--------------------------------+------+---------+------+---------+-----------------------------+
  | id | select_type | table  | type | possible_keys                  | key  | key_len | ref  | rows    | Extra     |
  +----+-------------+--------+------+--------------------------------+------+---------+------+---------+-----------------------------+
  |  1 | SIMPLE      | videos | ALL  | approved,approved_3,approved_2 | NULL | NULL    | NULL | 1476818 | Using where; Using filesort |
  +----+-------------+--------+------+--------------------------------+------+---------+------+---------+-----------------------------+
  </pre>
  
  <p>The table structure is as follows:</p>
  
  <pre><code>CREATE TABLE `videos` (
    `indexer` int(9) NOT NULL auto_increment,
    `user_id` int(9) default NULL,
    `public_private` varchar(24) default NULL,
    `approved` varchar(24) default NULL,
    `number_of_views` int(9) default NULL,
    PRIMARY KEY  (`indexer`),
    KEY `approved` (`approved`,`user_id`),
    KEY `approved_3` (`approved`,`public_private`,`indexer`),
    KEY `approved_2` (`approved`,`public_private`,`number_of_views`),
  ) ENGINE=MyISAM AUTO_INCREMENT=1969091 DEFAULT CHARSET=utf8 |
  </code></pre>
  
  <p>What should I do to force mysql to use index for sorting the results?</p>