title: Mysql - "Select like" not using index
body: <p>I have been playing around with indexes on MySQL (5.5.24, WinXP), but I can't find the reason of why the server is not using one index when a <code>LIKE</code> is used.</p>
  
  <p>The example is this:</p>
  
  <p>I have created a test table:</p>
  
  <pre><code>create table testTable (
    id varchar(50) primary key,
    text1 varchar(50) not null,
    startDate varchar(50) not null
  ) ENGINE = innodb;
  </code></pre>
  
  <p>Then, I added an index to <code>startDate</code>. (Please, do not ask why the column is a text and not a date time.. this is just a simple test):</p>
  
  <pre><code>create index jeje on testTable(startdate);
  analyze table testTable;
  </code></pre>
  
  <p>After that, I added almost 200,000 rows of that where <b>startDate</b> had 3 possible values. (One third of appearences for each one..near 70,000 times)</p>
  
  <p>So, if I run an <b>EXPLAIN</b> command like this:</p>
  
  <pre><code>explain select * from testTable use index (jeje) where startDate = 'aaaaaaaaa';
  </code></pre>
  
  <p>The answer is the following:</p>
  
  <pre><code>id = 1
  select_type = SIMPLE
  type = ref
  possible_keys = jeje
  key = jeje
  rows = 88412
  extra = Using where
  </code></pre>
  
  <p>So, the key is used, and the rows amount is near to 200,000/3 so all is ok.</p>
  
  <p>The poblem is that if I change the query to: (just chaning '=' to 'LIKE'):</p>
  
  <pre><code>explain select * from testTable use index(jeje) where startDate LIKE 'aaaaaaaaa';
  </code></pre>
  
  <p>In this case, the answer is:</p>
  
  <pre><code>id = 1
  select_type = SIMPLE
  type = ALL
  possible_keys = jeje
  key = null
  rows = 176824
  extra = Using where
  </code></pre>
  
  <p>So, the index is not being used now(key is null, and rows near to the full table..as the type=all suggests).</p>
  
  <p>MySQL documentation says that LIKE DOES make use of indexes.</p>
  
  <p>So, what am i not seeing here? Where is the problem?</p>
  
  <p>Thanks for your help.</p>