Оптимизация запроса, эксперты выскажите свое мнение!

[mutineer]

Оптимизация запроса, эксперты выскажите свое мнение!

Добрый день, вожусь над написанием запроса:

таблицы:

CREATE TABLE `product_rubric` (
`rubric_id` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`rubric_left` INTEGER(11) UNSIGNED NOT NULL,
`rubric_right` INTEGER(11) UNSIGNED NOT NULL,
`rubric_level` INTEGER(11) UNSIGNED NOT NULL,
`rubric_parent` INTEGER(11) UNSIGNED NOT NULL DEFAULT '0',
`rubric_locked` INTEGER(11) UNSIGNED NOT NULL,
`rubric_name` VARCHAR(255) COLLATE utf8_general_ci NOT NULL DEFAULT '',
`rubric_translit` VARCHAR(255) COLLATE utf8_general_ci NOT NULL DEFAULT '',
`rubric_has_filters` BINARY(1) NOT NULL DEFAULT '0',
`rubric_import_id` INTEGER(11) UNSIGNED DEFAULT '0',
PRIMARY KEY (`rubric_id`),
UNIQUE KEY `rubric_translit` (`rubric_translit`),
KEY `index_fullbranch` (`rubric_left`, `rubric_right` )
)ENGINE=InnoDB
AUTO_INCREMENT=906 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

CREATE TABLE `vendor_model` (
`model_id` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`model_rubric_id` INTEGER(11) UNSIGNED NOT NULL,
`model_vendor_id` INTEGER(11) UNSIGNED NOT NULL,
`model_translit` VARCHAR(255) COLLATE utf8_general_ci NOT NULL DEFAULT '',
`model_name` VARCHAR(255) COLLATE utf8_general_ci NOT NULL DEFAULT '',
`model_status` ENUM('normal','inactive','deleted') NOT NULL DEFAULT 'inactive',
PRIMARY KEY (`model_id`),
UNIQUE KEY `model_translit` (`model_translit`),
KEY `model_rubric_id` (`model_rubric_id`),
KEY `model_vendor_id` (`model_vendor_id`),
CONSTRAINT `model_fk` FOREIGN KEY (`model_rubric_id`) REFERENCES `product_rubric` (`rubric_id`) ON UPDATE CASCADE,
CONSTRAINT `model_fk1` FOREIGN KEY (`model_vendor_id`) REFERENCES `vendor` (`vendor_id`) ON UPDATE CASCADE
)ENGINE=InnoDB
AUTO_INCREMENT=5 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

CREATE TABLE `product_rubric_relationship` (
`item_id` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`product_id` INTEGER(11) UNSIGNED NOT NULL,
`rubric_id` INTEGER(11) UNSIGNED NOT NULL,
PRIMARY KEY (`item_id`),
UNIQUE KEY `relationship` (`product_id`, `rubric_id`),
KEY `product_id` (`product_id`),
KEY `rubric_id` (`rubric_id`),
CONSTRAINT `product_rubric_relationship_fk` FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `product_rubric_relationship_fk1` FOREIGN KEY (`rubric_id`) REFERENCES `product_rubric` (`rubric_id`) ON DELETE CASCADE ON UPDATE CASCADE

)ENGINE=InnoDB
AUTO_INCREMENT=62262 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';


