title: Mysql optimization for REGEXP
body: <p>This query (with different name instead of "jack") happens many times in my slow query log. Why?</p>
  
  <p>The Users table has many fields (more than these three I've selected) and about 40.000 rows.</p>
  
  <p><code>select name,username,id from Users where ( name REGEXP
  '[[:&lt;:]]jack[[:&gt;:]]' ) or ( username REGEXP '[[:&lt;:]]jack[[:&gt;:]]' )
  order by name limit 0,5;</code></p>
  
  <p><code>id</code> is primary and autoincrement.<br>
  <code>name</code> has an index.<br>
  <code>username</code> has a unique index.</p>
  
  <p>Sometimes it takes 3 seconds!
  If I explain the select on MySQL I've got this:</p>
  
  
  
  <pre class="lang-none prettyprint-override"><code>select type: SIMPLE
  table: Users
  type: index
  possible keys: NULL
  key: name
  key len: 452
  ref: NULL
  rows: 5
  extra: Using where
  </code></pre>
  
  <p>Is this the best I can do? What can I fix?</p>