Multiple JOINs to the same subquery expression

I’ve recently been teaching myself SQL, and have been working on a toy project to do so. Here is a sample schema:

CREATE TABLE user (
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name VARCHAR(50)
);

INSERT INTO user(name) VALUES
  ("User 1"),
  ("User 2"),
  ("User 3"),
  ("User 4"),
  ("User 5");

CREATE TABLE friendship (
  uid_1 INT,
  uid_2 INT,
  accepted_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (uid_1, uid_2),
  CONSTRAINT fk_uid_1 FOREIGN KEY (uid_1) REFERENCES user (id),
  CONSTRAINT fk_uid_2 FOREIGN KEY (uid_2) REFERENCES user (id)
);

INSERT INTO friendship(uid_1, uid_2) VALUES
  (1, 2),
  (2, 1);

CREATE TABLE event (
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  name VARCHAR(50),
  owner_id INT,
  CONSTRAINT fk_owner_id FOREIGN KEY (owner_id) REFERENCES user (id)
);

INSERT INTO event (name, owner_id) VALUES
 ("Event 1", 1),
 ("Event 2", 2),
 ("Event 3", 3),
 ("Event 4", 4),
 ("Event 5", 5),
 ("Event 6", 1);

CREATE TABLE invite (
  event_id INT NOT NULL,
  sent_from_id INT NOT NULL,
  sent_to_id INT NOT NULL,
  PRIMARY KEY (event_id, sent_to_id),
  CONSTRAINT fk_event_id FOREIGN KEY (event_id) REFERENCES event (id),
  CONSTRAINT fk_sent_from_id FOREIGN KEY (sent_from_id) REFERENCES user (id),
  CONSTRAINT fk_sent_to_id FOREIGN KEY (sent_to_id) REFERENCES user (id)
);

INSERT INTO invite(event_id, sent_from_id, sent_to_id) VALUES
  (1, 2, 3);

As part of this project, I have a query that gets a list of users, with information populated relative to the currently authenticated user.

A simplified version of the query looks like this:

$select_users_query = "

SELECT
    user.id AS id,
    user.name AS name,
    friendship.accepted_time AS friend_since
FROM user
LEFT JOIN friendship
    ON friendship.uid_1 = user.id AND friendship.uid_2 = $relative_to_id

";

Then, at some endpoints, I want to return objects which have one or more users as sub-objects. In order to do this, I’ve been JOINing tables to the above query as a subquery, but when the returned object has multiple users (e.g., an invite to an event has a sending user, a receiving user, and a user that owns the event in question), the resulting query can end up pretty repetitive:

$select_invites_query = "

SELECT
    event.id AS event_id,
    event.name AS event_name,

    owner.id AS owner_id,
    owner.name AS owner_name,
    owner.friend_since AS owner_friend_since,

    sent_to.id AS sent_to_id,
    sent_to.name AS sent_to_name,
    sent_to.friend_since AS sent_to_friend_since,

    sent_from.id AS sent_from_id,
    sent_from.name AS sent_from_name,
    sent_from.friend_since AS sent_from_friend_since,

FROM invite

INNER JOIN event
    ON event.id = invite.event_id

INNER JOIN ($select_users_query) owner
    ON event.owner_id = owner.id

INNER JOIN ($select_users_query) sent_from
    ON invite.sent_from_id = sent_from.id

INNER JOIN ($select_users_query) sent_to
    ON invite.sent_to_id = sent_to.id

";

My questions are:

1. Is repeating a subquery like this a performance issue during execution, assuming that the INNER JOINs all match on just a single row?

2. If not, is the additional parsing required for $select_invites_query a significant concern at all (especially as $select_users_query grows big and complex)?

3. Would using a variable here be a good idea, or a bad idea? From my inspection of EXPLAIN it seems as though MySQL is able to handle these JOINs pretty efficiently, but would defining a variable force MySQL to pull the unfiltered result set into memory before JOINing?

See SQL Fiddle schema here.

Answer

Since you appear to need self joins of same query, consider a Common Table Expression (CTE) (available in MySQL 8.0+) and with PHP parameterization. Below demonstrates with PHP’s mysqli API in object-oriented and procedural styles:

$select_invites_query = "
 WITH sub AS (
    SELECT u.id AS id, u.`name` AS `name`, 
           f.accepted_time AS friend_since 
    FROM user 
    LEFT JOIN friendship f
          ON f.uid_1 = u.id AND f.uid_2 = ?
 )

 SELECT event.id AS event_id, event.`name` AS event_name, 
        owner.id AS owner_id, owner.`name` AS owner_name, 
        owner.friend_since AS owner_friend_since,

        sent_to.id AS sent_to_id, sent_to.`name` AS sent_to_name, 
        sent_to.friend_since AS sent_to_friend_since, 

        sent_from.id AS sent_from_id, sent_from.`name` AS sent_from_name, 
        sent_from.friend_since AS sent_from_friend_since

 FROM invite 
 INNER JOIN event 
    ON event.id = invite.event_id 
 INNER JOIN sub owner 
    ON event.owner_id = owner.id 
 INNER JOIN sub sent_from 
    ON invite.sent_from_id = sent_from.id 
 INNER JOIN sub sent_to 
    ON invite.sent_to_id = sent_to.id";

// OBJECT-ORIENTED STYLE
$conn = new mysqli("my_host", "my_user", "my_pwd", "my_db");
$stmt = $conn->prepare($select_invites_query))
$stmt->bind_param("i", $relative_to_id);
$stmt->execute();
...

// PROCEDURAL STYLE     
$conn = mysqli_connect("my_host", "my_user", "my_pwd", "my_db");
$stmt = mysqli_prepare($conn, $select_invites_query);
mysqli_stmt_bind_param($stmt, "i", $relative_to_id); 
mysqli_stmt_execute($stmt); 
...