2ord
@2ord
продвинутый чайник

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

Есть таблица постов с тегами, упрощенно:
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. Или есть предложения по структуре таблицы?
  • Вопрос задан
  • 75 просмотров
Пригласить эксперта
Ответы на вопрос 4
nokimaro
@nokimaro
Иногда лучший способ оптимизации, разбить сложный запрос на два простых, в том числе на каждый запрос можно прикрутить отдельно кеширование
1. SELECT tag FROM post_tags WHERE post_id = 123
2.
SELECT DISTINCT(post_id) FROM post_tags WHERE tag IN(..список tag из п1)
Ответ написан
Rsa97
@Rsa97
Для правильного вопроса надо знать половину ответа
1. EXPLAIN точно от этого запроса? Что-то я не вижу в запросе таблицы `tags_sort`.
2. Попробуйте в JOIN поменять местами поля:
ON t2.site_id = t1.site_id AND t2.tag = t1.tag
Ответ написан
@mayton2019
Ent. Software engineer. Oracle. SQL. BigData.
Что такое 123 и 555 ? Это переменные? Или константы?

Это важно понимать для дальнейшей оптимизации.
Ответ написан
jemunjho
@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 [т.е. делаем еще одну таблицу, в которой будем хранить - даты, когда и на какой сайт данный пост был опубликован]
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы