Хранение тегов к изображениями в mysql

Каждая пара «файл - тэг» — одна строка таблицы. Т.е. таблица:

id	filename	tag
1	barsik.jpg	котики
2	barsik.jpg	Барсик
3	mypasport.jpg	документы
4	mypasport.jpg	мои-фото
5	mypasport.jpg	приватное

Не хочешь готовое решение: http://piwigo.org/ ?

Тебе нужно только описание и теги? Тогда почему бы не использовать xattrs (user.xdg.comment и user.xdg.tags соответственно) файловой системы? Стандарт freedesktop как-никак.

Можно заморочиться и сделать вторую таблицу,

На самом деле правильный вариант строится из трёх таблиц.

• id, filename
• id, tag
• file_id, tag_id

Чет я не влетаю как сделать селект по нужным тегам (с обязательным условием «И»)

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

Чет извращение какое-то. А если выборка по 10 тегам?

Чтоб найти все теги из какого-то файла:

select tag where filename="barsik.jpg" from table;

Чтоб найти все файлы, содержащие нужный тег:

select filename where tag="приватное" from table;

Но вообще лучше создать базу из 3 таблиц, как советовал surefire, так правильнее. За подробностями - в любую книжку по основам реляционных баз данных.

И для очень больших баз лучше использовать postgresql, мускул на них может тоже притормаживать.

Чтоб найти все файлы, содержащие нужный тег:

Да ради поиска по одному тэгу и базой можно было не заморачиваться. Как сделать выборку по 10 тегам?

Чет я не влетаю как сделать селект по нужным тегам (с обязательным условием «И»)

Где в твоем запросе «И»??

Ты вручную что-ли запросы писать собираешься? Напиши утилиту которая будет герить запросы и выдавать результат в удобном тебе формате. Альтернативы обязательно нарушают принципы построения реляционных БД и, опционально, требуют жутких костылей вроде tags like '%котики%' and tags like '%Барсик%' (да, на первый взгляд это может показаться хорошей идеей, но на самом деле это не так)

Ну, например, так для 2 тегов:

select filename from table where filename in (select filename from table where tag='приватное') and tag='документы';

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

Да даже если утилитой, select 10 кратной вложенности сводит на нет преимущества использования mysql.

Уверен? Проверял? На счёт вложенных селектов не поручусь, вроде есть с ними какие-то косяки в мускуле, но джойны работают довольно быстро. Да даже если «джойнить вручную», в скрипте, это всё-равно будет быстрее грепа по куче файлов

И потом где-то отдельно помнить какие теги какие? Ну нафиг

А зачем помнить? В программе прописать один раз. Если тег не прописан, то в отдельной таблице, иначе - поле.

вроде есть с ними какие-то косяки в мускуле

Я вообще не вижу реальных преимуществ мускула перед постгресом, кроме разве что привычки.

Просто перекладываешь свою память в программу. Усложнение никуда не девается

Что такое усложнение? Имхо, если программа будет на 3 строчки длиннее, а транзакций с бд будет в 5 раз меньше, то это упрощение, а не усложнение. И сточки зрения логики, и с точки зрения скорости.

Высокая производительность из коробки, а не после вдумчивого тюнинга. И ещё можно публично игр громко обвинять в своих косяках и идиотизме «этот дебильный мускуль», и получать моральную поддержку от таких-же дебилов и фанбоев других СУБД, с постгресом так наверное не выйдет потому-что средняя компетентность pg-админа выше, а средняя любовь к своей СУБД ещё выше. Вместо сочувствия тебе могут объяснить почему ты идиот

но джойны работают довольно быстро

Кстати да, можно и через джойн:

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;

Усложнение это усложнение логики, а не усложнение вычислений. Тут ведь явно не те объёмы что-бы впиливать костыли для повышения производительности

Постановка задачи несколько размытая.
Я бы даже сказал, что тут описаны скорее какие-то фантазии на тему решения некоей задачи, а не сама задача.
Совершенно на пустом месте взялась сущность в виде xml файлов.Про MySQL вообще смешно, это типичный пример «когда у тебя в руках молоток, всё вокруг кажется гвоздями».
Попробуй описать ЧТО нужно сделать, не говоря КАК нужно сделать.

Если строить запросы с десятком джоинов или подзапросов всё-таки не хочется, и перестраивать схему при создании нового тега тоже, то может перестать уже насиловать РСУБД и взять что-то более подходящее? Вроде radis умеет хранить списки и делать по ним запросы. Да мало-ли всяких no-sql поделок

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 секу допустимо.

Тут ведь явно не те объёмы что-бы впиливать костыли для повышения производительности

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

А покажите каким будет джойн на 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 секу допустимо.

Тоже верно. Чем проще - тем лучше. А может оно и быстрее будет за счёт меньшего числа поисков. Хотя это, опять же, надо тестить.

Так и знал что всё закончится этим костылём.
Минусы как минимум:
1) Фулскан, не используются индексы. Мускуль прочтёт с диска всю таблицу и сравнит каждую ячейку колонки tags с шаблоном. По сути это недалеко от грепа
2) Ища фото с тегом «котики» ты найдёшь за одно и фото с тегом «наркотики». обходится ещё одним костылём

