CODEWITHSUNDEEP
sqlperformancesubqueryunion
CanCeylan
CanCeylan

Reputation: 3010

SQL - Join one subquery for multiple times within union

I have 4 different tables which store different transactions for users and I would like to union all of the transactions for some specific users. But the problem is, all these 4 tables have huge amount of data, therefore when I try to UNION all of them and JOIN with the users, it takes hours to compute. What I'm doing is:

SELECT blablabla
FROM transactions1 t1
JOIN users ON (users.id = t1.user_id AND user.state = 1 AND user.friends > 1 AND user.loc = 'Turkey')
UNION
SELECT blablabla
FROM transactions2 t2
JOIN users ON (users.id = t2.user_id AND user.state = 1 AND user.friends > 1 AND user.loc = 'Turkey')
UNION
SELECT blablabla
FROM transactions3 t3
JOIN users ON (users.id = t3.user_id AND user.state = 1 AND user.friends > 1 AND user.loc = 'Turkey')
UNION
SELECT blablabla
FROM transactions4 t4
JOIN users ON (users.id = t4.user_id AND user.state = 1 AND user.friends > 1 AND user.loc = 'Turkey')
ORDER BY date DESC

The problem is, I'm running JOIN users ON (users.id = t1.user_id AND user.state = 1 AND user.friends > 1 AND user.loc = 'Turkey') this filter 4 times by joining each transaction table with users.

In order to have best practice and increase the efficiency of my query, how should I improve my query ?

Thanks !

Upvotes: 1

Views: 1489

Answers (3)

Jannic Beck
Jannic Beck

Reputation: 2425

You might try something like this:

SELECT blablabla
FROM transactions1 t1, transactions2 t2,transactions3 t3,transactions4 t4
JOIN users ON (users.id = t1.user_id OR users.id = t2.user_id OR users.id = t3.user_id 
OR users.id = t4.user_id AND user.state = 1 AND user.friends > 1 AND user.loc = 'Turkey');

Upvotes: 1

valex
valex

Reputation: 24144

Check if all fields with conditions are INDEXED. (t.user_id, users: state,friends,loc) Also try to use this:

select blablabla from
(
  select * from user where user.state = 1 AND user.friends > 1 AND user.loc = 'Turkey'
) U1
join transactions1 t1 on (U1.Id=t1.User_id)

UNION ALL

select blablabla from
(
  select * from user where user.state = 1 AND user.friends > 1 AND user.loc = 'Turkey'
) U1
join transactions1 t2 on (U1.Id=t2.User_id)

UNION ALL

select blablabla from
(
  select * from user where user.state = 1 AND user.friends > 1 AND user.loc = 'Turkey'
) U1
join transactions1 t3 on (U1.Id=t3.User_id)

UNION ALL

select blablabla from
(
  select * from user where user.state = 1 AND user.friends > 1 AND user.loc = 'Turkey'
) U1
join transactions1 t4 on (U1.Id=t4.User_id)

Upvotes: 0

Allan S. Hansen
Allan S. Hansen

Reputation: 4081

Either select the "user.state = 1 AND user.friends > 1 AND user.loc = 'Turkey'" from your users using a Common Table Expression or into a temporary table.

Then use that result in your Union.

Upvotes: 2

Related Questions