SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 11.01.2017 12:54:03

evgeny
Гуру
Зарегистрирован: 04.05.2009
Сообщений: 335

Выборки из EAV структуры

Всем привет.
Работаю с стандартной структурой EAV.
MariaDB 5.5

CREATE TABLE `attributes` (
  `entity_id` varchar(255) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
  `attribute` varchar(32) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
  `value` varchar(32) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
  KEY `p1_p2` (`attribute`,`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;

Нужно извлекать entities имеющие перечисленные атрибуты.
Пример: color : black и size : big

Использую:

select * from attributes a
where
(a.attribute='color' and a.value='black')
or
(a.attribute='size' and a.value='big')
group by a.entity_id
having count(a.entity_id)=2;


Запрос может быть весьма тяжелым , так как отбор совпадений уже выполняется в HAVING.

Может что то упускаю ? Другая структура ? Другой запрос ?

Спасибо.

Неактивен

 

#2 11.01.2017 13:11:39

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 4946

Re: Выборки из EAV структуры

Приветствую, красивый номер сообщения - 333

сделать суррогатный цифровой `_id`, который использовать в группировке вместо `entity_id`
и попробовать какой из вариантов индексов лучше подойдет
(`_id`,`attribute`,`value`)
(`attribute`,`value`,`_id`)

запрос лучше усложнить, во from части использовать `_id`,`attribute`,`value`, а потом после группировки дополнительным join подтянуть `entity_id`.

Неактивен

 

#3 11.01.2017 13:39:59

evgeny
Гуру
Зарегистрирован: 04.05.2009
Сообщений: 335

Re: Выборки из EAV структуры

vasya написал:

Приветствую, красивый номер сообщения - 333

сделать суррогатный цифровой `_id`, который использовать в группировке вместо `entity_id`
и попробовать какой из вариантов индексов лучше подойдет
(`_id`,`attribute`,`value`)
(`attribute`,`value`,`_id`)

запрос лучше усложнить, во from части использовать `_id`,`attribute`,`value`, а потом после группировки дополнительным join подтянуть `entity_id`.

Извини не понял твою идею.
Можеш хотя бы примерно показать псевдо код.

Неактивен

 

#4 11.01.2017 14:28:42

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 4946

Re: Выборки из EAV структуры

select at.entity_id
from attributes at join
(select `_id` from attributes a
where
(a.attribute='color' and a.value='black')
or
(a.attribute='size' and a.value='big')
group by a.`_id`
having count(*)=2) t ON t.`_id`=at.`_id`;


во from-подзапросе мы делаем группировку по числовому полю `_id`, и лишь определив нужные `_id` мы для них получаем текстовые entity_id.

Таблица будет иметь вид

CREATE TABLE `attributes` (
  `_id` INT NOT NULL,
  `entity_id` varchar(255) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
  `attribute` varchar(32) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
  `value` varchar(32) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


с индексом
(`_id`,`attribute`,`value`)  -- если подойдет этот вариант, то его сделать primary
или
(`attribute`,`value`,`_id`) + (`_id`)


как лучше генерить `_id` вопрос открытый
нужно уникальное значение на основе `entity_id` с минимальным размером
реализовать можно через триггеры, чтобы не затрагивать приложение, так как `_id` нужна только для оптимизации выполнения запроса

Неактивен

 

#5 11.01.2017 14:51:31

evgeny
Гуру
Зарегистрирован: 04.05.2009
Сообщений: 335

Re: Выборки из EAV структуры

select `_id` from attributes a
where
(a.attribute='color' and a.value='black')
or
(a.attribute='size' and a.value='big')
group by a.`_id`
having count(*)=2


Эта сама по себе проблематичная часть, от нее хочеться избавится.
color: black - 400,000 записей
size: big - 10 записей
а общих всего 2.

Mysql прейдеться пройти по 400,010 записей , перенести их в temporary table, отгрупировать и только потом найти общие 2.

Неактивен

 

#6 11.01.2017 15:15:43

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 4946

Re: Выборки из EAV структуры

это он предлагал, когда группировка по текстовому не индексированному полю
в моём варианте будет пошустрее
ещё можно поставить на первое место более селективное условие, т.е.
(a.attribute='size' and a.value='big')
or
(a.attribute='color' and a.value='black')
хотя, емнип, гарантии это не дает.

Но скорее всего работать будет быстро.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson