Алгоритм поиска по тегам?

Question

[rundll32]

Если в таблице базы данных есть строкове поле tags, которое содержит теги перечисленные через запятую, как оптимальнее реализовать поисковую выдачу? Допустим первым результатом будет запись с совпадением 3\3, потом 2\3 и 1\3.

Answer 1

[Сергей Тихонов]

SELECT record.id, count(*) 
FROM record 
   JOIN record_tags ON (record_tags.record_id = record.id)
WHERE record_tags.tag_id IN (1,2,3) GROUP BY 1 ORDER BY 2 DESC

В структуре данных "многие-ко-многим" получите все записи, связанные с искомыми тегами, а сортировка выдаст количество совпадений (одной записи с разными тегами)

Comments

[xmoonlight]

Запустите на количестве 1000+ записях и идите пить кофе.

[Сергей Тихонов]

xmoonlight, а альтернативы?

[xmoonlight]

Сергей Тихонов, бинарной маской, например.

[Сергей Тихонов]

А нужную сортировку как получить?

Answer 2

[ThunderCat]

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

Comments

[xmoonlight]

Это абстрактый пример в вопросе на нечёткий поиск, а не реальные теги в базе через запятую... )

[twobomb]

Ага и потом мы делаем поиск по тегу и идем курить

[ThunderCat]

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

[ThunderCat]

xmoonlight, Ага, есть поле тэги через запятую, но это не тэги, и вопрос вообще абстрактный, и я не я и физиономия не моя )

[xmoonlight]

ThunderCat, ну откуда дроби-то?!)

[twobomb]

ThunderCat, Конечно шустро, до того момента пока не начнет медленно

[ThunderCat]

xmoonlight, дроби - количество совпадающих тегов видимо, 2 тега у новости из 3 перечисленных в поисковой строке, или выбор похожих новостей - отстортировать по "подходящести" по тегам, 2 из 3 совпали...

[ThunderCat]

twobomb, о, ес, конечно тепло, пока не стало холодно... О чем это вообще?

[xmoonlight]

ThunderCat,

подходящести

удачный термин для нечёткой выборки))

[ThunderCat]

xmoonlight, ну, релевантности, если использовать "новомодные" термины )

[xmoonlight]

ThunderCat, до релевантности, там как до другого края вселенной)))
"Похожие наборы тегов" - ещё как-то)

[Daria Motorina]

twobomb, а какие есть способы ушустрить еще больше? Я считаю что с индексами и нормализацией будет вменяемо работать, как описал ThunderCat

[ThunderCat]

Daria Motorina, на самом деле всегда есть как "ушустрить" что то еще на чуть-чуть, вопрос только стоит ли оно того или нет. Как вариант - часть часто используемых данных вывести в быстрые хранилища а ля редис, например в случае если это похожие новости - можно хранить уже готовые наборы в редисе конкретно для этой новости, или наборы индексов часто используемых тегов хранить в редисе и получать из него уже готовые индексы, минуя поиск по текстовому полю, сразу из кей-валуе хранилища... Но в структурно-архитектурном плане пока ничего лучше хранения связки м-к-м не придумали.

[twobomb]

Daria Motorina, Где это используется и какой там вообще объем данных?

[Daria Motorina]

twobomb, я не знаю, это же вопрос автора, просто Вы заняли позицию, что ответ Thundercat неполный, хотелось услышать Вашу версию)

[twobomb]

Daria Motorina, Thundercat не дал ответ на вопрос автора. Он просто предложил свой вариант организации, причем вариант, который приходит наверно самый первый на ум любому, кто столкнется с такой задачей. Так как реализовано во всех цмсках , том же вордпресе Такой вариант рабочий, до поры до времени. Опять же смотря где это используется и какой вообще объем данных.

[ThunderCat]

twobomb,

Он просто предложил свой вариант организации

Не свой вариант, а так как это делается по классике, действительно хотелось бы услышать "не мой" - свежий прогрессивный новый вариант хранения. Прошу, маэстро!

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

Ну вот чтобы не быть голословным - на БОЛЬШИЕ объемы данных. То есть реально вот - миллионы записей на десятки миллионов тегов. Внимательно слушаю.

[twobomb]