Это линуксоиды, ять? Срамота! Им родина дала специализированные СУБД, пользуйся! Не хотят! Фулсканы по шаблонам жрать хотят

Так сам же сказал, что может производительность будет приемлемой. ТС потестит, если скорость не понравится, то переделает. Это ведь поделка для домашнего использования, а не релиз. :-)

Так и знал что всё закончится этим костылём.

Да... А делать 10тикратный вложенный селект или джойнить одну и ту же таблицу сам с собой 10 раз ну прям вообще оптимальное решение.

2) Ища фото с тегом «котики» ты найдёшь за одно и фото с тегом «наркотики». обходится ещё одним костылём

`like «% котики %»` + tags будет начинаться и заканчиваться на " ".

MrClon, surefire, aureliano15, надыбал сравнение обоих методов, на моих масштабах `text` проще всего.

http://tagging.pui.ch/post/37027745720/tags-database-schemas
http://tagging.pui.ch/post/37027746608/tagsystems-performance-tests

На самом деле правильный вариант - это одна таблица, а суррогатные ключи «id» не нужны и вредны.

на моих масштабах `text` проще всего

Не факт. Там немного другая бд. Кроме того, использовались ли в этих тестах индексы? Ведь они хоть и замедляют добавление данных, зато ускоряют их поиск.

Я бы сначала потестил самый простой вариант с простым текстом. Если результаты будут удовлетворительными, то от добра добра не ищут, и лучшее враг хорошего. Если нет, то попробовал бы самый корректный с точки зрения теории баз данных вариант с 3 таблицами, предложенный в посте Хранение тегов к изображениями в mysql (комментарий) . Затем вариант с 2 таблицами, здесь база не нормализована, зато на 1 таблицу меньше. И, наконец, если все варианты окажутся неудовлетворительны по времени, то вариант с дополнительной таблицей, включающей наиболее частые теги как отдельные поля (true если есть или false если нет). Не забывая на каждом шаге играться с индексами.

Кстати, 1-ый вариант с простым текстом куда проще реализовать простым текстовым файлом формата

filename tag1 tag2 tag3 ...

и грепать его. Будет, думаю, не медленнее, т. к. индексов не будет ни в мускуле, ни в текстовом файле, а принцип один. Зато grep лучше поддерживает регулярные выражения, а простой текст проще и править, и смотреть, и переносить в другой каталог/на другой комп или архивировать. Плюс число тегов в строке будет неограничено, в отличие от таблицы (в таблице тоже можно сделать очень большое поле переменной длины, но оно может быть менее эффективным).

Единственное ограничение: filename и теги не должны содержать пробелов. Но и оно легко обходится, если в качестве разделителя выбрать другой символ, например запятую или двоеточие (или что-то ещё).

Лорчую

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...)

кстати, можно ещё компактнее: скажем, если ограничить размер словаря в 16 бит (т.е. 65536 записей, что довольно много), можно использовать скажем одно 64 битное интовое поле для хранения 4 тегов. итого для хранения 12 тегов нужно всего три 64-битных поля и небольшие вычисления перед запросом. что скажем соответствует длине строки в 24 символа в utf8, если я верно понимаю. не так мало, но вероятно не хуже остальных вариантов. а скорость будет как на 3х интовых индексах, т.е. моментально, практически.

Если хочешь с 1 таблицей и быстрый поиск по строке из тегов в разных сочетаниях, то копай в сторону Fulltext Index.

InnoDB уже умеет, но если хочешь получше, то подключи модуль Mroonga и используй таблицу на основе Engine=Mroonga.

понятно что ограниченное число тегов, но зато быстро, удобно и довольно компактно

Если для тс ограничения на число тегов приемлемы, то, имхо, вполне нормально. Только на всякий случай добавлю для ТС'а, что здесь подразумевается наличие 2-й связанной таблицы с уникальными полями

create table tags (tag_id integer, tag varchar);

