How to optimize searching for a value across 2 MySQL tables?

Question

I need to determine if a user supplied value exists in 2 different MySQL tables, and which table it exists in. I know I could do:

SELECT 1 FROM table1 WHERE col_name_table1 = user_value;

And if the user value does not exist there, then I could just check the next table:

SELECT 1 FROM table2 WHERE col_name_table2 = user_value;

In my PHP logic, which executes the SQL queries above, I can easily capture which table the user value exists in depending on the values returned from the queries (i.e. 0, 1).

I'm wondering though, is there a more elegant way to do what I need?
Is it possible to write a single query that checks both tables, and then returns the table where the user value is found?

don't store them in two separate tables to begin with. Two tables holding the same content doesn't make sense — e4c5, Mar 02 '17 at 06:32
Possible duplicate of [PHP mysql search multiple tables using a keyword](http://stackoverflow.com/questions/6574564/php-mysql-search-multiple-tables-using-a-keyword) — Shadow, Mar 02 '17 at 06:40

score 0 · Answer 1 · answered Mar 02 '17 at 06:37

0

Maybe a UNION ALL would work?

(SELECT 1 FROM table1 WHERE col_name_table1 = user_value) UNION ALL (SELECT 2 FROM table2 WHERE col_name_table2 = user_value);

answered Mar 02 '17 at 06:37

Franklin

881
1
8
28

score 0 · Answer 2 · answered Mar 02 '17 at 06:47

Running two queries in a single one is possible using UNION ALL or CASE WHEN or SUB QUERIES, but it is not an optimized way. Do this by php only as you know if and else case. If you get a result from the first table it will hardly takes .01 sec, and if not in first then on else condition will .01 sec more. But once you create some UNION or SUB it will increase upto 2.0 to 3.0 sec

How to optimize searching for a value across 2 MySQL tables?

2 Answers2