title: mysql query scanning all rows
body: <p>Guys trying to wrap my head around mysql query on why it is scanning all rows in table</p>
  
  <p>I have 2 tables topic_entry and topic_user</p>
  
  <pre><code>CREATE TABLE `topic_entry` (
    `entry_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `topic_id` bigint(20) unsigned NOT NULL,
    `entry_created` datetime NOT NULL,
    `entry_object` text,
    `level` tinyint(4) NOT NULL DEFAULT '3',
    PRIMARY KEY (`entry_id`),
    KEY `entry_created` (`entry_created`),
    KEY `level` (`level`),
    KEY `topic_id_2` (`topic_id`,`entry_id`)
  ) ENGINE=MyISAM;
  
  CREATE TABLE `topic_user` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `topic_id` bigint(20) unsigned NOT NULL,
    `user_id` varchar(100) NOT NULL,
    `private` enum('y','n') DEFAULT NULL,
    `freq` enum('a','d','w') DEFAULT NULL,
    `topic_id_freq` varchar(10) DEFAULT NULL,
    `casematch` enum('0','1') DEFAULT '0',
    `textmatch` enum('0','1') DEFAULT '0',
    `topic_name_case` varchar(100) DEFAULT '',
    PRIMARY KEY (`id`),
    UNIQUE KEY `topic_id_user_id` (`topic_id`,`user_id`),
    KEY `user_id` (`user_id`),
    KEY `topic_id_freq` (`topic_id_freq`)
  ) ENGINE=MyISAM;
  </code></pre>
  
  <p>This is the query that i am trying to run</p>
  
  <pre><code>explain 
  select  te.topic_id,te.entry_id 
  from topic_entry te 
  WHERE te.topic_id in (select topic_id 
                          from topic_user where user_id ='xyz') 
      AND te.level=4 
      AND te.entry_id &gt; 0 
  ORDER BY te.entry_id DESC 
  LIMIT 5;
  </code></pre>
  
  <p>The explain output shows that it is scanning all rows</p>
  
  <pre><code>|  1 | PRIMARY| te | range  | PRIMARY,level | PRIMARY | 8 | NULL| **722978** | Using where              | 
  |  2 | DEPENDENT SUBQUERY | topic_user | unique_subquery | topic_id_user_id,user_id | topic_id_user_id | 310     | func,const |      1 | Using index; Using where |
  </code></pre>