CREATE TABLE `product` (
`product_id` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`product_status` ENUM('normal','inactive','deleted') NOT NULL DEFAULT 'normal',
`product_company_id` INTEGER(11) UNSIGNED NOT NULL,
`product_vendor_id` INTEGER(11) UNSIGNED NOT NULL DEFAULT '0',
`product_aliace_id` INTEGER(11) UNSIGNED DEFAULT NULL,
`product_translit` VARCHAR(255) COLLATE utf8_general_ci NOT NULL DEFAULT '',
`product_name` VARCHAR(255) COLLATE utf8_general_ci NOT NULL DEFAULT '',
`product_image` VARCHAR(255) COLLATE utf8_general_ci DEFAULT '',
`product_short_descr` VARCHAR(500) COLLATE utf8_general_ci DEFAULT '',
`product_price` DECIMAL(11,2) UNSIGNED DEFAULT '0.00',
`product_dt_edit` DATETIME NOT NULL,
`product_photo_cnt` INTEGER(11) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`product_id`),
UNIQUE KEY `product_translit` (`product_translit`),
KEY `product_profile_id` (`product_company_id`),
KEY `product_aliace_id` (`product_aliace_id`),
KEY `product_profile_name` (`product_company_id`, `product_name`),
KEY `product_vendor_id` (`product_vendor_id`),
CONSTRAINT `product_fk` FOREIGN KEY (`product_company_id`) REFERENCES `company` (`company_id`) ON UPDATE CASCADE
)ENGINE=InnoDB
AUTO_INCREMENT=9285 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';


CREATE TABLE `company` (
`company_id` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`company_user_id` INTEGER(11) UNSIGNED NOT NULL,
`company_status` ENUM('normal','inactive','deleted') NOT NULL DEFAULT 'inactive',
`company_translit` VARCHAR(255) COLLATE utf8_general_ci NOT NULL DEFAULT '',
`company_type` ENUM('free','standart','premium') NOT NULL DEFAULT 'free',
`company_dt_type` DATE DEFAULT NULL,
`company_region_id` INTEGER(11) UNSIGNED NOT NULL DEFAULT '0',
`company_city_id` INTEGER(11) UNSIGNED NOT NULL DEFAULT '0',
`company_name` VARCHAR(255) COLLATE utf8_general_ci NOT NULL DEFAULT '',
`company_dt_edit` DATETIME NOT NULL,
PRIMARY KEY (`company_id`),
UNIQUE KEY `company_translit` (`company_translit`),
KEY `company_user_id` (`company_user_id`),
KEY `company_region_id` (`company_region_id`),
KEY `company_city_id` (`company_city_id`),
CONSTRAINT `company_fk` FOREIGN KEY (`company_region_id`) REFERENCES `region` (`region_id`) ON UPDATE CASCADE,
CONSTRAINT `company_fk1` FOREIGN KEY (`company_city_id`) REFERENCES `region_city` (`city_id`) ON UPDATE CASCADE,
CONSTRAINT `profile_fk` FOREIGN KEY (`company_user_id`) REFERENCES `user` (`user_id`) ON UPDATE CASCADE
)ENGINE=InnoDB
AUTO_INCREMENT=13531 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';


Запрос:
Задача. Необходимо получить ветку от заданной ноды, с количеством товаров по каждой рубрике и количеством моделей
различных производителей участвующих в рубрике. На данном этапе товары и модели производителей не пересекаются.
На вход - $node_id - нода, ветку которой надо получить; $region_id - регион пользователя

sql запрос получился такой:

SELECT
RUB.rubric_id,
RUB.rubric_name,
COUNT(DISTINCT MOL.model_id) as model_cnt,
COUNT(DISTINCT REL.product_id) as product_cnt
FROM product_rubric RUB
IGNORE INDEX (index_fullbranch)
LEFT JOIN vendor_model MOL ON MOL.model_rubric_id = RUB.rubric_id
LEFT JOIN product_rubric_relationship REL ON REL.rubric_id = RUB.rubric_id
LEFT JOIN product PRO ON PRO.product_id = REL.product_id
LEFT JOIN company COM ON COM.company_id = PRO.product_company_id AND COM.company_region_id = 11
WHERE (RUB.rubric_left >= 1692)
AND (RUB.rubric_right <= 1709)
GROUP BY RUB.rubric_id

explain:

id sel_typ table type key ref rows Extra
1 SIMPLE RUB index PRIMARY NULL 863 Using where
1 SIMPLE MOL ref rub_id dbname.RUB.rub_id 1 Using index
1 SIMPLE REL ref rub_id dbname.RUB.rub_id 1
1 SIMPLE PRO eq_ref PRIMARY dbname.REL.prod_id 1
1 SIMPLE COM eq_ref PRIMARY dbname.PRO.prod_com_id 1

