Производительность решения SQL like vs join?

Question

[GeKskill]

Придумалось два варианта решения задачи и не могу определиться какой использовать.
Суть задачи - находить записи которые содержат определенные опции.

Первый вариант, хранить все опции строкой в одном столбце :
Table - objects
id | title | options
------------------------------------------------
1 | text | option=x,option2=y,option3=z
1 | text | option=b,option2=n,option3=z

В этом случае запрос будет выглядеть так:

SELECT * FROM table 
WHERE options 
LIKE %option=x% 
AND options LIKE %option2=y% 
AND options LIKE %option3=z%

Второй вариант, перенести опции в отдельную таблицу:
Table - options
id | object_id | option_name | option_value
1 | 1 | option | x
2 | 1 | option2 | y
3 | 1 | option3 | z
Для этого варианта запрос будет выглядеть так:

SELECT * FROM `objects` 
       INNER JOIN options as ot1 ON ( objects.id = options.object_id )
       INNER JOIN options as ot2 ON ( objects.id = ot2.object_id )
       INNER JOIN options as ot3 ON ( objects.id = ot3.object_id )
WHERE 1=1 AND (
 ( ot1.option_name = 'option' AND ot1.option_value = 'x' )
 AND ( ot2.option_name = 'option2' AND ot2.option_value = 'y' )
 AND ( ot3.option_name = 'option3' AND ot3.option_value = 'z'  )
 )
GROUP BY objects.id;

Answer 1

[rPman]

Ты забыл третий вариант, самый быстрый и наиболее предпочтительный если общее количество опций не велико (сотни, например у mysql лимит 1024 колонок максимум) - каждая опция это своя колонка, пустое (null) значение будет значить отсутствие опции у записи.

Недостаток подхода - если у объекта может быть несколько опций с одним именем, то такой подход не работает (но судя по всему это не твой случай).

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

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

p.s. если говорить про твои варианты:
* для 1 используй json сериализацию (опция=значение) в mysql для работы с такими данными есть соответствующие методы
* для 2 постарайся вместо текстовых наименований опций использовать числовые идентификаторы, заведя соответствующий классификатор либо в базе в отдельной таблице либо в виде констант в коде.

p.p.s. Ну и еще вариант, если тип значений опций - boolean (либо ограничен небольшим количеством значений, например цвет светофора 4 - выключен, красный, желтый, зеленый) то так же заводи для этих значений числовой эквивалент. В этом случае у тебя появляется еще бонус, упаковывать битовые значения (где количество вариантов 2^x и x это количество бит) и хранить в целочисленном поле сразу несколько (правда не все базы позволяют индексировать операции с битами).

Comments

[GeKskill]

Большое спасибо! Таблица с колонками опций, действительно не подумал. Но это ведь придется перед каждым сохранением записи делать alter table, чтобы не попасть на column undefined. Так как опции добавляются со временем, и в будущем, вероятно, доставит проблем)

[Ипатьев]

GeKskill, разумеется придется. Именно поэтому "таблица с колонками" - это полная чушь.

[rPman]

GeKskill,

Так как опции добавляются со временем, и в будущем, вероятно, доставит проблем)

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

Проблемы из-за использования сериализации в текстовое поле - база данных не будет следить за целостностью системы, ей будет пофиг, не ошибся ли ты с названием опции, не сделал ли дублирование значений опции с одним именем и ошибки в синтаксисе сериализации (а они будут если во время жизни в базу будут часто залезать ручками)

Answer 2

[Ипатьев]

Это у вас, похоже, не опции, а свойства в неограниченном количестве.
Если бы это были опции, то можно было бы или добавить просто колонки в основную таблицу, либо сделать одну колонку с битовой маской

А свойства хранить либо в EAV, который у вас второй вариант, либо в JSON поле.
И искать по ним отдельным не вот этим вот, а отдельным сервисом типа Эластика.

В любом случае, первый вариант, понятное дело, это вообще не вариант