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

Question

[d'Ivan]

Есть таблица постов с тегами, упрощенно:

CREATE TABLE `post_tags` (
  `site_id` int NOT NULL,
  `post_id` int NOT NULL,
  `tag` VARCHAR(255) NOT NULL,
  `published_at` datetime NOT NULL,
  PRIMARY KEY (`post_id`,`tag`), /* составной первичный индекс */
  KEY `by_site` (`site_id`,`published_at` DESC),
  KEY `by_site_tag` (`site_id`,`tag`,`published_at` DESC)
);

Составил такой запрос

SELECT
	DISTINCT(t2.post_id) as post_id
FROM (
	SELECT post_id, tag, site_id FROM post_tags
	WHERE post_id = 123  /* находим все посты с тегами у исходного поста */
) t1
INNER JOIN post_tags t2
	ON t1.site_id = t2.site_id AND t1.tag = t2.tag  /* пересечение с тегами у исходного поста и сайтом */
WHERE t2.site_id = 555 AND t2.post_id != 123  /* исключаем исходный пост и фильтруем по сайту */
ORDER BY t2.published_at DESC;

EXPLAIN вырисовывает нерадужную картину:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	tags_sort	NULL	ref	PRIMARY,by_site_tag,by_site	PRIMARY	2002	const	3	30.10	Using where; Using temporary; Using filesort
1	SIMPLE	t2	NULL	ref	PRIMARY,by_site_tag,by_site	by_site_tag	884	const,post_tags.tag	1	93.55	Using where; Using index

1. Какие рекомендации по индексам?
   
2. Как можно упростить запрос, возможно исключив подзапрос?
   
3. Или есть предложения по структуре таблицы?
   

Answer 1

[nokimaro]

Иногда лучший способ оптимизации, разбить сложный запрос на два простых, в том числе на каждый запрос можно прикрутить отдельно кеширование
1. SELECT tag FROM post_tags WHERE post_id = 123
2.

SELECT DISTINCT(post_id) FROM post_tags WHERE tag IN(..список tag из п1)

Comments

[d'Ivan]

tag IN(..список tag из п1)

После разбиения все еще получается
Using index condition; Using where; Using filesort
что неприемлемо для меня.

[nokimaro]

Роман Мирр, подозреваю что filesort может быть потому что не используется нормально составной индекс, например order by published_at может вызывать filesort

[d'Ivan]

nokimaro, не могу выкинуть сортировку order by published_at ...

[nokimaro]

Роман Мирр,

KEY `by_site_tag` (`site_id`,`tag`,`published_at` DESC)

по идее этот индекс должен покрывать

WHERE site_id = ... AND tag IN(...) ORDER BY published_at DESC

в остальном, по опыту просто filesort не так страшен, страшнее temporary + filesort

[nokimaro]

Роман Мирр, ну выкинуть сортировку конечно всегда можно, и сортировать в приложении.
но повторюсь, filesort не всегда означает что там file
есть хорошая старая статья по теме https://www.percona.com/blog/2009/03/05/what-does-...

я если говорим про оптимизацию, то это всегда компромис. померьте скорость, выбирите оптимальный вариант.

Answer 2

[Rsa97]

1. EXPLAIN точно от этого запроса? Что-то я не вижу в запросе таблицы `tags_sort`.
2. Попробуйте в JOIN поменять местами поля:
ON t2.site_id = t1.site_id AND t2.tag = t1.tag

Comments

[d'Ivan]

1. не заменил название таблицы в выводе EXPLAIN, но суть не меняется
2. не имеет эффекта похоже

Answer 3

[mayton2019]

Что такое 123 и 555 ? Это переменные? Или константы?

Это важно понимать для дальнейшей оптимизации.

Comments

[d'Ivan]

нет, просто для примера вставил

[mayton2019]

Твой select distinct. Это функция. Чтобы ее оптимизировать надо понять какие у нее аргументы.

[d'Ivan]

mayton2019, переменные, понятное дело.

Answer 4

[jemunjho]

Несколько вопросов:
1) Теги могут дублироваться? Или они уникальные?

2) Один и тот же тег - может быть привязан к разным постам. Так ведь?
Можно попробовать сделать следующее: разбить все на три таблицы:
- posts: id / site_id / published_at
- tags: id / name [и можно поставить еще ключ уникальности на поле name]
- post_tag_binding: post_id / tag_id [и поставить уникальный составной ключ на post_id + tag_id]

3) Тогда запрос, приблизительно, будет выглядеть вот как-то так:

select
	p.id
from posts as p, post_tag_binding as p_t_b
where p.site_id = 124 and 
      p.id != 123 and 
      p.id = p_t_b.post_id and 
      p_t_b.tag_id in (
      	select 
      		p_t_b2.tag_id
      	from post_tag_binding as p_t_b2
      	where p_t_b2.post_id = 123
      )

4) Один и тот же пост - может быть привязан к разным сайтам? Если да, то нужно сделать еще одну таблицу: post_site_binding, в которой будут содержаться поля - post_id / site_id. Это немного усложнит выборку, но не значительно. И так же - можно будет сделать уникальный ключ по двум этим полям.

Я бы предложил следующую структуру:
- posts: id / title / description / created_at и т.д.
- tags: id / name [и можно поставить еще ключ уникальности на поле name]
- sites: id / name [и можно поставить еще ключ уникальности на поле name]
- post_tag_binding: post_id / tag_id [и поставить уникальный составной ключ на post_id + tag_id; дополнительно - сюда можно будет добавить еще site_id, если предполагается, что на разных сайтах может быть разный набор тегов для одного и того же поста]
- post_feed: post_id / site_id / published_at [т.е. делаем еще одну таблицу, в которой будем хранить - даты, когда и на какой сайт данный пост был опубликован]

Comments

[d'Ivan]

1. Теги могут дублироваться только не в одном посте. Поэтому PK (site_id, post_id)
2. Да