Как эффективно организовать таблицу для 250 000 записей?

Question

[litvin2]

Коллеги, подключайтесь.

Упрощенная модель такова: таблица POST_ID, KEY, VALUE, которая хранит разные данные для элементов (записей) на сайте.

1. (BIGINT(20)) POST_ID – ID для элемента на сайте
   
2. (VARCHAR(30)) KEY – название поля у элемента
   
3. (LONGTEXT) VALUE – значение поля
   

В таблице может быть несколько строк с одной парой POST_ID и KEY.
VALUE может принимать значения как в один символ, так и в длинные статьи по 4 000 000 знаков.

Мы изначально реализовали хранение, принято везде: например, и Wordpress, и Битрикс хранят такие "пользовательские" данные в одной таблице и точно в таком же формате.

Сейчас возникают опасения, так как подобные выборки выполняются долго:

SELECT POST_ID FROM TABLE WHERE KEY='qwrety' AND VALUE='asdfghjk12345678'

Я подумываю над решением, как это делают с файлами: создать 26 таблиц A, B, C, D... и хранить в каждой KEY начинающийся с соответствующей буквы.

Коллеги, поделитесь опытом – как эту организацию в 250 000 записей можно оптимизировать?

Answer 1

[Melkij]

Если вам действительно, в реальности, зачем-то нужно делать where value = "4 миллиона знаков", то сделайте hash-индекс.
Mysql их не умеет, так что велосипедим рядом ещё одним полем, в которое пишете результат хэш-функции от этого самого текста. И индексируем обычным btree. Например, md5 или даже просто crc32
В запросе, соответственно, where hash = посчитанный хэш and value = "4 миллиона знаков"

Comments

[litvin2]

Офигенное предложение, спасибо!

[d'Ivan]

Melkij crc32 нельзя, так как этот алгоритм не подходит для выявления уникального значения, по которому будет искаться value.
litvin2: простите, является ли этот ответ решением для Вашего вопроса? Если да, то нужно отметить его как решение.

Answer 2

[Макс]

попробуйте полнотекстовый поиск для VALUE.
будет индекс - по 250 000 строкам будет искать моментально.

Comments

[Александр Аксентьев]

про 4 000 000 знаков вы видимо пропустили.

но опять же смотря по какому полю искать.

[Макс]

Александр Аксентьев: нет, как раз не пропустил. а вот "от 1 символа" как раз не обратил внимания. не пойдет полнотекстовик. предложение снимается.
отдельные таблицы делать не обязательно, можно ввести прямо в этой отдельный столбец с первой (второй, третьей) буквой - поиск явно уже ускорится

[Александр Аксентьев]

Макс: тут вопрос уже в архитектуре вообще, не вижу кейсов когда надо по value искать в таблице. Судя по описанию конечно.

[litvin2]

Макс: почему полнотекстовый поиск не пойдет? Он как раз дает результат по 250 000 полям за 0.005 сек.

[Макс]

litvin2: ну начнем с того, что он индексирует слова не менее определенной длинны. это настраиваемый параметр, но менее трех букв обычно не делают. Далее - это скорее аналог LIKE-а. т.е. Вы им найдете строки, в которых в тексте присутствуют определенные слова.

Answer 3

[Александр Аксентьев]

SELECT POST_ID FROM TABLE WHERE KEY='qwrety' AND VALUE='asdfghjk12345678'

Т.е. может быть where value = "4 миллиона знаков"

Что-то вы неправильно делаете значит вообще.

Зачем по value искать что-либо?

Comments

[litvin2]

Да, VALUE может быть 4 000 000 знаков (текст статьи).

Так как для каждого элемента сохраняются несколько пар KEY+VALUE, то требуется сделать выборку элементов, у которых KEY+VALUE принимают определенные значения.

Понятно объяснил?

[litvin2]

Александр Аксентьев: уточню. Искать точное совпадение с полем в 4 млн знаков – я тоже кейсов не вижу. В данный момент в поиске присутствуют только короткие поля (данные товаров, атрибуты статей и проч.)

[Александр Аксентьев]

litvin2: key value хранится обычно вместе с id объекта к которому оно прикреплено. Поэтому выборка происходит в виде select value from blah where object_id = 123 and key = "attribute".

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

[litvin2]

Александр Аксентьев: если возникает потребность искать ID по паре KEY+VALUE, как бы вы организовали такой процесс?

[Александр Аксентьев]

litvin2: читал через строку вопрос. Какая задача-то у вас стоит? Вы пытаетесь найти посты по определенному тегу или типо того?

[litvin2]

Александр Аксентьев: Да, посты
1) по конкретной KEY='xxx' AND VALUE='yyyy'
2) для фильтра по диапазону KEY='xxxx' AND VALUE>2

[Александр Аксентьев]

litvin2: Меня очень смущает размер поля в 400000 знаков. Поэтому кейс непонятен.

Если все намного проще и эти заоблачные значения никогда не будут достигнуты, то я бы взял Sphinx Search или ElasticSearch, проиндексировал бы это все дело и искал с их помощью.

[evnuh]

litvin2: опиши уже конкретно свою задачу, нифига не понятно

[litvin2]

Александр Аксентьев: поле VALUE было сделано универсальным для всевозможных данных: как атрибуты статьи, так и текст статьи. Сейчас я понимаю, что текст статьи, например, требует отдельного поля.
Но тем не менее, Битрикс используют такие "универсальные" поля для такой же задачи.

[Александр Аксентьев]

litvin2: Для статей такая универсальность не пойдет. Тем не менее вопрос все еще остается, почему вы по целому тексту статьи будете доставать post_id, я не понимаю. Откуда у вас целый текст статьи возьмется? В целом такая универсальность ломается на ваших же гипотетических данных - 250к/4кк

[litvin2]

Александр Аксентьев: спасибо, Александр, за разумные вопросы. Заставили задуматься.