lampyridae lampyridae - 6 months ago 10
SQL Question

Is multiple LEFT JOINS on same table efficient?

I have to deal with the following schema (simplified):

CREATE TABLE user
(`id` int,
`name` varchar(255),
PRIMARY KEY (id));

CREATE TABLE user_extra
(`user_id` int,
`user_extra_name` varchar(64),
`value` varchar(255),
UNIQUE INDEX user_id_extra_name (user_id, user_extra_name));

INSERT INTO user
(`id`, `name`)
VALUES
(1, 'John Doe'),
(2, 'Don Joe');

INSERT INTO user_extra
(`user_id`, `user_extra_name`, `value`)
VALUES
(1, 'phone', '1234444'),
(1, 'email', 'doe@example.com'),
(2, 'email', 'joe@example.com'),
(1, 'nickname', 'johnny'),
(2, 'nickname', 'donny');


I want to get the following data in the most efficient way possible:

id name phone email nickname
1 John Doe 1234444 doe@example.com johnny
2 Don Joe (null) joe@example.com donny


There will be many users and potentially more extra entries, besides phone, email and nickname.

What I do so far is multiple left joins on user_extra:

SELECT
u.id, u.name,
uephone.value AS phone,
ueemail.value AS email,
uenick.value AS nickname
FROM
user u
LEFT JOIN
user_extra uephone ON
uephone.user_id = u.id AND
uephone.user_extra_name = 'phone'
LEFT JOIN
user_extra ueemail ON
ueemail.user_id = u.id AND
ueemail.user_extra_name = 'email'
LEFT JOIN
user_extra uenick ON
uenick.user_id = u.id AND
uenick.user_extra_name = 'nickname'


Is there a better way to approach this problem? Is this liable to cause performance issues as the number of users grow or if there would be, say, a dozen such left joins?

SQLFiddle

(I use MySQL.)

EDIT: Adding indexes/keys for clarity

Answer

Since you published simplified tables schema (with no index) I can not really say if your query has any performance issue. Probably if user.id is primary key and user_extra.user_id is indexed as well your query is pretty good.

And if you will add complex index to user_extra against (user_id,user_extra_name) that could be even better.

But for small tables and just to simplify reading this query here is another approach:

http://sqlfiddle.com/#!9/0ab56b/20

SELECT
  u.id, u.name,
  ue.phone,
  ue.email,
  ue.nickname
FROM
  user u
LEFT JOIN (
  SELECT 
    user_id,
    MAX(IF(user_extra_name = 'phone',`value`,null)) AS phone,
    MAX(IF(user_extra_name = 'email',`value`,null)) AS email,
    MAX(IF(user_extra_name = 'nickname',`value`,null)) AS nickname
  FROM user_extra
  GROUP BY user_id
  ) ue
ON
    ue.user_id = u.id 

But really, if you have indexes on your table I believe your query is good.

Comments