CODEWITHSUNDEEP
mysqlsqljoinquery-optimization
JHS
JHS

Reputation: 7871

LEFT OUTER JOIN vs SUBSELECT in MySQL

I have a table say table1 which has 3 columns column1, column2 and column3.

The column1 and column2 are a FOREIGN KEY with 2 other tables. However the data in column3 is from n number of tables.

For e.g. Let us consider Facebook. To display the activities it might maintain a table which could have user1 photoliked photo1 or user1 statusliked status1. So in this case column3 cannot be a FOREIGN KEY with a specific table.

Now there are 2 ways of getting real data -

1st way -

SELECT user_id,
       verb_id,
       CASE WHEN verb_id = photoliked THEN
            (SELECT photo_name FROM photos WHERE photo_id = column3) -- getting the desired data from the third column
         WHEN verb_id = statusliked THEN
            (SELECT status FROM statustable WHERE status_id = column3) 
         ELSE '' END AS performedon
FROM table1
     JOIN table2 ON user_id = user_id  -- joining the first column
     JOIN table3 ON verb_id = verb_id  -- joining the second column

2nd way -

SELECT user_id,
       verb_id,
       CASE WHEN verb_id = photoliked THEN
            p.photo_name
         WHEN verb_id = statusliked THEN
            s.status
         ELSE '' END AS performedon
FROM table1
     JOIN table2 ON user_id = user_id  -- joining the first column
     JOIN table3 ON verb_id = verb_id  -- joining the second column
     LEFT JOIN photos p ON p.photo_id = column3  -- joining the column3 with specific table 
     LEFT JOIN statustable s ON s.status_id = column3

Question

Which of the 2 ways is better to retrieve data? and which of the 2 queries is less expensive?

Upvotes: 6

Views: 2551

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115630

You could use this approach:

SELECT t.user_id,
       t.verb_id,
       p.photo_name  AS performedon
FROM table1 AS t
     JOIN table2 AS t2  ON t2.user_id = t.user_id  
     JOIN table3 AS t3  ON t3.verb_id = t.verb_id 
     JOIN photos AS p  ON  p.photo_id = t.column3  
                       AND t.verb_id = 'photoliked'

UNION ALL

SELECT t.user_id,
       t.verb_id,
       s.status  
FROM table1 AS t
     JOIN table2 AS t2  ON t2.user_id = t.user_id
     JOIN table3 AS t3  ON t3.verb_id = t.verb_id
     JOIN statustable AS s  ON  s.status_id = t.column3
                            AND t.verb_id = 'statusliked' ;

Upvotes: 1

Brent Baisley
Brent Baisley

Reputation: 12721

The second would be faster and the reason is the first one contains what is called correlated subqueries. The subqueries have a correlation with records from the master query. So the subqueries need to be run once for every matching record in the master query. In your case it can't run the subquery until it determines the value of verb_id in the master query. That is a lot of queries to run.

An EXPLAIN on the first query should indicate this issue. It's is usually a red flag when you see that in an EXPLAIN.

Upvotes: 1

vearutop
vearutop

Reputation: 4072

I think JOIN will be faster as it is performed once for a query, and also I would try to filter verb_id in JOIN

SELECT user_id,
   verb_id,
   COALESCE(p.photo_name, s.status) AS performedon
FROM table1
    JOIN table2 ON user_id = user_id  -- joining the first column
    JOIN table3 ON verb_id = verb_id  -- joining the second column
    LEFT JOIN photos p ON verb_id = 'photoliked' AND p.photo_id = column3  -- joining the column3 with specific table 
    LEFT JOIN statustable s ON verb_id = 'statusliked' AND s.status_id = column3

Upvotes: 1

Related Questions