Как эффективно вывести статус друга?

Question

[Алексей Коновалов]

Всем привет! У меня есть таблица пользователей и таблица друзей. При запросе пользователя мне нужно получить статус является ли текущий пользователь другом выбранных из таблицы. Для этого я использовал подзапрос, потому что LEFT JOIN при таком условии не использует индексы и пробегает все поля. Это видно на картинке ниже:
Гораздо лучше работает подзапрос:


Но проблема появляется, когда мне нужно вывести не только статус дружбы (f.`status`), но и поле f.`accept`, чтобы уже на строне php определить, что если f.`status` == 0 и f.`accept` == 0, значит пользователь f.`user_two` еще не отреагировал на заявку в друзья.
Ну или если f.`status` == 0 и f.`accept` == 1, значит пользователь f.`user_two` отреагировал на заявку, но не принял её.

Проблема в том, что с помощью подзапроса нельзя вывести два поля в таблицу. Приходится делать либо два подзапроса, либо JOIN. Но в случае с двумя подзапросами ситуация становится хуже если вывести нужно не 2 поля, а 4. Не делать же 4 подзапроса?

У меня уже была мысль изменить поле `status` и сделать его по принципу:

• NULL - (в случае если нет записи в таблице друзей) - небыло заявок в друзья между пользователями.
  
• 0 - была заявка, но не было реакции второго пользователя
  
• 1 - заявка была принята - пользователи друзья
  
• 2 - заявка была отклонена
  

Прошу помощи... как сделать правильно, красиво и производительно?

Структура таблиц:

Users

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `group_id` smallint(2) NOT NULL DEFAULT '4',
  `email` varchar(50) NOT NULL,
  `login` varchar(64) NOT NULL,
  `name` varchar(32) NOT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  `bdate` date DEFAULT NULL,
  `country_id` int(11) DEFAULT NULL,
  `state_id` int(11) DEFAULT NULL,
  `city_id` int(11) DEFAULT NULL,
  `pwd` varchar(64) DEFAULT NULL,
  `salt` varchar(100) DEFAULT NULL,
  `reg_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `last_visited` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `foto` varchar(32) DEFAULT NULL,
  `provider` enum('native','vk','ok','go','fb','tw') DEFAULT 'native',
  `social_id` bigint(32) DEFAULT NULL,
  `link_fb` varchar(40) DEFAULT NULL,
  `link_in` varchar(40) DEFAULT NULL,
  `link_tw` varchar(40) DEFAULT NULL,
  `link_yo` varchar(40) DEFAULT NULL,
  `rating` float DEFAULT NULL,
  `albums` int(11) DEFAULT NULL,
  `timezone` varchar(255) DEFAULT NULL,
  `is_banned` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD KEY `login` (`login`) USING BTREE,
  ADD KEY `username` (`name`(5));

Friends:

--
-- Структура таблицы `friends`
--
CREATE TABLE `friends` (
  `id` int(11) NOT NULL,
  `user_one` int(11) NOT NULL,
  `user_two` int(11) NOT NULL,
  `status` int(1) NOT NULL,
  `accept` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Индексы таблицы `friends`
--
ALTER TABLE `friends`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_one` (`user_one`),
  ADD KEY `user_two` (`user_two`);

Comments

[Mikhail Kupriyanov]

Доброго дня.
Опубликуйте структуру ваших таблиц, пожалуйста: полный DDL с атрибутами и индексами

[Алексей Коновалов]

Mikhail Kupriyanov, Добавил в вопрос

[Mikhail Kupriyanov]

Алексей Коновалов, я правильно понимаю, что вам нужно показать статус отправленных заявок в друзья в профиле конкретного пользователя?

[Алексей Коновалов]

Mikhail Kupriyanov, Да, но не в профиле конкретного пользователя, а в списке пользователей, например при поиске пользователей или просто при выводе списка пользователей.
Их может быть на странице, скажем, 20

[Mikhail Kupriyanov]

Алексей Коновалов, ответ ниже не подходит?

