title: Optimize MySQL FULL TEXT search
body: <p>I have a search with only one field that allows me to search in several columns of a MySQL table.</p>
  
  <p>This is the SQL query:</p>
  
  <pre><code>SELECT td__user.*
  FROM td__user LEFT JOIN td__user_oauth ON td__user.id = td__user_oauth.user_id 
  WHERE ( td__user.id LIKE "contact@mywebsite.com" OR (MATCH (email, firstname, lastname) AGAINST (+"contact@mywebsite.com" IN BOOLEAN MODE)) ) 
  ORDER BY date_accountcreated DESC LIMIT 20 OFFSET 0
  </code></pre>
  
  <p>The exact SQL query with PHP pre-processing of the search field to separate each word:</p>
  
  <pre><code>if($_POST['search'] == '') {
      $searchId = '%';
  } else {
      $searchId = $_POST['search'];
  }
  
  $searchMatch = '';
  foreach($arrayWords as $word) {
      $searchMatch .= '+"'.$word.'" ';
  }
  
  $sqlSearch = $dataBase-&gt;prepare('SELECT td__user.*,
                                          td__user_oauth.facebook_id, td__user_oauth.google_id
                                   FROM td__user
                                   LEFT JOIN td__user_oauth ON td__user.id = td__user_oauth.user_id
                                   WHERE (
                                           td__user.id LIKE :id OR 
                                           (MATCH (email, firstname, lastname) AGAINST (:match IN BOOLEAN MODE)) ) 
  
                                   ORDER BY date_accountcreated DESC LIMIT 20 OFFSET 0);
  
  $sqlSearch-&gt;execute(['id'    =&gt; $searchId,
                       'match' =&gt; $searchMatch]);
  
  $searchResult = $sqlSearch-&gt;fetchAll();
  $sqlSearch-&gt;closeCursor();
  </code></pre>
  
  <p>And these are my index:<a href="https://i.stack.imgur.com/PbJXr.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/PbJXr.png" alt="enter image description here"></a></p>
  
  <p>The SQL query works well, when I put an ID in the search field or a first name or a last name, or an email even not complete I have results. I can also put a first name and a last name in my search field and I will only result in people with this name.</p>
  
  <p>On the other hand, in a table containing 500,000 contacts, the query takes more than 5 seconds. Are there any possible points for improvement in the query or in the indexes to be done in order to have a faster query?</p>