Вот команды для создания таблиц и выборки результатов для постгрес (для мускула они будут немного отличаться, и для простоты я сделал только 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 тегов.

Наверно можно, но, имхо, сложновато. По-мне так лучше сделать на каждый тег своё числовое поле с минимальной длиной.

А покажите каким будет джойн на 10 тегов?

Покажу на 3 тега с данными из твоего поста (filename и tags).

Сетап:

CREATE TABLE images (
       image_id SERIAL PRIMARY KEY,
       filename TEXT NOT NULL UNIQUE
);

CREATE TABLE tags (
       tag_id SERIAL PRIMARY KEY,
       tag    TEXT NOT NULL UNIQUE
);

CREATE TABLE images_tags (
       PRIMARY KEY (tag_id, image_id),
       image_id INTEGER NOT NULL REFERENCES images (image_id),
       tag_id   INTEGER NOT NULL REFERENCES tags (tag_id)
);

INSERT INTO images(filename)
VALUES ('boris.jpg'), ('anastasia.png');

INSERT INTO tags(tag)
VALUES ('2017'), ('png'), ('jpg'), ('лето'), ('осень'), ('геленджик');

INSERT INTO images_tags(image_id, tag_id)
VALUES (1, 1), (1, 3), (1, 4), (1, 6),
       (2, 1), (2, 2), (2, 5), (2, 6);

Запрос на получение изображений, у которых есть хотя бы один тег из списка:

SELECT DISTINCT images.filename
  FROM images
       NATURAL JOIN images_tags
       NATURAL JOIN tags
 WHERE tags.tag IN ('2017', 'лето', 'геленджик');

Запрос на получение изображений, у которых есть все теги из списка:

SELECT images.filename
  FROM images
       NATURAL JOIN images_tags
       NATURAL JOIN tags
 WHERE tags.tag IN ('2017', 'лето', 'геленджик')
 GROUP BY images.filename
HAVING COUNT(images.filename) = 3;

Обрати внимание, что число, стоящее в HAVING-clause, должно равняться количеству тегов, по которым мы ищем. Для 10 тегов это будет HAVING COUNT(images.filename) = 10.

Короче сделаю пока так:

С запросами типа like '%2017%' есть одна проблема: ни MySQL, ни Postgres не могут использовать индексы, если паттерн начинается на wildcard (т.е. первый символ шаблона — '%'). Т.е. в таком запросе индекс будет задействован (для Postgres ещё можно будет уменьшить размер индекса, если применить операторный класс text_pattern_ops):

SELECT ... WHERE tags LIKE '2017%';

а вот в таком — не будет:

SELECT ... WHERE tags LIKE '%2017%';

То есть у тебя всегда будет sequential scan.

А вообще, zolden всё правильно сказал выше: постановка задачи размытая, кроме как «чтоб искало быстро» никаких подробностей нет.

Насколько жёсткие требования к накладным расходам на хранение тегов? Возможно, стоит взять Postgres и хранить теги как массивы строк?

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

Нужна возможность искать не только по точным названиям тегов? Можно использовать полнотекстовый поиск в реализации Postgres или отдельный поисковый движок типа Elasticsearch.

Может быть, тебе БД вообще не нужна, а нужен только поиск (по тегам, названию, дате и пр. атрибутам). Тогда лучше использовать поисховый движок типа Elasticsearch.

Костыли-костылики.

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');

Палю годноту:

create table tags_id (tag_id serial primary key, tag varchar unique);

твой вариант конечно самый интересный с т.з. «лаконичности» и универсальности.

но я таких сложных запросов побаиваюсь, таки having vs index . как по мне так чёрт с ним с неограниченным числом тегов, лишь бы можно было использовать индекс при селекте . А у тебя сканирование временной таблицы получается, или я ошибаюсь?

Палю годноту:

Да. Просто я у себя в консоли сначала сделал так, потом чуток переделал альтером и скопипастил.

но я таких сложных запросов побаиваюсь, таки having vs index . как по мне так чёрт с ним с неограниченным числом тегов, лишь бы можно было использовать индекс при селекте . А у тебя сканирование временной таблицы получается, или я ошибаюсь?

Вопрос адресован theNamelessOne, но он просто сократил 3 мои строчки до одной. Поэтому, предполагая, что на самом деле он адресован мне, отвечаю: у меня то же самое, что и у тебя. Просто вводить-то пользователь будет названия, а не номера тегов. Вот я и сделал соответствующий запрос. Но тут же оговорился, что таблицу соответствия номеров названиям (у меня она называется tags_id) можно прочитать сразу и потом просто подставлять номера, как сделано у тебя. А временных таблиц у меня нет. Они все постоянные.

А временных таблиц у меня нет. Они все постоянные.

Это вопрос полностью звучит так: план который выдаёт mysql по запросу explain содержит tmp table или только using index ?

У моего запроса будет using index, если я не путаю. А что у вас?

просто using index это одно, а сканировать временную таблицу это совсем другое. первое работает очень быстро (почти) всегда, во втором случае время растёт в зависимости от объёма данных причём расти может весьма нелинейно.

А у тебя сканирование временной таблицы получается, или я ошибаюсь?

Да, в моём примере получается HashAggregate, который использует временную хеш-таблицу. Можно добавить сортировку по группируемому столбцу, тогда будет GroupAggregate, который не использует временных таблиц:

SELECT images.filename
  FROM images
       NATURAL JOIN images_tags
       NATURAL JOIN tags
 WHERE tags.tag IN ('2017', 'лето', 'геленджик')
 GROUP BY images.filename
HAVING COUNT(images.filename) = 3
 ORDER BY images.filename;

Правда, теперь у нас появляется сортировка.

Правда, теперь у нас появляется сортировка.

лучше чем временная таблица, конечно, но по сравнению с индексом...

поэтому вопрос по-моему в том критично ли любое кол-во тегов или чего то вроде 10 хватит с головой.

ну и вопрос конечно будет ли там когда то много данных. но ТС вот говорит что 500 Гб фоток уже. фотка условно 5Мб, тогда это 100 тыщ фоток. прилично в принципе.