title: MySQL search for id in (identically structured) multiple tables
body: <p>I have products in 3 (identically structured) tables with 3 columns columns: id, date, requests.
  When searching for a specific product id I'd like to search in all tables (I don't know in advance in which table the product id can be found)</p>
  
  <p>table1 is very large (over 100 million rows), and currently I'm using this query to create a temporary table which I use to perform other queries on.</p>
  
  <pre><code>CREATE TEMPORARY TABLE temp ENGINE=MEMORY 
  as (select DISTINCT id, date, requests from table1 WHERE id='$id');
  
  SELECT ... FROM temp
  </code></pre>
  
  <p>Merging the 3 tables using UNION and then look for the id takes forever due to the size of table1 (over 100 million rows), so that is not the right approach.</p>
  
  <p>The fastest way I came up with is (I would do this using PHP):</p>
  
  <pre><code>search table1, if id found create temporary table, 
  if id not found in table 1 then
  search table2, if id found create temporary table, 
  if id not found in table 2 then
  search table3, if id found create temporary table, 
  if id not found in table3 then return id not found. 
  </code></pre>
  
  <p>However this way I end up performing multiple queries (if id is located in table 3 then 4 queries will have been executed). </p>
  
  <p>is there a better way of doing this with MySQL?</p>
  
  <p>ADDED: </p>
  
  <p>Would something like this work (what is the proper syntax for this, what I have below throws an error)</p>
  
  <pre><code>CREATE TEMPORARY TABLE temp ENGINE=MEMORY 
  as (
  (select DISTINCT id, date, requests from table1 WHERE id='$id')
  UNION
  (select DISTINCT id, date, requests from table2 WHERE id='$id')
  UNION
  (select DISTINCT id, date, requests from table3 WHERE id='$id')
  )
  </code></pre>