Время выполнения запроса 141 мс, но при этом product_rubric_relationship и product пустые пока еще.

Если разрешить идекс index_fullbranch, то кол-во рядов в таблице RUB становиться 26, но появляется filesorting, время не меняется, а то и хуже становиться.

Планируется достаточно большой объем (десятки тысяч)

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

Спасибо

 

[LONGMAN]

Посмотри тут http://phpclub.ru/talk/showthread.php?threadid=119965

 

[mutineer]

Что-то там мне не очень нравиться, смотрел уже

-~{}~ 10.08.10 12:49:

В общем данную страницу закончил писать общее время генерации страницы: 0.28247714 sec без кеширования. Двинусь пока дальше, несколько позже наполню данными проверю на реальном объеме.

 

[prolis]

да, 26 рядов это тема для оптимизации.
Я вообще не понял:
1. "получить ветку от заданной ноды" - судя по запросу не ветку, а только родительские категории.
2. почему LEFT jouns?
3. "LEFT JOIN company COM ON ... AND COM.company_region_id = 11" - ТС что-то недоговаривает?
4. Глядя на план запроса по пустым таблицам я бы не взялся его анализировать, тем более отключать индекс.

 

[mutineer]

1. "получить ветку от заданной ноды" - судя по запросу не ветку, а только родительские категории.

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

2.
INNER JOIN мновенно вызывает почему-то filesorting, temp и прочие вещи

3. попытка поместить условие "AND COM.company_region_id = 11" в WHERE опять же вызывает filesorting

4. отключен потому что вызывает filesorting

-~{}~ 10.08.10 12:58:

таблицы уже не пустые, но разговора о десятках тысяч пока нет, попоже напишу граббер стяну откуда нибудь инфу для теста

 

[Wicked]

у меня дежавю
- опять запрос, в котором все понамешано. Статистику надо считать отдельно, один запрос для COUNT(MOL.model_id) (уже без DISTINCT), другой для COUNT(DISTINCT? REL.product_id).
- опять nested sets и неумелая работа с ними (надо: left between 1692 and 1709 group by left; KEY `index_left` (`rubric_left`))
- опять везде left join'ы без видимых причин

+ LEFT JOIN product PRO ON PRO.product_id = REL.product_id
LEFT JOIN company COM ON COM.company_id = PRO.product_company_id AND COM.company_region_id = 11 - в них я вообще не вижу смысла, ибо в такой формулировке запрос они на выборку вообще влиять не должны.

 

[mutineer]

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

По поводу неумелой работы с деревом попробую сейчас вариант с битвином.

По поводу того что надо выполнять два запроса не понял, можно объяснение? видать тут у меня пробел

-~{}~ 11.08.10 10:20:

Гениально!

Запрос исправлен на

SELECT
RUB.rubric_id,
RUB.rubric_name,
COUNT(DISTINCT MOL.model_id) as model_cnt,
COUNT(DISTINCT REL.product_id) as product_cnt
FROM product_rubric RUB
LEFT JOIN vendor_model MOL ON MOL.model_rubric_id = RUB.rubric_id
LEFT JOIN product_rubric_relationship REL ON REL.rubric_id = RUB.rubric_id
LEFT JOIN product PRO ON PRO.product_id = REL.product_id AND PRO.product_status = 'normal'
LEFT JOIN company COM ON COM.company_id = PRO.product_company_id AND COM.company_region_id = 11 AND COM.company_status = 'normal'
WHERE RUB.rubric_left BETWEEN 1692 AND 1709
GROUP BY RUB.rubric_left DESC

explain:

| id | select_type | table | rows | Extra
| 1 | SIMPLE | RUB | 9 | Using where |
| 1 | SIMPLE | MOL | 2 | Using index |
| 1 | SIMPLE | REL | 1 | |
| 1 | SIMPLE | PRO | 1 | |
| 1 | SIMPLE | COM | 1 | |

