У меня есть две таблицы: Posts и Tags , в которых хранятся статьи для публикации пользователей и теги, которые они прикрепляют к статьям. Таблица PostTags используется для обозначения отношения идентификаторов статей и идентификаторов тегов. Структуры представлены ниже:
Сообщения:
id | title | author_id | create_time | update_time | ... #(title, author_id, create_time) is unique
Метки:
id | tag_text | create_time #tag_text is unique and index
PostTags:
id | post_id | tag_id #(post_id, tag_id) is unique
Теперь я использую следующий sql для получения статей с соответствующими тегами (с помощью group_concat).
SELECT p.id, p.title, t.tag AS Tags FROM Posts p LEFT JOIN Tags t on t.id IN (SELECT tag_id FROM PostTags WHERE post_id=s.id) GROUP BY p.id ORDER BY p.update_time DESC LIMIT 0, 10
Но я нахожу это очень медленным (для 2.5k строк статей и 600 тегов, требуется> 3s). Как я могу улучшить производительность?
Результат EXPLAIN выглядит следующим образом:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra 1 | PRIMARY | p | ALL | NULL | NULL | NULL | NULL | 2569 | Using temporary; Using filesort 1 | PRIMARY | t | ALL | NULL | NULL | NULL | NULL | 616 2 | DEPENDENT SUBQUERY | PostTags | index_subquery | unique_index,tag_id,post_id | tag_id | 4 | func | 1 | Using where
PS, мой оригинальный sql (с group_concat)
SELECT p.id, p.title, group_concat(DINSTINCT t.tag) AS Tags FROM Posts p LEFT JOIN Tags t on t.id IN (SELECT tag_id FROM PostTags WHERE post_id=s.id) GROUP BY p.id ORDER BY p.update_time DESC LIMIT 0, 10
Но случай без group_concat такой же.
Документация MySQL описывает именно такую ситуацию :
Типичный случай низкой производительности подзапроса IN – это когда подзапрос возвращает небольшое количество строк, но внешний запрос возвращает большое количество строк для сравнения с результатом подзапроса.
Проблема в том, что для оператора, который использует подзапрос IN, оптимизатор переписывает его как коррелированный подзапрос. [..] Если внутренний и внешний запросы возвращают строки M и N, соответственно, время выполнения становится порядка O (M × N), а не O (M + N), как это было бы для некоррелированного подзапроса .
Использование другого соединения вместо подзапроса было бы более оптимальным решением:
SELECT p.id, p.title, t.tag AS Tags FROM Posts p LEFT JOIN PostTags pt on pt.post_id = p.id LEFT JOIN Tags t on t.id = pt.tag_id GROUP BY p.id ORDER BY p.update_time DESC LIMIT 0, 10
Вот запрос с group_concat и group_concat имеет ничего общего со скоростью.
select p.id, p.title, group_concat(t.tag_text) as post_tags from Post p left join PostTags pt on pt.post_id = p.id left join Tags t on pt.tag_id = t.id group by p.id order by p.udate_time desc limit 0,10
У вас уже есть некоторые индексы в таблице, которые пока хороши, однако добавление другого индекса увеличит запрос
alter table Posts add index updated_time_idx(updated_time);