title: MySQL search engine query not returning all results
body: <p>I have a database that has the tables 'releases' and 'releases_index'. Each row in the releases table has an ID, and the index table contains keywords related to every row of the releases table. There are multiple keywords for each row of the releases table.</p>
  <p>I am trying to create a query that searches the index table for one or more keywords and counts how many matches there are for each keyword, returning the results in order of matches starting with the best match.</p>
  <p>This is what I have so far:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT index_releaseId, COUNT(1) as count FROM releases_index
  WHERE index_value LIKE '%apple%' AND index_value LIKE '%banana%'
  GROUP BY index_releaseId
  ORDER BY count DESC
  </code></pre>
  <p>The problem with this is that it would only return results where index_value has both 'apple' and 'banana' in the same row, whereas I need it to return results if the same release ID has separate keywords for 'apple' and 'banana'.</p>
  <p>If index_releaseId 1 has two keywords (apple and banana) then I expect to get this result:</p>
  <pre><code>index_releaseId   count
  1                 2
  </code></pre>
  <p>I hope I have explained this clearly. Essentially it's just a search engine.</p>
  <p>Can anyone advise the best way to do this?</p>
  <p><strong>UPDATE:</strong>
  Here is a hopefully clearer example of what I want</p>
  <p>Say this is the releases_index table:</p>
  <pre><code># index_id, index_releaseId, index_value
  '435101', '21956', 'Scarlet Witch'
  '435104', '21956', 'Television'
  '435102', '21956', 'WandaVision'
  '435109', '21998', 'WandaVision'
  </code></pre>
  <p>If in my search engine I search for 'scarlet witch wandavision', release ID 21956 would be a result. Although 21998 matched on one phrase, it wouldn't be a result as it doesn't have rows that match 'scarlet' or 'witch'. In terms of the query above, the result should look like this:</p>
  <pre><code># index_releaseId, count
  '21956', '2'
  </code></pre>
  <p>If i searched for just 'wandavision' both 21956 and 21998 would be results:</p>
  <pre><code># index_releaseId, count
  '21956', '1'
  '21998', '1'
  </code></pre>
  <p>Hopefully this clarifies what I'm looking for, however, I am finding it difficult to explain.</p>