Будут ли просадки по производительности при реализации фильтров интернет магазина через sql intersect?

Question

[alestro]

Имею две таблицы: товары и фильтры, которые связаны через сводную таблицу filters_products

CREATE TABLE `filters_products` (
	`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
	`filter_id` INT(11) UNSIGNED NULL DEFAULT NULL,
	`product_id` INT(11) UNSIGNED NULL DEFAULT NULL,
	`filter_value` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	PRIMARY KEY (`id`) USING BTREE,
	UNIQUE INDEX `filter_id_product_id` (`filter_id`, `product_id`) USING BTREE,
	INDEX `FK__products` (`product_id`) USING BTREE,
	INDEX `filter_value` (`filter_value`) USING BTREE,
	CONSTRAINT `FK__filters` FOREIGN KEY (`filter_id`) REFERENCES `filters` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT `FK__products` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB

В настоящий момент фильтр реализован через запрос вида:

SELECT * FROM products WHERE id IN (
    SELECT product_id FROM filters_products WHERE filter_id = 1 AND filter_value BETWEEN 1000 AND 4000
INTERSECT
    (SELECT product_id FROM filters_products WHERE (filter_id = 3 AND (filter_value = 'Intel® Core™ i5 11400F' OR filter_value = 'AMD Ryzen 5 5600G')))
INTERSECT 
    (SELECT product_id FROM filters_products WHERE filter_id = 2 AND filter_value = 6)
);

Насколько удачное решение, будут ли просадки по производительности при большом количестве фильтров?

Comments

[Михаил]

Будут, почему intersect, а не join?

[alestro]

Михаил, через join товаров и и сводной таблицы?

[Михаил]

Что-то типа такого не даст того же результата? Мне кажется должно в перспективе отрабатывать быстрее

SELECT p.*
FROM products p
INNER JOIN filters_products fp1 ON p.id = fp1.product_id
INNER JOIN filters_products fp2 ON p.id = fp2.product_id
INNER JOIN filters_products fp3 ON p.id = fp3.product_id
WHERE fp1.filter_id = 1 AND fp1.filter_value BETWEEN 1000 AND 4000
  AND fp2.filter_id = 3 AND fp2.filter_value IN ('Intel® Core™ i5 11400F', 'AMD Ryzen 5 5600G')
  AND fp3.filter_id = 2 AND fp3.filter_value = 6;

[alestro]

Михаил, нет, результат будет разный. Такой запрос вернет товары, которые подпадают хотя бы под одно условие.

Answer 1

[mayton2019]

Данный фильтр - слишком избыточен. Его можно переписать через

SELECT * FROM products WHERE id IN (
    SELECT product_id FROM filters_products WHERE 
        (filter_id = 1 AND filter_value BETWEEN 1000 AND 4000)
      OR
        (filter_id = 3 AND (filter_value = 'Intel® Core™ i5 11400F' OR filter_value = 'AMD Ryzen 5 5600G'))
      OR
        (filter_id = 2 AND filter_value = 6)
);

В оригинальном запросе стоял INSERSECT и по идее надо было заменять предикаты на AND.
Но я поставил OR поскольку мне кажется что автор тоже ошибается и INSERSECT - это
слишком строгий фильтр и пересечение с ключами 1-2-3 даст пустоту. И скорее всего
там надо объединение а не пересечение.

Просадки-шмосадки... Будут или нет. Никто не знает. Смотрим execution план в двух вариантах. И сравниваем.

Еще раз я призываю всех SQL писателей - указывать целевую dbms.

Comments

[alestro]

Ваш вариант и мой выдает два разных результата, если изменить предикат на AND, то выборка вовсе будет пуста. Изначально фильтр был реализован через объединение вида

SELECT DISTINCT `products`.`id`, `products`.`name`, `products`.`price`, `products`.`attributes`, `products`.`description`, `products`.`created_at` AS `createdAt`,
products.category_id AS `categoryId`, `products`.`brand_id` AS `brandId`
FROM `products` 
INNER JOIN `filters_products`
    ON `products`.`id` = `filters_products`.`product_id`  
WHERE ((`filters_products`.`filter_id` = 3 AND (filter_value = 'Intel® Core™ i5 11400F' OR filter_value = 'AMD Ryzen 5 5600G') )OR (`filters_products`.`filter_id` = 1 AND (filter_value BETWEEN 1000 AND 4000  ) ) )

Но он не учитывал пересечение фильтров, он возвращал все товары, которые подпадают хотя бы под один фильтр.
Мой запрос вернет:

{
	"table": "products",
	"rows":
	[
		{
			"id": 1,
			"name": "Lenovo Legion T5 Gen 6 26IOB6 90RT00RYRS",
			"category_id": 1,
			"brand_id": 1,
			"price": 106000,
			"attributes": null,
			"description": null,
			"short_description": null,
			"created_at": "2023-02-08 00:00:11"
		},
		{
			"id": 3,
			"name": "Системный блок HP Omen GT12-1049ur черный (5D427EA)",
			"category_id": 1,
			"brand_id": 2,
			"price": 120990,
			"attributes": null,
			"description": null,
			"short_description": null,
			"created_at": "2023-02-08 00:00:11"
		}
	]
}

В то время, как ваш запрос и запрос через объединение вернет:

{
	"table": "products",
	"rows":
	[
		{
			"id": 1,
			"name": "Lenovo Legion T5 Gen 6 26IOB6 90RT00RYRS",
			"category_id": 1,
			"brand_id": 1,
			"price": 106000,
			"attributes": null,
			"description": null,
			"short_description": null,
			"created_at": "2023-02-08 00:00:11"
		},
		{
			"id": 2,
			"name": "Системный блок HP Pavilion Gaming TG01-2018ur черный (42V09EA)",
			"category_id": 1,
			"brand_id": 2,
			"price": 87990,
			"attributes": null,
			"description": null,
			"short_description": null,
			"created_at": "2023-02-08 00:00:11"
		},
		{
			"id": 3,
			"name": "Системный блок HP Omen GT12-1049ur черный (5D427EA)",
			"category_id": 1,
			"brand_id": 2,
			"price": 120990,
			"attributes": null,
			"description": null,
			"short_description": null,
			"created_at": "2023-02-08 00:00:11"
		}
	]
}

Сводная таблица:

{
	"table": "filters_products",
	"rows":
	[
		{
			"filter_id": 1,
			"product_id": 1,
			"filter_value": "2600"
		},
		{
			"filter_id": 1,
			"product_id": 3,
			"filter_value": "2600"
		},
		{
			"filter_id": 1,
			"product_id": 2,
			"filter_value": "3900"
		},
		{
			"filter_id": 2,
			"product_id": 1,
			"filter_value": "6"
		},
		{
			"filter_id": 2,
			"product_id": 2,
			"filter_value": "6"
		},
		{
			"filter_id": 2,
			"product_id": 3,
			"filter_value": "6"
		},
		{
			"filter_id": 3,
			"product_id": 2,
			"filter_value": "AMD Ryzen 5 5600G"
		},
		{
			"filter_id": 3,
			"product_id": 1,
			"filter_value": "Intel® Core™ i5 11400F"
		},
		{
			"filter_id": 3,
			"product_id": 3,
			"filter_value": "Intel® Core™ i5 11400F"
		}
	]
}

[mayton2019]

alestro, ну направление верное?

[alestro]

mayton2019, я обновил комментарий, добавил результат и содержание сводной таблицы.

[alestro]

mayton2019, относительно направления - на данный момент я не придумал варианта без использования intersect. Без использования пересечения фильтр возвращает все товары, которые подпадают хотя бы под одно условие, но не под их группу.

[mayton2019]

alestro, мне дальше не интересно извини. Колбаса из UNION/INTERSECT в твоем варианте полностью не нужна. Она заменяется на WHERE с комбинациями нужных предикатов.

А union чаще всего используется когда сами таблицы разные и надо их сводить к одной.

[alestro]

mayton2019, Предиката два в первом случае (or) результат не тот, что нужен, в случае объединения фильтров через AND выборка будет пустая.

[mayton2019]

alestro, слушай меня как мастера кунг-фу. Я говорю - тебе множественные операции не нужны. Твоя задача решается через WHERE. Копать комбинации AND/OR мне лень. Это ты сам сделаешь.

Вобщем советы мастера не нуждаются в доказательствах.

[Максим Тимофеев]

Fatality