MYSQL Оптимизировать вложенный запрос с Group BY

164

24 марта 2019, 10:10

Ситуация следующая, есть БД MYSQL 5.5 в ней таблица с ~1млн строк. Для простоты приведу БД аналогичной структуры. Допустим у нас есть 3 таблицы, "Пользователь", "Спорт", "Пользователь - спорт".

CREATE TABLE users
(`id` int, `name` varchar(10), PRIMARY KEY (`id`));
INSERT INTO users (`id`, `name`)
VALUES (1, 'sasha'), (2, 'vitaliy'),(3, 'fedor');

CREATE TABLE sports
(`id` int, `name` varchar(10), PRIMARY KEY (`id`));
INSERT INTO sports (`id`, `name`)
VALUES (1, 'tennis'), (2, 'football'), (3, 'basketball');
CREATE TABLE  user_sports
(`id_user` int, `id_sport` int, `pref` int, 
 CONSTRAINT fk1 FOREIGN KEY(`id_user`) REFERENCES users(`id`),
 CONSTRAINT fk2 FOREIGN KEY(`id_sport`) REFERENCES sports(`id`)); 
INSERT INTO  user_sports (`id_user`, `id_sport`, `pref`)
VALUES
(1, 1, 0),
(1, 2, 1),
(2, 3, 0),
(3, 1, 2),
(3, 3, 1),
(3, 2, 0);

Нужно составить быстрый запрос, который для заданного вида спорта выведет всех пользователей, при этом если пользователь занимается еще и другим спортом, то это тоже нужно вывести. Вот пример медленного запроса:

select id, name, group_concat(user_sports.id_sport)
from users 
inner join user_sports
    ON users.id = user_sports.id_user
    AND users.id in (SELECT user_sports.id_user
                     FROM user_sports
                     WHERE user_sports.id_sport = 1) 
group by users.id

вот результат выполнения запроса

Однако когда речь идет о большом кол-ве записей этот запрос выполняется очень медленно. Вот explain запроса

Подскажите как можно оптимизировать данный запрос, исходя из поставленных условий? как избавится от using temporary? Кроме как использовать временную таблицу, в которую записать результат подзапроса и повешать индекс в голову ничего не приходить.

Answer 1

select users.id, users.name, group_concat(user_sports.id_sport)
from users 
inner join user_sports
    ON users.id = user_sports.id_user
group by user_sports.id_user
having sum(user_sports.id_sport = 1) > 0

К сожалению этот запрос тоже очень медленный...

Ну попробуйте

select u.id, u.name, group_concat(us.id_sport)
from users u
inner join user_sports us
    ON u.id = us.id_user
INNER JOIN ( SELECT DISTINCT us1.id_user
             FROM user_sports us1
             WHERE us1.id_sport = 1 ) u1
    ON u.id = u1.id_user
group by u.id