разница по графе rows уже очевидна!

 

[Wicked]

необходимо получать кол-во предложений для региона откуда пришел пользователь

и как, получается? что будет, если 11 заменить на 100500 ?

По поводу того что надо выполнять два запроса не понял, можно объяснение? видать тут у меня пробел

у тебя для каждой рубрики R есть некоторое кол-во NR связанных с ней записей в MOL, и некоторое кол-во MR записей из REL. Когда 2 этих джоина происходят в одном запросе, кол-во перебираемых записей будет NR * MR для каждой категории R, которые в итоге сгруппируются до 1 записи на каждую категорию.

Когда это разнесено на 2 запроса, это будет NR + MR, что обычно значительно меньше.

Но я бы пошел еще дальше и разделил запросы на такие:
1) SELECT RUB.rubric_id,
RUB.rubric_name
FROM product_rubric RUB
WHERE RUB.rubric_left BETWEEN 1692 AND 1709
2) SELECT COUNT(*) as model_cnt
FROM vendor_model MOL
WHERE MOL.model_rubric_id in (...)
GROUP BY MOL.model_rubric_id
3) SELECT COUNT(*) as product_cnt
FROM product_rubric_relationship REL
...JOINS...
WHERE REL.rubric_id in (...)
GROUP BY REL.rubric_id

 

[mutineer]

Автор оригинала: Wicked
и как, получается? что будет, если 11 заменить на 100500 ?

не получается

Да, ты абсолютно прав.

SELECT
RUB.rubric_id,
RUB.rubric_name
FROM product_rubric RUB
WHERE RUB.rubric_left BETWEEN 1692 AND 1709;


SELECT model_rubric_id, COUNT(model_id) as model_cnt
FROM vendor_model
WHERE model_rubric_id IN (71,73,74,75,76,77,72,78,70)
AND model_status = 'normal'
GROUP BY model_rubric_id;


SELECT REL.rubric_id, COUNT(REL.product_id) as product_ccnt
FROM product_rubric_relationship REL
INNER JOIN product PRO ON PRO.product_id = REL.product_id
INNER JOIN company COM ON COM.company_id = PRO.product_company_id
WHERE REL.rubric_id IN (71,73,74,75,76,77,72,78,70)
AND PRO.product_status = 'normal'
AND COM.company_region_id = 11 AND COM.company_status = 'normal'
GROUP BY REL.rubric_id;


Это похоже самое оптимально решение

-~{}~ 11.08.10 11:04:

Спасибо огромное за помощь.

 

[Wicked]

GROUP BY RUB.rubric_left DESC;

- больше не нужен

Ну и последний запрос лучше всего оптимизировать с помощью кэширования по паре рубрика-регион. А то даже в текущей реализации при росте кол-ва записей он будет тормозить все больше и больше.

 

[mutineer]

"последний запрос лучше всего оптимизировать с помощью кэширования по паре рубрика-регион"

не совсем понял, можно подробнее о чем идет речь, о каком кеше и что нужно сделать чтобы было правильно?

 

[Wicked]

например, так: завести табличку, где будет 3 поля: section_id, region_id, cnt - куда писать, по сути, результаты запроса:

SELECT REL.rubric_id, COM.company_region_id, COUNT(REL.product_id) as product_ccnt
FROM product_rubric_relationship REL
INNER JOIN product PRO ON PRO.product_id = REL.product_id
INNER JOIN company COM ON COM.company_id = PRO.product_company_id
AND PRO.product_status = 'normal'
AND COM.company_status = 'normal'
GROUP BY REL.rubric_id, COM.company_region_id;

-~{}~ 11.08.10 15:53:

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

 

[mutineer]

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

таблицы:

