[Solved] Is multiple LEFT JOINS on same table efficient?

EverSQL Database Performance Knowledge Base

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', '[email protected]'),
    (2, 'email', '[email protected]'),
    (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 [email protected]     johnny
2   Don Joe     (null)  [email protected]     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

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `user_extra` ADD INDEX `user_extra_idx_user_name_user_id` (`user_extra_name`,`user_id`);
The optimized query:
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'

Related Articles



* original question posted on StackOverflow here.