title: mysql like query is very slow in pulling results
body: <p>I have a very large mysql database consisting of 35 tables. Each table is different in structure, but all have some common fields. <code>'entity_name'</code> is one such common field in all the tables. It is a <code>VARCHAR</code> field. Some tables contains millions of records, while some contains billions.</p>
  
  <p>I am searching for a search key in all these tables and pull the entity name, plus some of the common fields from the tables containing matching records; Push the results to an array and finally display in HTML. The queries are pretty straightforward:</p>
  
  <pre><code>SELECT * FROM table_name WHERE entity_name LIKE '%search_key%' //Partial match.
  
  SELECT * FROM table_name WHERE entity_name LIKE 'search_key%' //Begins with.
  
  SELECT * FROM table_name WHERE entity_name IN('search_key') //Equal to.
  </code></pre>
  
  <p>Most often the first query is used.</p>
  
  <p>The size of the database is around 9GB. The search process is very slow. Per research I have found that using indexes might not working since I am searching with this: <code>entity_name LIKE '%search_key%</code> The wildcard char appears in the front. Partitions also do not work(please correct if I am wrong) since we are focusing on a varchar field.</p>
  
  <p>Any ideas/ suggestions are welcome.</p>