title: why use a second column of a key in where is so slow to return result
body: <p>I have a table op_log , all record count is <code>47362198</code>. and have below colums and index,</p>
  
  <pre><code>`id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `operate_result` varchar(100) DEFAULT NULL,
  `create_user_id` varchar(128) DEFAULT NULL
  
   PRIMARY KEY (`id`),
   KEY `user_id` (`user_id`,`create_user_id`),
  </code></pre>
  
  <p>And actually <code>user_id</code> is null, only used <code>create_user_id</code>, first I execute below sql</p>
  
  <pre><code>select * from op_log where operate_result like 'foo%'  order by id desc limit 10;
  ......
  10 rows in set (0.01 sec)
  </code></pre>
  
  <p>Then I add a condition of <code>create_user_id</code>, </p>
  
  <pre><code>select * from op_log where operate_result like 'foo%' and create_user_id = '4987bcbabc7530ee4c777038184d364b'  order by id desc limit 10;
  </code></pre>
  
  <p>very strange it seems it will execute forever, I have to CTRL+C to stop it, then I add another condition of user_id(actually user_id never used so it's null)</p>
  
  <pre><code>select * from op_log where operate_result like 'foo%' and create_user_id = '4987bcbabc7530ee4c777038184d364b'  and user_id is null order by id desc limit 10;
  ...
  1 row in set (0.01 sec)
  </code></pre>
  
  <p>So why only have <code>create_user_id</code> in where could execute for ever, but either not have it or both have it with <code>user_id</code> could execute quickly?</p>
  
  <pre><code>explain select * from op_log where operate_result like 'foo%'  order by id desc limit 10;
  +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
  | id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
  +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
  |  1 | SIMPLE      | op_log | index | NULL          | PRIMARY | 8       | NULL |   10 | Using where |
  +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
  
  explain select * from op_log where operate_result like 'foo%' and create_user_id = '4987bcbabc7530ee4c777038184d364b'  order by id desc limit 10;
  +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
  | id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
  +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
  |  1 | SIMPLE      | op_log | index | NULL          | PRIMARY | 8       | NULL |   10 | Using where |
  +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
  
  explain select * from op_log where operate_result like 'foo%' and create_user_id = '4987bcbabc7530ee4c777038184d364b'  and user_id is null order by id desc limit 10;
  +----+-------------+--------+------+---------------+---------+---------+-------------+------+-------------+
  | id | select_type | table  | type | possible_keys | key     | key_len | ref         | rows | Extra       |
  +----+-------------+--------+------+---------------+---------+---------+-------------+------+-------------+
  |  1 | SIMPLE      | op_log | ref  | user_id       | user_id | 396     | const,const |    2 | Using where |
  +----+-------------+--------+------+---------------+---------+---------+-------------+------+-------------+
  </code></pre>