SELECT from different tables with WHERE or JOIN?

Question

I want to select information from 6 table I have 2 choice first one with where statement:

SELECT columns 
FROM   db1, 
       db2, 
       db3, 
       db4, 
       db5, 
       db6 
WHERE  db1.id = $user_id 
        OR db2.user_id = $user_id 
        OR db3.user_id = $user_id 
        OR db4.user_id = $user_id 
        OR db5.user_id = $user_id 
        OR db6.user_id = $user_id 

$user_id is php 1variable` that contain user_id;

Second Choice joins tables and one where statement: like here which one is fast, useful and clear?I want to get fast result because I will do that kind of queries with very complicated queries also

Answer 1

You want to do this

SELECT columns 
FROM   db1
WHERE id = $user_id
UNION
SELECT columns 
FROM   db2
WHERE id = $user_id
UNION
SELECT columns 
FROM   db3
WHERE id = $user_id
UNION
SELECT columns 
FROM   db4
WHERE id = $user_id
UNION
SELECT columns 
FROM   db5
WHERE id = $user_id
UNION
SELECT columns 
FROM   db6
WHERE id = $user_id

Put an index on the id column on all 6 tables. This will have a performance of O(N) or faster.

You could also user UNION ALL instead of UNION if you want to get the same results from multiple tables (eg leave duplicates). UNION ALL is much faster so use that if you can.

The query you have will cross join all the tables and have a performance of O(N^6) as I'm sure you found out.