title: Search MySQL text column with array of words and sort according to amount of matches
body: <p>How do I retrieve records from a MySQL table (Innodb) with results sorted according to how many sub-strings a record's two columns might contain. </p>
  
  <p>I'm searching for words in bodies of texts contained within two different columns. So the word might exist and it might have unknown text in front or behind it. </p>
  
  <p>Currently my PHP code creates a query that searches two columns (contains text) with a varying amount of different sub-strings. </p>
  
  <p>See simplified example:</p>
  
  <pre><code>$string = "";
  
  foreach $array_of_words as $word) {
      $string = $string . " column_one LIKE '% $word%' OR column_two LIKE '% $word%' OR ";
  }
  
  $string = substr ( $string, 0, - 3 );
  
  $query = "SELECT * FROM tbl_maintable WHERE (" . $string . ") LIMIT 5";
  </code></pre>
  
  <p>This works fairly well even though it is not exactly what I would like, however the biggest issue with the above code is that it's resource intensive (7 seconds to complete a query against a 100k database).</p>
  
  <p>I have taken a look at natural language searches (to a certain extent), but the documentation hasn't given me a clear definitive method I can use, and the MYSQL version (5.5.44) of the MySQL server in question does not have that option for InnoDB. I will however upgrade if that is the only option. </p>