Как объединить поиск по тегам и названию поста в MySQL?

Question

[Avreliya777]

Несколько миллионов постов.
У постов есть также и теги.
Как организовать поиск?
Просто если запрос будет из одного слова, которое есть в тегах, и найдено например много-много постов, напр. тысяч 300, а потом делать второй поиск тут же на поиск этого слова в названии постов, тоже куча вылезет вариантов, и потом в PHP вычленять один набор из другого? Или другие какие-то варианты есть? Как обычно делают в таких случаях?

Добавлено: а теги 2 таблицы, tags - id, name,
и tags_posts - id_tag, id_post,
т.е. я сначала нахожу совпадение тега в тегс, а потом если есть совпадение, то делаю уже второй запрос на выковыривание постов через tags_posts.
Но сейчас хочу добавить поиск по названию поста ещё, и как бы это объединить эффективно не знаю

Comments

[mayton2019]

А покажи схему таблицы? Как имена постов и теги уже лежат?

[Avreliya777]

mayton2019, на теги 2 таблицы, tags - id, name,
и tags_posts - id_tag, id_post, т.е. я сначала нахожу совпадение тега в тегс, а потом если есть совпадение, то делаю уже второй запрос на выковыривание постов через tags_posts.
Но сейчас хочу добавить поиск по названию поста ещё, и как бы это объединить эффективно не знаю

[mayton2019]

Avreliya777, лучше-бы в одну таблицу сложил. Для textsearch оно лучше.

[Akina]

Список тегов фиксированный или динамический? Сколько их вообще, возможных тегов?

[Ипатьев]

mayton2019, ну вот зачем советовать глупости? А потом как редактировать привязанные теги? переименовывать? Объединять? Ну это же первый класс, начальная школа баз данных. У человека сделано все нормально, а вы его обратно в пещеру тянете. Только потому что не знаете, как простейший запрос составить

[mayton2019]

Ипатьев, так всегда. Хочешь хороший текстовый поиск - денормализуй. А вы что предлагаете?

[Ипатьев]

mayton2019, а где здесь текстовый по тегам-то?

[mayton2019]

Ипатьев, теги == токены. Почему нет?

[Ипатьев]

mayton2019, я с трудом понимаю ваш язык. Поэтому опишу то, как я вас понял, и попробую объяснить, как это работает на самом деле. Если вы что-то другое имели в виду, то поправьте.

Что такое "теги == токены" я не понял совсем.
"текстовый поиск" - это, как я понимаю, полнотекстовый поиск.
Он здесь не нужен, поскольку у нас нет текста. А есть простая таблица, где поиск происходит просто по совпадению. По колонке с именами тегов строится обычный индекс, и нужные строки находятся мгновенно. Затем через джойн, по первичному ключу, точно так же мгновенно находятся нужные посты, привязанные к найденным тегам.
Это самые основы реляционной архитектуры.

Сначала сливать нормализованные данные в одну колонку, а потом строить по ним полнотекстовый индекс - это по осмысленности примерно как отрезать себе уши и пришить их к нижней части спины, соединив с головой звукопроводящими трубками от стетоскопа. Работать будет, но криво, а главное - непонятно зачем.

[mayton2019]

Ипатьев,

Что такое "теги == токены" я не понял совсем.
"текстовый поиск" - это, как я понимаю, полнотекстовый поиск

Да. Тестовый поиск (обычно) ищет по текстовым индексам из токенов. В грубом приближении
это очищенное представление документа. Как-то сведение всех слов к основе. Стемминг. Лемматизация.
Убирание всяких предлогов и шумящих символов. В данной задаче например тело поста после
такой обработки мало чем будет отличаться от тегов этого-же поста. Вот поэтому я и предложил
соединить пост и теги в один datarow.

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

Я-бы хотел уйти от джойна в данной задаче. Я по сути материализовал ваш join и теперь мне его
делать не нужно. Ведь алгоритмы текстового поиска найдут все мои теги так-же точно как и соединение.
Моя идея будет более понятна если представить что и пост и теги у нас лежат в Postgresql в поле
типа JSON.

Вопрос нормализации я предлагаю оставить пока за кадром. Ведь мы обсуждаем соц-сети
где исходные данные всегда денормализованы и поэтому нам не стоит бепокоиться о том
что не было гарантировано изначально.

Вообще здесь нормализаций это некий фетишь который мы конечно можем реализовать для
себя лишь на 5 минут и затем поняв что она не нужа - снова денормализуем для текстового поиска.

[Ипатьев]

mayton2019, судя по всему, вы не понимаете, что такое теги.

Вот смотрите, на этом сайте используются теги: mysql, php - и так далее.
Для их поиска нужно только имя. Им не нужен никакой "Стемминг", "Лемматизация" и убирание предлогов. И всё остальное, что вы тут нафантазировали.

Ваша идея давно понятна. И я уже писал выше, что она собой представляет.

Такие гении, предлагающие "оставить нормализацию за кадром" и "слить все в JSON", тут стадами бегают. А спроси его потом - а как тег из системы удалить, или переименовать, или слить два тега в один - только глазками хлопают.

[mayton2019]

Ипатьев, да не переживай так сильно. Просто у тебя один уровнь видения. Это реляционки.
А у меня - и реляционки и биг-дата.

[Stalker_RED]

mayton2019, а несколько милионов постов это уже бигдата?

Answer 1

[Ипатьев]

В запросе ничего сложного, всё так, как вы и описали
Только, разумеется, в одном запросе.

Для этого пора бы уже освоить джойны. Нормализацию БД вы освоили - это прекрасно.
Джойны - тоже не бином ньютона, это основа SQL. Второй запрос не нужен, всё делается в одном
Сначала выбираем фильмы по тегам

select f.* from sakila.category c
join sakila.film_category fc on fc.category_id = c.category_id
join sakila.film f on f.film_id = fc.film_id
where c.name in ('Action', 'Drama') 
group by f.film_id;

Если хотим добавить поиск по заголовку, то просто добавляем к этому же запросу еще условие во WHERE, and description like '%Drama%'
https://sqlize.online/sql/mysql57/8d7c91ad5f6cf415...

Правда на таких объемах LIKE делать не рекомендуется. Но если сейчас работает по всей базе, то по десятой части и подавно будет. Но в целом надо уже прикручивать внешний поисковый движок, ElasticSearch. Ну или хотя бы добавить полнотекстовый индекс по названию (и тексту тогда уж заодно) и искать в них через match...against

Comments

[Avreliya777]

Спасибо большое! Ну вот по полнотекстовому (не в тегах, в тегах обычный LIKE будет, а вот названиях по неточному совпадению хотелось бы) я как раз пока к manticore присматриваюсь, опенсорсная ветка от сфинкс как я поняла, а сфинксом я как-то много лет назад уже пользовалась.
Вот собственно говоря наверное там я и столкнусь с проблемой, что теги я одним способом ищу, а названия по полнотексту, и при этом чтобы они не пересекались. Наверное всё же два запроса придётся делать, соединять результаты (в mysql или php) и удалять дубли (в mysql или php, посмотреть что быстрее).

[Ипатьев]

Можно и мантикору, но я порекормендую все-таки эластик. Это примерно как Жигули и Фольксваген. Ездить будет, но хуже, а главное - при переезде в Европу твои навыки замены карбюратора не пригодятся.

В любом случае, с внешним поисковым движком искать уже будет он сам, а этот запрос вообще не понадобится.