title: MySQL search across multiple indices
body: <p>I've inherited a large, ugly MySQL 5.7 database and am looking to optimize some queries.</p>
  
  <p>Specifically, the user wants to search 7 different <code>var_char</code> columns...in 7 different tables.</p>
  
  <p>Obviously, I can join the 7 tables and filter for <code>LIKE '%search term%'</code>, but I'd love to use <code>MATCH...AGAINST</code> a <code>FULLTEXT</code> index.</p>
  
  <p>I created the indices:</p>
  
  <pre class="lang-sql prettyprint-override"><code>ALTER TABLE table1 ADD FULLTEXT INDEX (origname);
  ALTER TABLE table2 ADD FULLTEXT INDEX (byline);
  ALTER TABLE table3 ADD FULLTEXT INDEX (copyright);
  ALTER TABLE table4 ADD FULLTEXT INDEX (source);
  ALTER TABLE table5 ADD FULLTEXT INDEX (image_title);
  ALTER TABLE table6 ADD FULLTEXT INDEX (photo_info);
  ALTER TABLE table7 ADD FULLTEXT INDEX (alt_text);
  </code></pre>
  
  <p>I tried the below but performance was terrible...is there a good way to do a full-text search across multiple tables?</p>
  
  
  
  <pre><code>SELECT
      MATCH(table1.name) AGAINST ('search term') as name,
      MATCH(table2.byline) AGAINST ('search term') as byline,
      MATCH(table3.copyright) AGAINST ('search term') as copyright,
      MATCH(table4.source) AGAINST ('search term') as copyright,
      MATCH(table5.image_title) AGAINST ('search term') as image_title,
      MATCH(table6.photo_info) AGAINST ('search term') as photo_info,
      MATCH(table7.alt_text) AGAINST ('search term') as alt_text
  FROM table1
  LEFT JOIN table2 on table2.entity_id = table1.fid
  LEFT JOIN table3 on table3.entity_id = table1.fid
  LEFT JOIN table4 on table4.entity_id = table1.fid
  LEFT JOIN table5 on table5.entity_id = table1.fid
  LEFT JOIN table6 on table6.entity_id = table1.fid
  LEFT JOIN table7 ON table7.alt_text = table1.fid
  WHERE
      MATCH(table1.name) AGAINST ('search term') OR
      MATCH(table2.byline) AGAINST ('search term') OR
      MATCH(table3.copyright) AGAINST ('search term') OR
      MATCH(table4.source) AGAINST ('search term') OR
      MATCH(table5.image_title) AGAINST ('search term') OR
      MATCH(table6.photo_info) AGAINST ('search term') OR
      MATCH(table7.alt_text) AGAINST ('search term');
  </code></pre>
  
  <p>Ideally I would just use Elasticsearch / Lucene / Solr etc but let's assume for a moment I can't use any of those things, that I'm restricted to MySQL. Is there a good way to do this?</p>
  
  <p>Here is the <code>EXPLAIN EXTENDED</code> output:</p>
  
  <pre><code>id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra   
  1   SIMPLE  table1  NULL    ALL NULL    NULL    NULL    NULL    557535  100 NULL    
  1   SIMPLE  table2  NULL    ref entity_id   entity_id   4   mydb.table1.fid 1   100 NULL    
  1   SIMPLE  table3  NULL    ref entity_id   entity_id   4   mydb.table1.fid 1   100 NULL    
  1   SIMPLE  table4  NULL    ref entity_id   entity_id   4   mydb.table1.fid 1   100 NULL    
  1   SIMPLE  table5  NULL    ref entity_id   entity_id   4   mydb.table1.fid 1   100 NULL    
  1   SIMPLE  table6  NULL    ref entity_id   entity_id   4   mydb.table1.fid 1   100 NULL    
  1   SIMPLE  table7  NULL    ALL field_file_image_alt_text_value NULL    NULL    NULL    203374  100 Using where 
  </code></pre>
  
  <p>===========================================</p>
  
  <p><strong>Answer</strong>: Bomar's solution below is the right approach but MySQL 5.7 didn't like his syntax. I had to code it this way:</p>
  
  <p>SELECT fid, scoredName AS score, origname AS "File Name", NULL AS Byline, NULL AS Copyright, NULL AS Source, NULL AS "Image title", NULL AS "Photo info", NULL AS "Alt text"</p>
  
  <pre><code>    SELECT fid, MATCH(origname) AGAINST ('search term') AS scoredName, origname, NULL AS N4, NULL AS N5, NULL AS N6, NULL AS N7, NULL AS N8, NULL AS N9
      FROM table1
      WHERE MATCH(origname) AGAINST ('search term')
  
      UNION
  
      SELECT entity_id, MATCH(byline) AGAINST ('search term') AS scoredByline, NULL AS N3, byline, NULL AS N5, NULL AS N6, NULL AS N7, NULL AS N8, NULL AS N9
      FROM table2   
      WHERE MATCH (byline) AGAINST ('search term')
  
      UNION
  
      SELECT entity_id, MATCH(copyright) AGAINST ('search term') AS scoredCopyright, NULL AS N3, NULL AS N4, copyright, NULL AS N6, NULL AS N7, NULL AS N8, NULL AS N9
      FROM table3
      WHERE MATCH (copyright) AGAINST ('search term')
  
      UNION
  
      SELECT entity_id, MATCH(source) AGAINST ('search term') AS scoredSource, NULL AS N3, NULL AS N4, NULL AS N5, source, NULL AS N7, NULL AS N8, NULL AS N9
      FROM table4
      WHERE MATCH (source) AGAINST ('search term')
  
      UNION
  
      SELECT entity_id, MATCH(image_title) AGAINST ('search term') AS scoredTitle, NULL AS N3, NULL AS N4, NULL AS N5, NULL AS N6, image_title, NULL AS N8, NULL AS N9
      FROM table5
      WHERE MATCH(image_title) AGAINST ('search term')
  
      UNION
  
      SELECT entity_id, MATCH(full_photo_info) AGAINST ('search term') AS scoredPhotoInfo, NULL AS N3, NULL AS N4, NULL AS N5, NULL AS N6, NULL AS N7, full_photo_info, NULL AS N9
      FROM table6
      WHERE MATCH(full_photo_info) AGAINST ('search term')
  
      UNION
  
      SELECT entity_id, MATCH(file_image_alt_text) AGAINST ('search term') as scoredaltText, NULL AS N3, NULL AS N4, NULL AS N5, NULL AS N6, NULL AS N7, NULL AS N8, file_image_alt_text
      FROM table7
      WHERE MATCH(file_image_alt_text) AGAINST ('search term')
  ) AS g
  GROUP BY fid, score, origname, byline, copyright, source, "Image title", "Photo info", "Alt text"
  ORDER BY score DESC;
  </code></pre>
  
  <p>To explain: <code>UNION</code> is just concatenating the subsequent queries into the columns set up by the first query.</p>
  
  <p>Hence they all have to have the same number of columns, and that's why you see all those <code>NULL</code> values.</p>
  
  <p>But you can't have two columns with the same name, so you have to alias each <code>NULL</code>.</p>
  
  <p>Next up I'll probably weight the different indices by multiplying the more important ones by a constant.</p>