1

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)

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.

CREATE TEMPORARY TABLE temp ENGINE=MEMORY 
as (select DISTINCT id, date, requests from table1 WHERE id='$id');

SELECT ... FROM temp

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.

The fastest way I came up with is (I would do this using PHP):

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.

However this way I end up performing multiple queries (if id is located in table 3 then 4 queries will have been executed).

is there a better way of doing this with MySQL?

ADDED:

Would something like this work (what is the proper syntax for this, what I have below throws an error)

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')
)
dean2020
  • 645
  • 2
  • 8
  • 25
  • You might be able to just create a temporary table that contains all the IDs using a union but you would still need to get it into a singular table in order to just have a single query. Just using table 2 and 3 would reduce it by one query and by reversing the order (searching table 1 last) you could reduce the time. – Seth Jan 26 '17 at 08:39
  • Thanks for the insight, I added one more option I thought of (UNION with WHERE statements), but not sure what the proper syntax is. – dean2020 Jan 26 '17 at 08:59
  • What error are you getting with your query? [The documentation](https://dev.mysql.com/doc/refman/5.7/en/union.html) seems to indicate it should work (at least without creating a temporary table). – Seth Jan 26 '17 at 09:03
  • I figured it out, the error was caused by the additional parenthesis on the outside. Unfortunately this approach seems slower than simply checking the tables one by one for the id. – dean2020 Jan 26 '17 at 09:15
  • Did you include the time to setup the temporary table in that case? In addition you could look into [optimizing](https://dev.mysql.com/doc/refman/5.5/en/optimization-indexes.html) the indexes for those tables. You could also try to do `SELECT 1 FROM table1 where id = 'id' UNION SELECT 2 FROM table2 where id = 'id' UNION ...` in order to have a single query that gets you the table which contains the `id`. You still really haven't clarified whenever it's about speed or number of queries. – Seth Jan 26 '17 at 09:39
  • Sorry not being clear, speed is what I'm after. The tables are optimised and performing well on the queries I'm currently using. I didn't know about "SELECT 1" , I'll try that as well. Thank you for your suggestions. – dean2020 Jan 26 '17 at 09:54

1 Answers1

0

try this
SELECT * FROM information_schema.columns WHERE column_name = 'column_name';

or

SELECT table_name, column_name FROM information_schema.columns WHERE column_name = 'column_name';

Reynald Henryleo
  • 397
  • 2
  • 7
  • 22