ThunderCat, Ну если у вас реально миллионы записей на десятки миллионов тегов и идет быстрый рост данных и большое количество запросов то стоит рассмотреть вариант шардирования вынести на mongoDb или redis, также можно подключить кеш на этом же или на memcached если прям вообще нужна пушка.

[ThunderCat]

twobomb, так, стоп. Мы вроде говорили про то как организовать хранение. Шардирование и кеш это немного из другой области и никак не относятся к организации структуры хранения.

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

Вот отсюда и поподробнее - альтернативные варианты организации плс.

[twobomb]

ThunderCat,

Мы вроде говорили про то как организовать хранение. Шардирование и кеш это немного из другой области и никак не относятся к организации структуры хранения.

Мы не говорили не о какой структуре, это ответ на

Ну вот чтобы не быть голословным - на БОЛЬШИЕ объемы данных. То есть реально вот - миллионы записей на десятки миллионов тегов. Внимательно слушаю.

А вопрос был вообще в другом, ну по крайней мере как я его понял. Есть поле tags, с забитыми тегами через запятую, нужно оптимально организовать выдачу. Всё! Зачем предлагать вариант огранизации если уже реализована кривая организация, уже забита база и написан функционал, не будет же он все переписывать, перепарсивать и переделывать.
А если мы говорим про скорость. То такая организация как предложена выше, самая примитивная и банальная, я думаю автор сам до нее может догадаться. Если у вас интернет магазин в котором тысяча товаров с тегами и не большая частота запросов то этого хватит, а дальше уже как минимум нужен хотя-бы какой нибудь кеш, хотя-бы самописный, одной организацией баз данных тут не справишься. Всё зависит от нагрузки.

[ThunderCat]

twobomb,

А вопрос был вообще в другом, ну по крайней мере как я его понял.

Видимо вы его не правильно поняли. Черным по белому:

как оптимальнее реализовать поисковую выдачу?

Ответ - никак, так как критерий оптимальности в данном построении неприменим.

не будет же он все переписывать, перепарсивать и переделывать.

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

[ThunderCat]

twobomb,

Такой вариант рабочий, до поры до времени.

вот на это я попросил привести альтернативу хранению в мэни-ту-мэни. И чтобы не возникало разночтений, так как вы упомянули что зависит от количества, написал что записей МНОГО. Но внятного описания какой-то ни было иной структуры хранения все-таки не увидел.

PS: Принимается даже вариант "Ой, все..." )

[twobomb]

ThunderCat, Мэни ту мэни тут в любом случае, я сам такую структуру использую, только это не отменяет того факта что вскоре все начинает тормозить и нужно придумывать как всё это ускорить. Поэтому для относительно больших баз нужно сразу это продумывать, недостаточно того что просто создали 2 таблицы и всё прекрасно работает. Вскоре заказчик захочет чтобы в каждой категории товара показывались топовые теги и их количество в товарах этой категории. Мы начинаем писать супер селекты с кучей джоинов, а потом пользователи начинают заходить на эту страницу все начинает тупить и все ложится потому-что наши запросы отрабатывают по пол часа. Поэтому в таких случаях нужен как минимум кеш, а то и отдельные таблицы где будет это все хранится и со своей структурой. Это просто маленький пример и нужно смотреть по обстоятельствам, рассматривать конкретный случай где эти теги будут использовать, как часто, сколько ....

[ThunderCat]

twobomb, Это называется преждевременная оптимизация, причем заранее приносящая убытки по многим направлениям. Ничего не бывает бесплатно, и все имеет свою цену, как по ресурсам, так и по удобству. Кэширование само по себе огромный компромис между скоростью и актуальностью, а расширения парка железа кроме того что само по себе не дешёвое, еще и требует некоторого уровня персонала для развертывания и обслуживания. Так вот, как это ускорить - вопрос достаточно отдаленного будущего и к вопросу как это сделать оптимально не относится от слова совсем.

Я бы понял если бы вы написали "в перспективе можно проделать дополнительную оптимизацию так-то и так-то", но заявление "Ага и потом мы делаем поиск по тегу и идем курить" и "не дал ответ на вопрос автора. Он просто предложил свой вариант организации..." никак не тянет на дополнение ответа.