CREATE TABLE `product_rubric_property` (
`property_id` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`property_rubric_id` INTEGER(11) UNSIGNED NOT NULL,
`property_group_name` VARCHAR(255) COLLATE utf8_general_ci NOT NULL DEFAULT '',
`property_name` VARCHAR(255) COLLATE utf8_general_ci NOT NULL DEFAULT '',
`property_value_type` ENUM('int','decimal','varchar') NOT NULL DEFAULT 'int',
`property_is_filter` BINARY(1) NOT NULL DEFAULT '1',
`property_is_multiple` BINARY(1) DEFAULT '0',
`property_filter_type` ENUM('list','minmax','minmaxlist') NOT NULL,
`property_order` INTEGER(11) UNSIGNED DEFAULT '10',
`property_possible_values` VARCHAR(1000) COLLATE utf8_general_ci DEFAULT '',
PRIMARY KEY (`property_id`),
KEY `property_rubric_id` (`property_rubric_id`),
CONSTRAINT `product_rubric_property_fk` FOREIGN KEY (`property_rubric_id`) REFERENCES `product_rubric` (`rubric_id`) ON UPDATE CASCADE

)ENGINE=InnoDB
AUTO_INCREMENT=23 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';


Данные заполняются как-то так:

property_id property_rubric_id property_group_name property_name property_value_type property_is_filter property_is_multiple property_filter_type property_order property_possible_values
2 71 Общие характеристики Тип процессора int 1 0 list 1 "2 => AMD Athlon
4 => AMD Sempron
8 => AMD Turion
16 => AMD Turion X2
32 => Intel Atom
64 => Intel Celeron Dual Core
128 => Intel Celeron M
256 => Intel Core 2 Duo
512 => Intel Core 2 Extreme
1024 => Intel Core 2 Quad
2048 => Intel Core Duo
4096 => Intel Core Solo
8192 => Intel Core i7
16384 => Intel Pentium Dual-Core
32768 => Intel Pentium M
65536 => VIA C7-M"


CREATE TABLE `product_rubric_property_value` (
`value_id` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`value_property_id` INTEGER(11) UNSIGNED NOT NULL,
`value_model_id` INTEGER(11) UNSIGNED NOT NULL,
`value_value` INTEGER(11) UNSIGNED DEFAULT '0',
`value_value2` VARCHAR(255) COLLATE utf8_general_ci DEFAULT '',
`value_value3` FLOAT(9,2) UNSIGNED DEFAULT '0.00',
PRIMARY KEY (`value_id`),
KEY `value_property_id` (`value_property_id`),
KEY `value_model_id` (`value_model_id`),
CONSTRAINT `product_rubric_property_value_fk` FOREIGN KEY (`value_property_id`) REFERENCES `product_rubric_property` (`property_id`) ON UPDATE CASCADE,
CONSTRAINT `product_rubric_property_value_fk1` FOREIGN KEY (`value_model_id`) REFERENCES `vendor_model` (`model_id`) ON DELETE CASCADE ON UPDATE CASCADE

)ENGINE=InnoDB
AUTO_INCREMENT=362 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';


Если при подобной структуре таблиц

есть представление как получить

1. список Производителей, продукция которых учавствует в рубрике
2. ценовой диапазон (min, max)
3. собственно получить данные для построения фильтров
4. список моделей удовлетворяющих условиям


то абсолютно не понятно как можно получить
кол-во товаров для каждого фильтра
с учетом уже применных фильтров


Не удается толково сформулировать, пример:

Матрица (эффективная ёмкость) (млн. пикс.)
* 2 - 5 (2)
* 5 - 7 (12)
* 7 - 9 (52)
* 9 - 13 (262)
* 13 - 25 (63)

Максимальное разрешение изображения_1
* 960 - 1008
* 1008 - 1344 (1)
* 1344 - 2016 (13)
* 2016 - 4032 (395)

Как же можно это посчитать (кол-во в скобках)??
Если выбрать кол-во пикселей 7-9, то пересчитываются кол-ва товаров для
макс. расширения

* 1200 - 1210
* 1210 - 1512
* 1512 - 2016
* 2016 - 3024
* 3024 - 6048 (262)

Что же тут надо сделать чтобы проводить такие вычисления на лету??? Туда еще не дошел, но голова уже болит