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

хотя вот что лучше чем временная я не уверен. всяко может быть. сортировка всё таки дело дорогое.

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

Это верно, но можно не лайкать, а строить Fulltext Index , а затем использовать поиск в BOOLEAN MODE, работает очень быстро и будет удобно складывать и даже исключать теги, например «+геленджик -лето» найдет все геленджики кроме тех, что с летом.

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

Вот этого не скажу. У меня только постгрес есть, а он про tmp ничего не говорит.

сортировка всё таки дело дорогое.

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

вероятно лучшее решение, я уже и забыл про фуллтекс индексы

Можно взять Postgres и хранить теги (или идентификаторы тегов) в массивах, получится примерно такой вариант, как и у тебя, только без ограничения на количество тегов для отдельной записи и монструозных запросов.

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

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

А кстати говоря, постгрес может полнотекстовый поиск в булевом режиме (как в решении у surefire)?

надо будет поглядеть о чём ты.

Ссылка.

Спасибо за ссылку.

Что то в searching in arrays не упомянуто про индексы. Индексы то будут?

АПД.

Ан нет, пардон, сказали будут индексы, это я пропустил.

Спасибо.

Вопрос про фуллтекст в силе, если будет минутка, буду благодарен.

А кстати говоря, постгрес может полнотекстовый поиск в булевом режиме

в булевом режиме

Это типа «искать документы, в которых встречаются одни теги и нет других»? Может, ссылка раз:

to_tsquery creates a tsquery value from querytext, which must consist of single tokens separated by the Boolean operators & (AND), | (OR) and ! (NOT). These operators can be grouped using parentheses.

ссылка два.

Рекомендую почитать доку.

Ты забыл про бульонпартицирование?

Похоже на правду, благодарю за ссылки.

Что то в searching in arrays не упомянуто про индексы. Индексы то будут?

Да, массивы можно проиндексировать GIN-индексами, которые поддерживают операторы @> (массив содержит подмассив), <@ (подмассив содержится в массиве), =, && (у двух массивов непустое пересечение, то есть у них есть общие элементы). Операции «содержится» можно использовать, чтобы найти записи, в которых есть все теги из перечисленных, операцию «пересечение» — чтобы найти записи, в которых есть любой из перечисленных тегов.

УЖСНХ...

Вот на три таблицы, выборка по множеству тегов, быстрая:

SELECT
        f.filename
    FROM tags_files tf
    INNER JOIN files f
        ON f.id = tf.file_id
    WHERE tf.tag_id IN(
        SELECT
                id
            FROM tags
            WHERE name IN('tag1', 'tag2', 'tag3', 'tagN')
    )
    GROUP BY f.id

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

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

В оригинальном сообщении теги соединяются операцией AND а у тебя OR.

Ну тогда HAVING в конец добавляем чтобы было равно кол-ву переданных тегов. Но мне было бы интересно погонять на больших объемах данных вот эти вагончики:

SELECT
        f.filemane
    FROM files f
    /* tag1 */
    INNER JOIN tags_files tf1
        ON tf1.file_id = f.id
    INNER JOIN tags t1
        ON t1.id = tf1.tag_id
            AND t1.name = 'tag1'
    /* tag2 */
    INNER JOIN tags_files tf2
        ON tf2.file_id = f.id
    INNER JOIN tags t2
        ON t2.id = tf2.tag_id
            AND t2.name = 'tag2'
    /* tag3 */
    INNER JOIN tags_files tf3
        ON tf3.file_id = f.id
    INNER JOIN tags t3
        ON t3.id = tf3.tag_id
            AND t3.name = 'tag3'
    /* tagN */
    INNER JOIN tags_files tfN
        ON tfN.file_id = f.id
    INNER JOIN tags tN
        ON tN.id = tfN.tag_id
            AND tN.name = 'tagN'

Так id есть, по нему можно будет и партиционировать в перспективе (:

вероятно так не получится. сортируется ведь _выборка_. А если индекс создать то он на всю таблицу а не на выборку. в общем случае индекс на таблице никак не поможет отсортировать выборку.

Да. Похоже так и есть. В некоторых случаях даже при выборке можно для сортировки использовать индекс, но не для having. По крайней мере, если верить http://www.nsc.ru/win/docs/db/sql/sql25.htm :

10. Для фильтрации записей используйте WHERE, а не HAVING.

Избегайте использования раздела HAVING вместе с GROUP BY на индексированных столбцах. В этом случае индекс не используется. Фильтруйте строки с помощью раздела WHERE, а не раздела HAVING.

не, это вроде не про сортировку.

просто where может использовать индекс, и тогда отработает очень быстро. Это как раз то что я сделал в своём первом варианте. А having в принципе не может, это ж группировка выборки, там результат непредсказуем в общем случае.

ВНЕЗАПНО каталог разросся до 500 ГБ. греп теперь работает 5 минут, что неудобно

А если использовать локейл вместо грепа?

Кого?

Пипец меня переглючило, локейт.

Так он же не заменит греп. Греп внутри файлов инфу ищет, locate сами файлы.

А точно, я просто вспомнил, что он индексирует файлы. Так наверное есть аналог грепа с индеком?

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

Да, посмотрел внимательней, именно так. Но всё-таки в мускуле возможно в некоторых случаях использование индекса в order by даже при наличии фильтра where. Вот что я нагуглил: http://www.mysql.ru/docs/man/ORDER_BY_optimisation.html и https://habrahabr.ru/post/138163/ .

Хотя, если честно, раньше мне казалось, что индексы для сортировки могут использоваться куда чаще, чем на самом деле. :-)

https://www.youtube.com/watch?v=mkI2q6twyc0

есть конечно, называется РСУБД :)

ссылки на доки хороши, я что то этого не читал раньше, спасибо.

https://www.youtube.com/watch?v=mkI2q6twyc0

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

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

Ага. Дьявол скрывается в деталях.

Вот тебе взгляд с другой стороны (1, 2). Юзеры посгреса в значительной мере склонны к сектантству. Связано с диким и непрекращающимся по сию пору баттхёртом от слова LAMP.

Вот тебе взгляд с другой стороны (1, 2).

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