title: Efficient MySQL text search
body: <p>I have a forum written in PHP using MySQL, and  I'd like to make forum search available. It will allow users to search for particular strings, as well as filter on metadata like post date and subject and so on. The metadata can be efficiently searched because most of these fields are indexed, but I think that the primary use-case is of course going to be normal text search, and without making use of metadata filters which could trim the results.</p>
  
  <p>After some testing I have found that, contrary to most people's setups, SQL_CALC_FOUND_ROWS is significantly faster (approx 1.5x) than doing the query twice in order to get the number of results, so the best query I have  is:</p>
  
  <pre><code>SQL_CALC_FOUND_ROWS * from blahblah where content like '%term%' limit whatever whatever;
  </code></pre>
  
  <p>Unsurprisingly, this is really slow because it has to text-match every single forum post in the database. Is there anything I can do to improve on this? Would putting an index on the content (TEXT) field even help when using the <code>LIKE</code> operator? How does one normally do this?</p>