title: mysql union search (multiple tables) maintaining a relevance
body: <p>I have more than one table, with different columns, full text indexes are set accordingly. There is no problem if I would want to seach only one table as score would sort data by relevance. However I have multiple tables and I use <code>UNION</code> for these sql <code>SELECT</code> statements as following:</p>
  
  <pre><code>$this-&gt;dbi-&gt;prepare("
      SELECT `id`,'".PRE."pages' as `table`, MATCH(`title`,`content`) AGAINST (?) AS `score` FROM `".PRE."pages` WHERE MATCH(`title`,`content`) AGAINST (?)
      UNION SELECT `id`,'".PRE."news' as `table`, MATCH(`title`,`content`) AGAINST (?) AS `score` FROM `".PRE."news` WHERE MATCH(`title`,`content`) AGAINST (?)
      UNION SELECT `id`,'".PRE."comments' as `table`, MATCH(`title`, `content`) AGAINST (?) AS `score` FROM `".PRE."comments` WHERE MATCH(`title`, `content`) AGAINST(?)
      UNION SELECT `id`,'".PRE."auction_auto' as `table`, MATCH(`manufacturer`,`model`,`location`,`other`,`contact`) AGAINST (?) AS `score` FROM `".PRE."auction_auto` WHERE MATCH(`manufacturer`,`model`,`location`,`other`,`contact`) AGAINST (?)
  ;")-&gt;...
  </code></pre>
  
  <p>How can I have relevancy for these many tables? Now no matter of scores data will be displayed according to the order of table I do select.</p>
  
  <p>Thanks.</p>
  
  <p><a href="https://stackoverflow.com/questions/13902427/is-this-a-right-solution-for-search-relevance-across-many-tables-w-fulltext">For a possible solution by an autor himself see this link</a></p>