title: MySQL search multiple tables for information
body: <p>I'm building a searchfunctionallity where a user can select the different libraries to search in with the provided keywords. Also the AND operator is supported. My problem is that when I try to search for information it gets somehow multiplied or it gets increased exponential??</p>
  
  <p>I'm using a MySQL database and PHP to process the results.</p>
  
  <p>A sample query looks like this, sample tablenames taken for readability:</p>
  
  <pre><code>SELECT table1.id AS table1_id,
         table2.id AS table2_id,
         table1.*,
         table2.*
  FROM   table1, table2
  WHERE  (table1.column1 LIKE '%key%' OR table1.column2 LIKE '%key%') OR
         (table2.column1 LIKE '%key%' OR table2.column2 LIKE '%key%')
  </code></pre>
  
  <p>Or when a user provides keywords like 'key AND yek' the queries will look like:</p>
  
  <pre><code>SELECT table1.id AS table1_id,
         table2.id AS table2_id,
         table1.*,
         table2.*
  FROM   table1, table2
  WHERE  ( (table1.column1 LIKE '%key%' AND table1.column2 LIKE '%key%') OR
          (table1.colum1 LIKE '%yek%' AND table1.colum2 LIKE '%yek%') ) 
  OR
         (( table2.column1 LIKE '%key%' AND table2.column2 LIKE '%key%') OR
          (table2.colum1 LIKE '%yek%' AND table2.colum2 LIKE '%yek%') )
  </code></pre>
  
  <p>I've searched arround and came accross UNION, but that won't do the trick. Firstly because of the tables I'm querying are not equal and it's a pretty expensive query. So also because the searchfunctionallity will be used often it's not an option.</p>
  
  <p>In fact I'm querying 5 tables and per table between 2 or 4 columns. I set up a testrow in one of the tables with a unique value so I should get 1 row as result but in fact I'm getting a lot more. When I select all 5 tables I'm getting over 10K resultrows. So my guess is the result gets multiplied some way or another. I've played with the operators AND and OR but so far without any luck.</p>
  
  <p>Is there someone who can help me further in my 'quest' to build a userfriendly searchengine in a webapp?</p>