title: Search a many to many relationship with a wild card, performance issues
body: <p>I am building a database for an app and I am testing performance issues on a <em>larger</em> data set.  I generated about 250,000 location records.  Each location can be assigned to many categories and a category can be assigned to many locations.  My data-set has 2-4 categories assigned to each location.</p>
  
  <p>I want to allow the user to search for locations by filtering which categories should be allowed using a wild card search.  So maybe I want to match all categories with the word "red" in it.  So if I type red, now it shows all locations which have a category title that has "red" in it. In addition, I would like to wildcard search the location title with that same string.</p>
  
  <p>I wrote up a query which <em>works</em> but performance is awful in large data-sets.  Essentially I am using inner queries which is fine if my limit is set and I find results quick (around .05ms).  If I don't find any results right away, it looks like it goes through the whole database and the query takes around 9-10 seconds.</p>
  
  <p>Here is a simplified layout of my database:</p>
  
  <pre><code>locations: id | title | address
  categories: id | title
  locations_categories: id | location_id | category_id
  </code></pre>
  
  <p>Here is the query I currently am using:</p>
  
  <pre><code>SELECT `id`,`title`,`address`
  FROM (`locations`)
  WHERE title LIKE '%string%'
  AND WHERE id IN (
   SELECT location_id 
   FROM locations_categories 
   JOIN categories ON categories.id = locations_categories.category_id 
   WHERE categories.title LIKE '%string%')
  </code></pre>