title: Keyword search using PHP MySql?
body: <p>I have title (varchar), description (text), keywords (varchar) fields in my mysql table. </p>
  
  <p>I kept keywords field as I thought I would be searching in this field only. But I now require to search among all three fields. so for keywords "word1 word2 word3", my query becomes</p>
  
  <pre><code>SELECT * FROM myTable
  WHERE (
  name LIKE '%word1%' OR description LIKE '%word1%' OR keywords LIKE '%word1%' 
  OR name LIKE '%word2%' OR description LIKE '%word2%' OR keywords LIKE '%word2%' 
  OR name LIKE '%word3%' OR description LIKE '%word3%' OR keywords LIKE '%word3%') 
  AND status = 'live'
  </code></pre>
  
  <p>Looks a bit messy but this works. But now I need to implement synonym search. so for a given word assuming there are a few synonyms available this query becomes more messy as I loop through all of the words. As the requirements are getting clearer, I will need to join this myTable to some other tables as well.</p>
  
  <p>So</p>
  
  <ul>
  <li><p>Do you think the above way is messy and will cause problems as the data grow?</p></li>
  <li><p>How can I avoid above mess? Is there any cleaner solution I can go by? Any example will help me.</p></li>
  <li>Is there any other method/technique you can recommend to me?</li>
  </ul>
  
  <p>With thanks</p>
  
  <h2>EDIT</h2>
  
  <p>@Peter Stuifzand suggested me that I could create one search_index table and store all 3 fields (title,keyword,desc) info on that and do full text search. I understand that additionally this table will include reference to myTable primary key as well. </p>
  
  <p>But my advanced search may include joining mytable with Category table, geographic_location table (for searching within 10, 20 miles etc), filtering by someother criteria and of course, sorting of search results. Do you think using mysql fulltext will not slow it down?</p>