MYSQL like query does not show full table scan in explain
<p>I have a query running in Mysql like so (obfuscated names)</p>

<pre><code>explain 
select this_.id as id1_0_,
this_.column1 as column1,
this_.column2 as column2,
this_.column3 as column3,
this_.column4 as column4,
this_.column5 as column5,
from
tablename this_
where
this_.column1 like '/blah%'
and this_.column2 = 'a9b51a14-4338-94f7-f23dbf9d539e'
and this_.column3 &lt;&gt; 'DUH'
and this_.column4=0
and this_.column5 like '%somename%'
order by this_.created desc 
limit 20
</code></pre>

<p><strong>Edit</strong>: column1 has a BTREE index, column2, column3, column 4, column5, created all have HASH indexes.</p>

<p>Table has one foreign key which is selected in the select clause but not the WHERE clause.</p>

<p>I'm told and read that the </p>

<blockquote>
  <p>like %somename%</p>
</blockquote>

<p>will result in a full table scan. However, when i ran the explain, the output of the explain is </p>

<pre><code>id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  this_   ref  somecolumnandinexnames 111 const   30414   Using where
</code></pre>

<p>The explain output looks exactly the same if I take away the like clause.  </p>

<p>Based on this, we decided to put the query in production only to discover that in practice the query with <em>like</em> took way longer to execute (some seconds compared to some millis without the like).</p>

<p>Is there an explanation as to why the explain didn't warn me beforehand about this?</p>

<p><strong>Edit</strong>: Observations</p>

<ul>
<li>Taking away the order by makes the query go fast again even with the LIKE still in there.</li>
<li>Splitting into a subquery with the like in the outer query as mentioned below in the post actually works!</li>
</ul>

<p>As @Uueerdo says, moving the rest of the conditions into a subquery actually speeds up performance!  I'm therefore tempted to conclude that one of the things that could be happening is that the WHERE clause with the like is executed before the other conditions leading to a large resultset. As @Uueerdo says, moving the rest of the conditions into a subquery actually speeds up performance!  I'm therefore tempted to conclude that one of the things that could be happening is that the WHERE clause with the like is executed before the other conditions leading to a large resultset. However, I still don't have an explanation on why removing the order by speeds up performance. The query selects all of 10 rows so the order by should be quite fast.</p>

<p>Is there a way I can see the order in which MYSQL evaluates the query. I think I remember seeing some sort of a graphical representation in MS SQL Server explain plans once. Don't remember if it was quite the same.</p>