Answer 1

[Rsa97]

Подзапрос в списке выборки SELECT - это самый наихудший вариант. Такой подзапрос выполняется для каждой строки, попавшей в выборку.

SELECT *
  FROM (
    SELECT `f`.`user_one` AS `id`, `f`.`status` AS `status`, `f`.`accept` AS `accept`
      FROM `friends` 
      WHERE `f`.`user_two` = :userId
    UNION SELECT `f`.`user_two` AS `friend`, `f`.`status` AS `status`, `f`.`accept` AS `accept`
      FROM `friends`
      WHERE `f`.`user_one` = :userId
  ) AS `f`
  JOIN `users` AS `u`
  ON `u`.`id` = `f`.`id`

Comments

[Алексей Коновалов]

Спасибо за отклик, но ваш запрос выводит не совсем то, что нужно...
Он выбирает таблицу friends и к ней присоединяет таблицу users. Таким способом можно вывести друзей, но для вывода друзей можно написать проще запрос.
Мне нужно получить именно пользователей, а статус дружбы нужен для того, чтобы знать какую кнопку отображать рядом с каждым пользователем

[Rsa97]

Алексей Коновалов,

SELECT *
  FROM (
    SELECT `f`.`user_one` AS `id`, `f`.`status` AS `status`, `f`.`accept` AS `accept`
      FROM `friends` 
      WHERE `f`.`user_two` = :userId
    UNION SELECT `f`.`user_two` AS `friend`, `f`.`status` AS `status`, `f`.`accept` AS `accept`
      FROM `friends`
      WHERE `f`.`user_one` = :userId
  ) AS `f`
  RIGHT JOIN `users` AS `u`
    ON `u`.`id` = `f`.`id`
  WHERE `u`.`id` IN (здесь список тех, кого надо отобразить)

[Алексей Коновалов]

Rsa97, Все-таки подзапрос это самый быстрый вариант:
56.028204917908 - Ваш запрос
29.229672193527 - Мой запрос с LEFT JOIN
0.054003000259399 - Запрос с подзапросом

Для всех тестов использовал SQL_NO_CACHE, один и тот же :user_id.
Количество пользователей в Users == 5млн (случайное заполнение)
Количество в таблице friends 1381 запись

Answer 2

[Mikhail Kupriyanov]

Исходя из собственного понимания задачи, позволю немного вмешаться в структуру
1. Таблица пользователей

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

2. Таблица друзей
Добавлены внешние ключи на таблицу пользователей
Поскольку статусов ограниченное количество, то статусы вынес в отдельные атрибуты

CREATE TABLE `friends` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_one` int(11) NOT NULL,
  `user_two` int(11) NOT NULL,
  `is_accepted` tinyint(1) NOT NULL DEFAULT '0',
  `is_rejected` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `fk_user_one_idx` (`user_one`),
  KEY `fk_user_two_idx` (`user_two`),
  CONSTRAINT `fk_user_one` FOREIGN KEY (`user_one`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_user_two` FOREIGN KEY (`user_two`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

3. Запрос со статусом заявок для первого пользователя будет не очень сложным

SELECT
    u1.name,
    IF (
        u2.id IS NOT NULL,
        IF (
            f1.is_accepted,
            CONCAT('Дружит c ', u2.name),
            CONCAT (
                'Не дружит c ',
                u2.name,
                ': ',
                IF(
                    f1.is_rejected,
                    'заявка отклонена',
                    'заявка ожидает рассмотрения'
                )
            )
        ),
        'Ни с кем не дружит'
	) status
FROM
	user u1
LEFT JOIN
	friends f1
    ON f1.user_one = u1.id
LEFT JOIN
	user u2
    ON u2.id = f1.user_two
WHERE
	u1.id = 1

Если я неправильно понял вашу задачу, готов обсудить и запрос и структуру таблиц.
Не исключено, что если я лучше пойму задачу, то можно будет создать лучшую структуру для хранения заявок в друзья, по которой получится сделать простой запрос