Как оптимизировать SQL запрос для выбора товара по параметрам?

Question

[jest]

Имеется 3 таблицы с товаром и его параметрами:
Товары (goods): id, article, num (num - число опций, описанных для текущего артикула)
Параметры (options): id, option
Значения (cross): id, goods_id, options_id, value

Осуществляется поиск артикула товара по набору опций и их значениям, например: Вес=15кг, Цвет=Красный и Упаковка=Пластик. Необходимо выбрать только те товары у которых присутствуют все эти значения одновременно, но при этом нет больше никаких других значений (например Ширина, Длина и т.п.). Ели задается пустой поисковый запрос то система должна вернуть те товары, где нет значения ни для одного параметра.

Сейчас это работает так:

SELECT `goods`.`article` FROM `goods`
WHERE
`goods`.`num`='2' AND 
`goods`.`num`=(SELECT COUNT(*) FROM `cross` WHERE `cross`.`goods_id`=`goods`.`id` AND (
     `options_id`='1' AND `value`='15кг'
     OR
     `options_id`='4' AND `value`='Синий'
));

Где число в `goods`.`num`='2' подставляется из кода и соответствует числу выражений типа `options_id`='1' AND `value`='15кг', которые так же генерируются автомотически в нужном количестве.

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

Примеры самих таблиц

Товары:

CREATE TABLE `goods` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `article` varchar(127) NOT NULL,
  `num` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `article` (`article`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Товары' AUTO_INCREMENT=11 ;

INSERT INTO `goods` (`id`, `article`, `num`) VALUES
(1, 'Артикул 1', 1), (2, 'Артикул 2', 1),  (3, 'Артикул 3', 1), (4, 'Артикул 4', 1), (5, 'Артикул 5', 1), (6, 'Артикул 6', 2), (7, 'Артикул  7', 2), (8, 'Артикул 8', 3), (9, 'Артикул 9', 3), (10, 'Артикул 10', 4);

Параметры:

CREATE TABLE `options` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `option` varchar(127) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `option` (`option`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Опции' AUTO_INCREMENT=6 ;

INSERT INTO `options` (`id`, `option`) VALUES
(1, 'Вес'), (2, 'Длина'), (3, 'Ширина'), (4, 'Цвет'), (5, 'Упаковка');

Значения:

CREATE TABLE `cross` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `goods_id` int(11) NOT NULL,
  `options_id` int(11) NOT NULL,
  `value` varchar(127) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `goods_id` (`goods_id`,`options_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Параметры товара' AUTO_INCREMENT=20 ;

INSERT INTO `cross` (`id`, `goods_id`, `options_id`, `value`) VALUES
(1, 1, 1, '15кг'), (2, 2, 2, '120см'), (3, 3, 3, '50см'), (4, 4, 4, 'Синий'), (5, 5, 5, 'Картон'), (6, 6, 1, '15кг'), (7, 6, 4, 'Синий'), (8, 7, 1, '15кг'), (9, 7, 4, 'Красный'), (10, 8, 1, '15кг'), (11, 8, 4, 'Синий'), (12, 8, 5, 'Картон'), (13, 9, 1, '15кг'), (14, 9, 4, 'Белый'), (15, 9, 5, 'Пластик'), (16, 10, 1, '30кг'), (17, 10, 2, '120см'), (18, 10, 3, '50см'), (19, 10, 5, 'Металл');

Answer 1

[Rsa97]

SELECT `goods`.`article` 
    FROM `goods`
    JOIN `cross` AS `c1` ON `c1`.`goods_id` = `goods`.`id` 
        AND `c1`.`options_id`='1' AND `c1`.`value`='15кг'
    JOIN `cross` AS `c2` ON `c2`.`goods_id` = `goods`.`id` 
        AND `c2`.`options_id`='4' AND `c2`.`value`='Синий'

Ну и индексы, конечно же.

Comments

[jest]

К сожалению, при таком запросе выбираются не только те товары, где присутствует два параметра "15кг" и "Синий", но и товары, где помимо этих присутствуют еще параметры, например "Упаковка - Картон" с Артикулом 8, в представленном дампе.

[jest]

Хотя, добавил в конец WHERE `num`=2 и вроде дает корректный резальтат, но пока тестирую на большом объеме

[Rsa97]

jest: Добавьте в конец запроса

LEFT JOIN (
        SELECT DISTINCT `goods_id` FROM `cross` WHERE `options_id` NOT IN ('1', '4')
    ) AS `cx` ON `cx`.`goods_id` = `goods`.`id`
    WHERE `cx`.`goods_id` IS NULL

Answer 2

[Pavel Zhukau]

Сделайте INDEX для полей, по которым ищете. У нас на работе тоже были такие проблемы, запрос даже бывал 10 секунд обрабатывался. И разумеется сделайте все через 1 запрос, должен быть 1 запрос, не пользуйтесь подзапросами.
http://ruhighload.com/post/Работа+с+индексами+в+MySQL

Comments

[jest]

Спасибо за совет, изучаю

Answer 3

[Талгат Балтасов]

Может через Join сделайте, а не подзапросами. И не понял насчет num, зачем он нужен?

Comments

[jest]

Я вводил NUM чтобы выбирать явно указать количество искомых параметров, т.к. необходимо выбрать не просто товары с "15кг" и "Синий", но чтобы при этом у них небыло никаких других значений типа "Упаковка - Картон"

[Alexander]

jest: Ну у товара есть "Вес", "Цвет" и "Размер". num вы ему ставите 3. Ибо 3 параметра. Но поиск делать начали по "цвет", "размер", "упаковка". Тоже три параметра, но один отличается. Что тогда?

[jest]

Syschel: Я, не указывал это в посте, но поиск является не пользовательским, он для склада. Стоит задача найти именно товары у которых N параметров равных m1,m2...mN, при этом описанных для товара параметров не должно быть ни больше ни меньше чем N.
Задача поиска без ограничения на превосходящие параметры чуть проще.