Как организовать хранение множества тегов в БД?
Но что делать в случае когда вопросы/посты подразумевают хранение сразу нескольких тегов. То есть связь между 1м questions и 1м tags я понимаю как организовать, но что делать если кол-во тегов динамическое. Как в этом случае организовать БД?
- Вопрос задан более двух лет назад
- 65 просмотров
Комментировать
Решения вопроса 1
Для правильного вопроса надо знать половину ответа
А что вам мешает в таблицу questions_tags добавить несколько записей с одним question_id и разными tag_id?
Как сделать теги правильно? НЕ облако!
Как спроектировать бд для тегов? Какой запрос?
Я сделал, таблицу тегов + таблицу тег id и id запись. Плохое решение? С запросом проблемы. При пагинации как выбрать одним запросом все статьи и нужные теги? Без тегов такой запрос.
>select article.title, article.content, article.dates, authors.name, cathegory.name as cathegory from article, authors, cathegory where article.id_author = authors.id and cathegory.id = article.id_cathegory limit 20 order_by article.id desc;
�� Подобається Сподобалось 0
До обраного В обраному 0
59 коментарів
общая идея такая: не используйте join
для этого условия и пагинация делается над одной таблицей — articles, т.е. без JOIN-a.
После получения набора этих записей (их будет не больше 10, где 10 — кол-во записей на странице при пагинации), далее идем в остальные справочные таблицы (категории, теги, авторы) и вытягиваем нужные данные.
— первый запрос
select . from articles
where __условие_поля_таблицы_articles__
order by .. limit
данный запрос вернет 10 нужных статей
— делаем запросы на получение тегов, авторов и категорий для полученных статей
— чтобы получить категории:
получаем массив category_id этих статей (в коде, не в SQL) — просто пройтись в коде по массиву статей.
select .. from cathegory where id IN ( массив этих cathegory_id)
— для тегов чуть сложнее
получаем список id статей из массива статей — аналогично как для категорий — в коде.
select . from tags
where id IN (select tag_id FROM tags_articles WHERE article_id IN (список id статей) )
тут join можно сделать, потому что структура запроса другая.
** если у вас условия более сложные и не только на поля из таблицы статей, то данную идею тоже можно модифицировать, чтобы не было join..
чем плохи join-ы?
вот рабочий запрос
select tag from tags where id in (select id_tag from tagarticle where id_article in ((тут array) ));
правильно ? при таком запросе, выводит все теги для всех статей. методами sql конвертировать для каждой статьи в один кортеж нужные теги, нельзя?
вот мой вариант решения с join.
$this->data[’query’] = $this->db ->select(’article.id, article.title, article.content, article.dates, authors.name, cathegory.name as cathegory’) ->from(’article left join authors on article.id_author = authors.id left join cathegory on article.id_cathegory = cathegory.id’) ->order_by(’article.id’, ’desc’) ->limit(20, $url_segment) ->get (); foreach ($this->data[’query’]->result() as $value) < try < $value->tags = $this->getTags($value->id); > catch (UnexpectedValueException $exc) < echo $exc->getMessage(); > > private function getTags($num) < if(is_numeric($num)) < $tags_query = $this->db ->select(’tag’) ->from(’tags join tagarticle on tags.id = tagarticle.id_tag and tagarticle.id_article =’ . $num) ->get(); $tags = ’’; foreach ($tags_query->result() as $value) < $tags .= ’, ’ . $value->tag; > return $tags; > else < throw new UnexpectedValueException(’not numeric, Ops!’); >>
в сумме два цикла от 40 до 180 итераций и 21 запрос. как можно улучшить решение?
21 запрос. вы шутите?
не делайте в цикле запросы, как у вас
foreach ($this->data[’query’]->result() as $value)
$value->tags = $this->getTags($value->id); — тут подразумевается запрос к базе?
см. выше — я написал решение, как сделать
— проблем с join нет, если условия в WHERE используются
т.е. этот запрос вполне нормальный
select tag from tags where id in (select id_tag from tagarticle where id_article in ((тут array) ));
а какие проблемы возникают, если использовать ansi join?
select tag, id_article from tags left join tagarticle on tags.id = id_tag where tags.id in (select id_tag from tagarticle where id_article in (142, 141)) and id_article in (142, 141);
select .. from cathegory where id IN ( массив этих cathegory_id)
практически хрестоматийный пример как делать не стоит. это касается всех этих IN.
чем плохи джойны?
почему реляционные базы данных называются реляционными?
Т.е. ИН-ы нельзя использовать потому что реляционные базы данных называются реляционными? Гениально. Хорошо хоть что не потому что гладиолус.
нет. насчёт реляционных баз я спросил человека исключительно потому, что он посоветовал не использовать джоины. ины хуже, чем exists исключительно потому, что это всегда последовательное сканирование, что явно медленнее, чем индекс скан.
человек как-то писал что-то похожее. оно как-то работало. он решил поделиться реализацией. получилось что-то вроде вредных советов
Я не понял почему ин-ы это обязательно последовательное сканирование?
вы просто посмотрите план запроса.
Конкретный план запроса ни о чем не говорит, так как может варьироваться от базы к базе и даже в рамках одной и той же БД в зависимости от собранной статистики. Какие конкретные ограничения добавляют ИН-ы, которые исключают index scan?
вы уже посмотрели план запроса? 🙂
для того, чтобы был индекс скан нужен индекс.
Я план запроса не собиртаюсь смотреть по описанным мной причинам. Ну и если у тебя индекса нет то как джойн вдруг сделает индекс скан?
так в том то и дело, что в таблице есть индекс, а вот в выборке из этой таблицы уже не будет индекса.
Ну точно такая же ситуация будет и с джойном. Ин-ы вообще если ты не в курсе разворачиваются оптимизатором в джойны в нормальных базах и работают абсолютно одинаково
да не будет такой же ситуации.
ин и джойн не всегда дает одинаковый результат
А нука приведи пример?
если в ине повторяются значения.
Шота я неосилил понять какая там разница будет. Не затруднит ли уважаемого джентельмена все таки привести пример?
ну вот смотрите, если выборка в IN будет вроде этой (1,1,3), а в таблице будут значения 1,1,4,5 — то IN вернет 2 строки, а JOIN 4
Очевидно что такой in развернется в join с ключевым словом distinct в select clause
и выйдет еще 3 выборка. отлично
Выйдет абсолютно тоже самое если бы ты решил написать такой же джойн руками
оно слепит дублирующиеся строки из таблицы.
вот для примера в таблице строки 2, А; 2; А, а в выборке 2, 2
в результате запрос с IN вернет вам 2 строки, запрос с джойном 4, запрос с дистинктом и джойном 1 строку
Ну вот в твоем последнем примере оптимизатор развернет IN в операцию называемую semi-join, которая хоть не имеет выражения в SQL-e но имеет место быть внутри во всех распространенных БД(mysql, postgre, ms sql, oracle, etc)
причем тут сравнение IN и JOIN ? не в этом суть.
просьба прочесть идею решения до конца.
см. коммент выше.
Ну да, производительность in vs join это офтопик.
да не будет такой же ситуации.
ин и джойн не всегда дает одинаковый результат
так и есть, разные связи бывают.
Например, у каждой статьи может быть теги, могут не быть.
Поэтому нужно возиться с LEFT JOIN, а если JOIN много в цепочке, то нужно очень аккуратно делать. Иначе может быть так, что если у статьи нет тегов, или у статьи нет категории (* просто для примера*), то эта запись со статьей не попадет в итогой набор.
но ведь речь сейчас не об этом.
речь сейчас о конкретной структуре БД и как будет движок БД выполнять IN по сравнению с JOIN.
Ну точно такая же ситуация будет и с джойном. Ин-ы вообще если ты не в курсе разворачиваются оптимизатором в джойны в нормальных базах и работают абсолютно одинаково
а я вот посмотрел план запроса.
и проверил все эти запросы в SQL Server, где он не обманывает и более правильно выбирает индексы.
так вот, планы запроса ОДИНАКОВЫЕ.
и тут двух мнений быть не может.
вы неправильно поняли идею. идея не в том, чтобы заменить JOIN на IN.
идея в том, чтобы сначала сделать запрос к одной таблице (где будет WHERE, и пагинация), после чего получить ровно 10 записей.
и дальше уже работать только с этими 10 записями.
а не делать в одном запросе кучу JOIN и там делать кучу условий и пагинацию.
а почему вы так упорно не хотите всё это сделать на уровне базы данных за один запрос?
см. мой коммент тут
я постарался объъяснить в этом комменте
еще раз повторю, что идея не в том, чтобы просто взять JOIN и заменить на IN.
человек как-то писал что-то похожее. оно как-то работало. он решил поделиться реализацией. получилось что-то вроде вредных советов
пожалуйста, разберитесь в решении, а потом делайте оценку с конкретными аргументами.
Моя реализация требует вместо одного большого JOIN к 4 таблицам (а может и больше), заменить на 4 отдельных запроса. С одной стороны 4 запроса медленнее, чем 1, но с другой стороны это решение имеет свои плюсы. А в случаях со сложными условиями, решение с одним большим JOIN — просто будет тормозить.
А в случаях со сложными условиями, решение с одним большим JOIN — просто будет тормозить.
то-есть вы всех обхитрили. сделали тот же джойн, только на стороне php и всё вдруг заработало быстрее?
анализируя порядок доступа к данным, будет происходить следующее:
— движок БД джойнит две таблицы (одна из которых огромная), .
ниправильно, движек скорее всего сделает сначала фильтрацию используя индексы, а потом уже будет джойнить получившиеся отфильтрованные результаты, и никаких джойнов огромных страниц не будет
полученные отфильтрованные результаты будут достаточно большими
В твоей идее выигрышь только в пагинации до джойна, я так понимаю это тоже можно засунуть в скл.
то-есть вы хотите сказать, что вы нашли лучшее решение, чем архитекторы СУБД?
мое решение основано на использовании средств СУБД.
но любой инструмент (СУБД) нужно понимать как он работает и как использовать.
например, как вы будете делать следующий запрос:
получить список статей с заданными авторами (например, у которых рейтинг = 5, либо начинающиеся на букву A%) + условия на сами статьи
(например, за 2011 год) + пагинация (по дате публикации).
отчего возникли системы NOSQL ? они что нашли более лучше архитектуру, чем реляционные СУБД? нет конечно, они сделали архитектуру, оптимизированную под конкретные задачи..
ну вы понимаете, что все эти системы носкл ориентированы на другое хранение данных. вы же храните их в реляционной субд, но джойны делаете в пхп.
конечно же джойн на стороне сервера БД работает быстрее, чем в коде.
мое решение возникло для задач, когда нужно делать сложный поиск.
например,
запрос, чтобы получить список статей с заданными авторами (например, у которых рейтинг = 5, либо начинающиеся на букву A%) + условия на сами статьи
(например, за 2011 год) + пагинация (по дате публикации).
В этом случае обычный джойн будет тормозить.
потому что доступ к данным будет примерно следующий:
— движок БД будет джойнить два достаточно больших набора данных.
с одной стороны набор авторов отфильтрованный по рейтингу = 5 (может быть достаточно большим),
с другой стороны набор статей за 2011 год тоже достаточно большой.
т.е. нужно дальше работать с этим сджойненным набором, чтобы дальше его отсортировать по дате публикации и сделать пагинацию.
а лучше было бы так:
— до джойна получить ограниченный набор статей, т.е. чтобы все фильтры применялись над одной базовой таблицей и там же сделать пагинацию.
т.е. получить 10 записей статей.
— и уже дальше джойнить эти 10 статей с другими связанными таблицами.
Стараются максимально сократить объем данным, с которым работают, на первом этапе.
Конечно, для этого нужно сделать денормализацию.
Также когда нужно делать джойны кучи таблиц (скажем то возникают много мелких вопросов:
— сделать LEFT JOIN, чтобы не выпали статьи
— дублирование записей (например, если к статьям приджойнить теги, когда у одной статьи будет много тегов)
Если ваш слой доступа к БД все это умеет делать — то не проблема.
Но мне удобнее сначала сделать один запрос к статьям, получить эти 10 статей,
а дальше уже получить все справочные данные для этих статей. И такое решение будет более масштабируемым.
Но в достаточно простом примере, который привел ТС, все эти мои аргументы не имеют смысла.
в этом комменте я привел другие нюансы:
Как хранить теги к посту?
Есть таблица постов с полями id, caption, content, date и тд. Каждый пост может содержать несколько тегов(обычные теги для блога). Как хранить теги в БД, чтобы можно было сделать запрос вида: «select * from posts where tag=»?
Отслеживать
задан 7 фев 2013 в 14:03
61 3 3 серебряных знака 14 14 бронзовых знаков
Создать отдельную таблицу для тегов и таблицу, где будут два поля: id поста и id тега.
7 фев 2013 в 14:28
1 ответ 1
Сортировка: Сброс на вариант по умолчанию
В данном треде обсуждали схожий вопрос
Отслеживать
ответ дан 7 фев 2013 в 14:29
646 3 3 золотых знака 28 28 серебряных знаков 50 50 бронзовых знаков
- база-данных
- mysql
-
Важное на Мете
Связанные
Похожие
Подписаться на ленту
Лента вопроса
Для подписки на ленту скопируйте и вставьте эту ссылку в вашу программу для чтения RSS.
Дизайн сайта / логотип © 2023 Stack Exchange Inc; пользовательские материалы лицензированы в соответствии с CC BY-SA . rev 2023.10.27.43697
Нажимая «Принять все файлы cookie» вы соглашаетесь, что Stack Exchange может хранить файлы cookie на вашем устройстве и раскрывать информацию в соответствии с нашей Политикой в отношении файлов cookie.
Хранение тегов к изображениями в mysql
Есть набор изображений. Пока их было меньше 100 рядом с каждым хранил xml файл с описанием и списком тегов. Грепал каталог по тегу, получал список файлов, скармливал дальше.
ВНЕЗАПНО каталог разросся до 500 ГБ. греп теперь работает 5 минут, что неудобно. Хочу перенести в базу mysql, благо она уже поднята.
Как мне хранить теги к изображениям (структура таблицы), учитывая что у изображения есть минимум один тег, и может их быть хоть полмиллиона. С заточкой под производительность, ибо файлов очень много.
PPP328 ★★★★★
17.06.17 20:30:15 MSK
← 1 2 →
Каждая пара «файл — тэг» — одна строка таблицы. Т.е. таблица:
id filename tag 1 barsik.jpg котики 2 barsik.jpg Барсик 3 mypasport.jpg документы 4 mypasport.jpg мои-фото 5 mypasport.jpg приватное Итого:
barsik.jpg — котики, Барсик
mypasport.jpg — документы, мои-фото, приватное
Индексы по filename и tag (и primary индекс по id, это самому мускулю нужно).
Для каждого файла можно найти все относящиеся к нему тэги, для каждого тэга можно найти все относящиеся к нему файлы. И то и другое очень быстро. Каждая строка таблицы содержит один факт, минимальную и достаточную единицу информации (принадлежность тэга фотографии).
Можно заморочиться и сделать вторую таблицу, словарь, в которой сопоставить каждый тэг с его ID (tag_id) и в первой таблице в колонке tag использовать уже tag_id. Это сэкономит сколько-то там копеек места, даст +2 к сурьёзности и усложнит базу данных
MrClon ★★★★★
( 17.06.17 21:39:33 MSK )
Последнее исправление: MrClon 17.06.17 21:40:06 MSK (всего исправлений: 1)
Не хочешь готовое решение: http://piwigo.org/ ?
sin_a ★★★★★
( 17.06.17 21:43:35 MSK )
Тебе нужно только описание и теги? Тогда почему бы не использовать xattrs ( user.xdg.comment и user.xdg.tags соответственно) файловой системы? Стандарт freedesktop как-никак.
gasinvein ★★★
( 17.06.17 22:06:02 MSK )
Ответ на: комментарий от MrClon 17.06.17 21:39:33 MSK
Можно заморочиться и сделать вторую таблицу,
На самом деле правильный вариант строится из трёх таблиц.
- id, filename
- id, tag
- file_id, tag_id
surefire ★★★
( 17.06.17 22:56:28 MSK )
Ответ на: комментарий от MrClon 17.06.17 21:39:33 MSK
Чет я не влетаю как сделать селект по нужным тегам (с обязательным условием «И»)
PPP328 ★★★★★
( 17.06.17 23:09:31 MSK ) автор топика
Ответ на: комментарий от PPP328 17.06.17 23:09:31 MSK
Вроде джойнами или подзапросами можно. Так с ходу не напишу, надо мозг включать, а я устал
MrClon ★★★★★
( 17.06.17 23:20:23 MSK )
Ответ на: комментарий от MrClon 17.06.17 23:20:23 MSK
Чет извращение какое-то. А если выборка по 10 тегам?
PPP328 ★★★★★
( 17.06.17 23:23:33 MSK ) автор топика
Ответ на: комментарий от PPP328 17.06.17 23:09:31 MSK
Чтоб найти все теги из какого-то файла:
select tag where filename="barsik.jpg" from table;Чтоб найти все файлы, содержащие нужный тег:
select filename where tag="приватное" from table;
Но вообще лучше создать базу из 3 таблиц, как советовал surefire , так правильнее. За подробностями — в любую книжку по основам реляционных баз данных.
И для очень больших баз лучше использовать postgresql, мускул на них может тоже притормаживать.
aureliano15 ★★
( 17.06.17 23:23:53 MSK )
Ответ на: комментарий от aureliano15 17.06.17 23:23:53 MSK
Чтоб найти все файлы, содержащие нужный тег:
Да ради поиска по одному тэгу и базой можно было не заморачиваться. Как сделать выборку по 10 тегам?
PPP328 ★★★★★
( 17.06.17 23:24:47 MSK ) автор топика
Ответ на: комментарий от aureliano15 17.06.17 23:23:53 MSK
Чет я не влетаю как сделать селект по нужным тегам (с обязательным условием «И»)
Где в твоем запросе «И»??
PPP328 ★★★★★
( 17.06.17 23:26:56 MSK ) автор топика
Ответ на: комментарий от PPP328 17.06.17 23:23:33 MSK
Ты вручную что-ли запросы писать собираешься? Напиши утилиту которая будет герить запросы и выдавать результат в удобном тебе формате. Альтернативы обязательно нарушают принципы построения реляционных БД и, опционально, требуют жутких костылей вроде tags like ‘%котики%’ and tags like ‘%Барсик%’ (да, на первый взгляд это может показаться хорошей идеей, но на самом деле это не так)
MrClon ★★★★★
( 17.06.17 23:35:03 MSK )
Ответ на: комментарий от PPP328 17.06.17 23:24:47 MSK
Ну, например, так для 2 тегов:
select filename from table where filename in (select filename from table where tag='приватное') and tag='документы';Но подзапросы, мне кажется, вещь не очень эффективная, поэтому я бы самые частые теги вынес в отдельные поля, а остальные — по заданной схеме с помощью подзапросов.
aureliano15 ★★
( 18.06.17 00:15:25 MSK )
Ответ на: комментарий от MrClon 17.06.17 23:35:03 MSK
Да даже если утилитой, select 10 кратной вложенности сводит на нет преимущества использования mysql.
PPP328 ★★★★★
( 18.06.17 00:43:23 MSK ) автор топика
Ответ на: комментарий от PPP328 18.06.17 00:43:23 MSK
Уверен? Проверял? На счёт вложенных селектов не поручусь, вроде есть с ними какие-то косяки в мускуле, но джойны работают довольно быстро. Да даже если «джойнить вручную», в скрипте, это всё-равно будет быстрее грепа по куче файлов
MrClon ★★★★★
( 18.06.17 09:34:16 MSK )
Ответ на: комментарий от aureliano15 18.06.17 00:15:25 MSK
И потом где-то отдельно помнить какие теги какие? Ну нафиг
MrClon ★★★★★
( 18.06.17 09:34:59 MSK )
Ответ на: комментарий от MrClon 18.06.17 09:34:59 MSK
А зачем помнить? В программе прописать один раз. Если тег не прописан, то в отдельной таблице, иначе — поле.
aureliano15 ★★
( 18.06.17 09:39:08 MSK )
Ответ на: комментарий от MrClon 18.06.17 09:34:16 MSK
вроде есть с ними какие-то косяки в мускуле
Я вообще не вижу реальных преимуществ мускула перед постгресом, кроме разве что привычки.
aureliano15 ★★
( 18.06.17 09:40:26 MSK )
Ответ на: комментарий от aureliano15 18.06.17 09:39:08 MSK
Просто перекладываешь свою память в программу. Усложнение никуда не девается
MrClon ★★★★★
( 18.06.17 09:48:34 MSK )
Ответ на: комментарий от MrClon 18.06.17 09:48:34 MSK
Что такое усложнение? Имхо, если программа будет на 3 строчки длиннее, а транзакций с бд будет в 5 раз меньше, то это упрощение, а не усложнение. И сточки зрения логики, и с точки зрения скорости.
aureliano15 ★★
( 18.06.17 09:52:15 MSK )
Ответ на: комментарий от aureliano15 18.06.17 09:40:26 MSK
Высокая производительность из коробки, а не после вдумчивого тюнинга. И ещё можно публично игр громко обвинять в своих косяках и идиотизме «этот дебильный мускуль», и получать моральную поддержку от таких-же дебилов и фанбоев других СУБД, с постгресом так наверное не выйдет потому-что средняя компетентность pg-админа выше, а средняя любовь к своей СУБД ещё выше. Вместо сочувствия тебе могут объяснить почему ты идиот
MrClon ★★★★★
( 18.06.17 09:53:26 MSK )
Ответ на: комментарий от MrClon 18.06.17 09:34:16 MSK
но джойны работают довольно быстро
Кстати да, можно и через джойн:
select a.filename, a.tag, b.tag from table as a, table as b where a.tag = 'приватное' and b.tag = 'документы' and a.filename = b.filename;aureliano15 ★★
( 18.06.17 09:55:02 MSK )
Ответ на: комментарий от aureliano15 18.06.17 09:52:15 MSK
Усложнение это усложнение логики, а не усложнение вычислений. Тут ведь явно не те объёмы что-бы впиливать костыли для повышения производительности
MrClon ★★★★★
( 18.06.17 09:56:10 MSK )
Постановка задачи несколько размытая.
Я бы даже сказал, что тут описаны скорее какие-то фантазии на тему решения некоей задачи, а не сама задача.
Совершенно на пустом месте взялась сущность в виде xml файлов.Про MySQL вообще смешно, это типичный пример «когда у тебя в руках молоток, всё вокруг кажется гвоздями».
Попробуй описать ЧТО нужно сделать, не говоря КАК нужно сделать.
zolden ★★★★★
( 18.06.17 09:59:52 MSK )
Последнее исправление: zolden 18.06.17 10:01:33 MSK (всего исправлений: 1)
Если строить запросы с десятком джоинов или подзапросов всё-таки не хочется, и перестраивать схему при создании нового тега тоже, то может перестать уже насиловать РСУБД и взять что-то более подходящее? Вроде radis умеет хранить списки и делать по ним запросы. Да мало-ли всяких no-sql поделок
MrClon ★★★★★
( 18.06.17 10:01:01 MSK )
Ответ на: комментарий от MrClon 18.06.17 10:01:01 MSK
aureliano15
А покажите каким будет джойн на 10 тегов?
Короче сделаю пока так:
filename = "boris.jpg" tags = "jpg лето 2017 геленджик" filename = "anastasia.png" tags = "png осень 2017 геленджик" select filename from table where tags like "%2017%" and tags like "%геленджик%" \G Просяду по скорости сильно — буду думать. Ожидание до 5 секу допустимо.
PPP328 ★★★★★
( 18.06.17 10:05:21 MSK ) автор топика
Ответ на: комментарий от MrClon 18.06.17 09:56:10 MSK
Тут ведь явно не те объёмы что-бы впиливать костыли для повышения производительности
Вот тут ничего не могу сказать. Надо тестировать. ТС, как я понял, боится, что всё будет очень медленно. Если его страхи при тестировании подтвердятся, то стоит впиливать костыли, а если нет, то не стоит.
aureliano15 ★★
( 18.06.17 10:09:18 MSK )
Ответ на: комментарий от PPP328 18.06.17 10:05:21 MSK
А покажите каким будет джойн на 10 тегов?
Практически таким же, но чуток длиннее (показываю на 4, потому что на 10 — лениво):
select a.filename, a.tag, b.tag, c.tag, d.tag from table as a, table as b, table as c, table as d where a.tag = 'приватное' and b.tag = 'документы' and c.tag = 'лето' and d.tag = 'геленджик' and a.filename = b.filename and c.filename = d.filename and a.filename = c.filename;Короче сделаю пока так:
Просяду по скорости сильно — буду думать. Ожидание до 5 секу допустимо.
Тоже верно. Чем проще — тем лучше. А может оно и быстрее будет за счёт меньшего числа поисков. Хотя это, опять же, надо тестить.
aureliano15 ★★
( 18.06.17 10:19:59 MSK )
Ответ на: комментарий от PPP328 18.06.17 10:05:21 MSK
Так и знал что всё закончится этим костылём.
Минусы как минимум:
1) Фулскан, не используются индексы. Мускуль прочтёт с диска всю таблицу и сравнит каждую ячейку колонки tags с шаблоном. По сути это недалеко от грепа
2) Ища фото с тегом «котики» ты найдёшь за одно и фото с тегом «наркотики». обходится ещё одним костылём
MrClon ★★★★★
( 18.06.17 10:23:14 MSK )
Это линуксоиды, ять? Срамота! Им родина дала специализированные СУБД, пользуйся! Не хотят! Фулсканы по шаблонам жрать хотят
MrClon ★★★★★
( 18.06.17 10:26:48 MSK )
Ответ на: комментарий от MrClon 18.06.17 10:26:48 MSK
Так сам же сказал, что может производительность будет приемлемой. ТС потестит, если скорость не понравится, то переделает. Это ведь поделка для домашнего использования, а не релиз. 🙂
aureliano15 ★★
( 18.06.17 10:29:51 MSK )
Ответ на: комментарий от MrClon 18.06.17 10:23:14 MSK
Так и знал что всё закончится этим костылём.
Да. А делать 10тикратный вложенный селект или джойнить одну и ту же таблицу сам с собой 10 раз ну прям вообще оптимальное решение.
PPP328 ★★★★★
( 18.06.17 12:41:26 MSK ) автор топика
Ответ на: комментарий от MrClon 18.06.17 10:23:14 MSK
2) Ища фото с тегом «котики» ты найдёшь за одно и фото с тегом «наркотики». обходится ещё одним костылём
`like «% котики %»` + tags будет начинаться и заканчиваться на » «.
PPP328 ★★★★★
( 18.06.17 12:42:39 MSK ) автор топика
MrClon , surefire , aureliano15 , надыбал сравнение обоих методов, на моих масштабах `text` проще всего.
PPP328 ★★★★★
( 18.06.17 12:50:13 MSK ) автор топика
Ответ на: комментарий от surefire 17.06.17 22:56:28 MSK
На самом деле правильный вариант — это одна таблица, а суррогатные ключи «id» не нужны и вредны.
anonymous
( 18.06.17 13:28:45 MSK )
Ответ на: комментарий от PPP328 18.06.17 12:50:13 MSK
на моих масштабах `text` проще всего
Не факт. Там немного другая бд. Кроме того, использовались ли в этих тестах индексы? Ведь они хоть и замедляют добавление данных, зато ускоряют их поиск.
Я бы сначала потестил самый простой вариант с простым текстом. Если результаты будут удовлетворительными, то от добра добра не ищут, и лучшее враг хорошего. Если нет, то попробовал бы самый корректный с точки зрения теории баз данных вариант с 3 таблицами, предложенный в посте Хранение тегов к изображениями в mysql (комментарий) . Затем вариант с 2 таблицами, здесь база не нормализована, зато на 1 таблицу меньше. И, наконец, если все варианты окажутся неудовлетворительны по времени, то вариант с дополнительной таблицей, включающей наиболее частые теги как отдельные поля (true если есть или false если нет). Не забывая на каждом шаге играться с индексами.
aureliano15 ★★
( 18.06.17 13:40:38 MSK )
Ответ на: комментарий от PPP328 18.06.17 12:50:13 MSK
Кстати, 1-ый вариант с простым текстом куда проще реализовать простым текстовым файлом формата
filename tag1 tag2 tag3 . и грепать его. Будет, думаю, не медленнее, т. к. индексов не будет ни в мускуле, ни в текстовом файле, а принцип один. Зато grep лучше поддерживает регулярные выражения, а простой текст проще и править, и смотреть, и переносить в другой каталог/на другой комп или архивировать. Плюс число тегов в строке будет неограничено, в отличие от таблицы (в таблице тоже можно сделать очень большое поле переменной длины, но оно может быть менее эффективным).
Единственное ограничение: filename и теги не должны содержать пробелов. Но и оно легко обходится, если в качестве разделителя выбрать другой символ, например запятую или двоеточие (или что-то ещё).
aureliano15 ★★
( 18.06.17 13:52:04 MSK )
Ответ на: комментарий от aureliano15 18.06.17 13:52:04 MSK
MrClon ★★★★★
( 18.06.17 14:37:48 MSK )
Ответ на: комментарий от aureliano15 18.06.17 10:19:59 MSK
TC, MrClon , aureliano15 , а как вам вариант
TABLE entity (id, filename, tag_1_id, tag_2_id, tag_3_id..) SELECT FROM entity WHERE (tag_1_id = 1 OR tag_2_id = 1 OR. ) AND . AND (tag_1_id = 10 OR tag_2_id = 10 OR. ) понятно что ограниченное число тегов, но зато быстро, удобно и довольно компактно даже для 10 тегов на запись (а десяти тегов вероятно хватит даже самым требовательным).
AndreyKl ★★★★★
( 18.06.17 14:44:57 MSK )
Последнее исправление: AndreyKl 18.06.17 14:46:24 MSK (всего исправлений: 1)
Ответ на: комментарий от AndreyKl 18.06.17 14:44:57 MSK
кстати, можно ещё компактнее: скажем, если ограничить размер словаря в 16 бит (т.е. 65536 записей, что довольно много), можно использовать скажем одно 64 битное интовое поле для хранения 4 тегов. итого для хранения 12 тегов нужно всего три 64-битных поля и небольшие вычисления перед запросом. что скажем соответствует длине строки в 24 символа в utf8, если я верно понимаю. не так мало, но вероятно не хуже остальных вариантов. а скорость будет как на 3х интовых индексах, т.е. моментально, практически.
AndreyKl ★★★★★
( 18.06.17 14:54:50 MSK )
Последнее исправление: AndreyKl 18.06.17 15:01:20 MSK (всего исправлений: 5)
Если хочешь с 1 таблицей и быстрый поиск по строке из тегов в разных сочетаниях, то копай в сторону Fulltext Index.
InnoDB уже умеет, но если хочешь получше, то подключи модуль Mroonga и используй таблицу на основе Engine=Mroonga.
surefire ★★★
( 18.06.17 17:37:19 MSK )
Ответ на: комментарий от AndreyKl 18.06.17 14:44:57 MSK
понятно что ограниченное число тегов, но зато быстро, удобно и довольно компактно
Если для тс ограничения на число тегов приемлемы, то, имхо, вполне нормально. Только на всякий случай добавлю для ТС’а, что здесь подразумевается наличие 2-й связанной таблицы с уникальными полями
create table tags (tag_id integer, tag varchar);И, имхо, поле id избыточно, т. к. уникальным идентификатором является filename, который должен быть unique.
Вот команды для создания таблиц и выборки результатов для постгрес (для мускула они будут немного отличаться, и для простоты я сделал только 3 поля, но 10 или больше делается аналогично):
create table tags_id (tag_id int primary key, tag varchar unique); -- это работает в постгресе, но в мускуле надо переделать: create sequence tag_id_seq; -- аналогично: alter table tags_id alter column tag_id set default nextval('tag_id_seq'); -- здесь я сделал только 3 тега вместо 10: create table tags (filename varchar, tag1 int references tags_id(tag_id), tag2 int references tags_id(tag_id), tag3 int references tags_id(tag_id)); -- делаем поле filename уникальным, возможно в мускуле это записывается чуть-чуть по-другому: alter table tags add constraint unique_filename unique(filename); -- здесь заполняем сначала таблицу tags_id, затем tags операторами insert. -- для 10 тегов select получится немного длиньше: select t.filename, a.tag, b.tag, c.tag from tags as t, tags_id as a, tags_id as b, tags_id as c where ((a.tag_id=t.tag1 or a.tag_id=t.tag2 or a.tag_id=t.tag3) and a.tag='лето') and ((b.tag_id=t.tag1 or b.tag_id=t.tag2 or b.tag_id=t.tag3) and b.tag='2017') and ((c.tag_id=t.tag1 or c.tag_id=t.tag2 or c.tag_id=t.tag3) and c.tag='геленджик'); А если делать это не в консоли, а программно, то программа может один раз прочитать таблицу tags_id и сама подставлять вместо названий тегов их номера. Тогда будет ещё проще, как у вас.
кстати, можно ещё компактнее: скажем, если ограничить размер словаря в 16 бит (т.е. 65536 записей, что довольно много), можно использовать скажем одно 64 битное интовое поле для хранения 4 тегов.
Наверно можно, но, имхо, сложновато. По-мне так лучше сделать на каждый тег своё числовое поле с минимальной длиной.