Каталог товаров оптимизация запросов

gv0zd · 15 Дек 2009

Каталог товаров оптимизация запросов

Использовал для своих сайтиков такую структуру каталога

Каталог состоит из:
1.Таблица Категорий (примерно 500 записей) В каждой строке название, описание, вкл/выкл
2. В каждой категории есть товары. Таблица товаров (примерно 7000 записей) В каждой строке название, описание, вкл/выкл
3. У каждого товара может быть несколько модификаций. Таблица модификаций (примерно 12000 записей). В каждой строке название, цена, вкл/выкл
4. У каждой модификации может быть неогр кол-во фотографий. Таблица фотографий (примерно 30 000 записей). Фотографии модификации можно сортировать. Таким образом фотка с минимальным значением параметра сортировки будет "главной". Ее следует грузить в описании модификации

Стоит такая задача:
1. Вывести перечень товаров категории для которых onoff = 1
2. Если у товара нет модификаций с onoff = 1 этот товар выводить ненужно
3. Каждому товару сопоставить модификацию с минимальной ценой.
3. Этой модификации сопоставить фото с минимальным значением sort и onoff = 1

На маленьких таблицах, я извратнулся и написал все это одним диким

запрос

PHP:

        SELECT * FROM (
    	    SELECT 
                `n`.`title` as `n_title` 
		        , `p`.*
		        , `s`.*
	        FROM 
	            `catalog_nodes` as `n`, `catalog_products` as `p`, (
                SELECT 
                    `t1`.`subproduct_id`, `t1`.`product`, `t1`.`price` 
                FROM 
                    `catalog_subproducts` as `t1`, (
                        SELECT 
                            `product`, MIN(`t3`.`price`) as `minprice`   
                        FROM 
                            (
                                SELECT
                                    `product`, `price`
                                FROM
                                    `catalog_subproducts`
                                WHERE
                                    `onoff` = 1
                            ) as `t3`                                
                        GROUP BY 
                            `product`
                    ) as `t2`
                WHERE
                    `t1`.`product` = `t2`.`product`
                    AND `t1`.`price` = `t2`.`minprice`
                    AND `t1`.`onoff` = 1
                GROUP BY 
                    `t1`.`product`) as `s`
	        WHERE
	            `n`.`node_id` = 43 
	            AND `n`.`node_id` = `p`.`node`
	            AND `n`.`onoff` = 1 
	            AND `p`.`onoff` = 1
	            AND `p`.`product_id` = `s`.`product`
	        ORDER BY 
    	        `n_title` ASC
    	        , `p`.`title` ASC) as `z`
    	        
    	        
            LEFT OUTER JOIN (
                SELECT 
                    `t1`.`image_id`, `t1`.`subproduct`, `t1`.`s_ext`, `t1`.`b_ext` 
                FROM 
                    `catalog_images` as `t1`, (
                        SELECT 
                            MIN(`t3`.`sort`) as `minsort`   
                        FROM 
                            (
                                SELECT
                                    `subproduct`, `sort`
                                FROM
                                    `catalog_images`
                                WHERE
                                    `onoff` = 1
                            ) as `t3`                                
                        GROUP BY 
                            `subproduct`
                    ) as `t2`
                WHERE
                    `t1`.`sort` = `t2`.`minsort`
                    AND `t1`.`onoff` = 1
                GROUP BY 
                    `t1`.`subproduct`) as `i`
            ON 
                `z`.`subproduct_id` = `i`.`subproduct`

prolis · 15 Дек 2009

Re: Каталог товаров оптимизация запросов

есть вопросы:
1. Каков план и время выполнения следующего запроса:
[sql]
select c_n.*, c_p.*, c_s.* from catalog_subproducts c_s, catalog_products c_p, catalog_nodes c_n,
(
SELECT product, MIN(price) as minprice FROM catalog_subproducts
where onoff = 1
group by product
) t1
where t1.product=c_s.product
and t1.minprice=c_s.price
and c_p.product_id=c_s.product
and c_p.onoff=1
and c_n.node_id=c_p.node
and c_n.onoff=1
order by c_n.title, c_p.title
[/sql]

2. Что делать, если есть две модификации с одинаковой минимальной ценой?
3. Как часто модифицируются данные в таблицах?

gv0zd · 15 Дек 2009

Сейчас не имею доступа к тем данным, создам тестовую базу, отпишусь

-~{}~ 15.12.09 17:42:

Сократил количество строк, оказалось там много лишнего, но все равно запрос выполняется за ненормальные 16-17 секунд (повторный запрос после кэширования выполняется за 0.0003сек

prolis
Ваш запрос чуть поправил добавил для чистоты сравнения

PHP:

WHERE
                `n`.`node_id` = 43

Время выполнения 0.26-0.44 сек

Сейчас только проверю насколько правильно он все подтягивает

По поводу вопросов:
2. Вытягивать любую из них
3. nodes редактируются редко. БОльшей частью вкл/выкл раз в пару недель
products и subproducts меняются для 90% строк каждое утро при выгрузке товаров из базы. В большинстве своем это изменение цен, остатков, вкл/выкл

Фотографии потом через отдельный запрос подтягивать?

fixxxer · 15 Дек 2009

чо ты все на одно время смотришь, это оптимизация методом тыка

explain давай

gv0zd · 15 Дек 2009

fixxxer
Explain для моего "мега" запроса

PHP:

1 	PRIMARY 	<derived2> 	ALL 	NULL 	NULL 	NULL 	NULL 	2 	 
1 	PRIMARY 	<derived6> 	ALL 	NULL 	NULL 	NULL 	NULL 	4521 	 
6 	DERIVED 	<derived7> 	ALL 	NULL 	NULL 	NULL 	NULL 	4521 	Using temporary; Using filesort
6 	DERIVED 	t1 	ALL 	NULL 	NULL 	NULL 	NULL 	5314 	Using where
7 	DERIVED 	<derived8> 	ALL 	NULL 	NULL 	NULL 	NULL 	5314 	Using temporary; Using filesort
8 	DERIVED 	catalog_images 	ALL 	NULL 	NULL 	NULL 	NULL 	5314 	Using where
2 	DERIVED 	n 	const 	PRIMARY 	PRIMARY 	4 	  	1 	Using temporary; Using filesort
2 	DERIVED 	<derived3> 	ALL 	NULL 	NULL 	NULL 	NULL 	4943 	 
2 	DERIVED 	p 	eq_ref 	PRIMARY,node 	PRIMARY 	4 	s.product 	1 	Using where
3 	DERIVED 	<derived4> 	ALL 	NULL 	NULL 	NULL 	NULL 	4943 	Using temporary; Using filesort
3 	DERIVED 	t1 	ref 	product 	product 	5 	t2.product 	11 	Using where
4 	DERIVED 	<derived5> 	ALL 	NULL 	NULL 	NULL 	NULL 	5482 	Using temporary; Using filesort
5 	DERIVED 	catalog_subproducts 	ALL 	NULL 	NULL 	NULL 	NULL 	5482 	Using where

Для запроса, что предложил prolis

PHP:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	PRIMARY 	n 	const 	PRIMARY 	PRIMARY 	4 	const 	1 	Using temporary; Using filesort
1 	PRIMARY 	<derived2> 	ALL 	NULL 	NULL 	NULL 	NULL 	4943 	 
1 	PRIMARY 	s 	ref 	product 	product 	5 	t1.product 	11 	Using where
1 	PRIMARY 	p 	eq_ref 	PRIMARY,node 	PRIMARY 	4 	s.product 	1 	Using where
2 	DERIVED 	catalog_subproducts 	ALL 	NULL 	NULL 	NULL 	NULL 	5482 	Using where; Using temporary; Using filesort

ЗЫ Заранее спасибо откликнувшимся

fixxxer · 16 Дек 2009

ой как все запущено...

такое сходу и не разгребешь, это целое дело

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

gv0zd · 16 Дек 2009

fixxxer
Можно на примере второго запроса, что prolis предложил, пояснить что не так?

prolis · 17 Дек 2009

1. Оба запроса к catalog_subproducts не используют индексов
2. Для сортировок используются дополнительные прогоны
- покажи структуру всех таблиц

gv0zd · 17 Дек 2009

PHP:

-- --------------------------------------------------------

-- 
-- Структура таблицы `catalog_nodes`
-- 

CREATE TABLE `catalog_nodes` (
  `node_id` int(10) unsigned NOT NULL auto_increment,
  `parent` int(11) default NULL,
  `l` int(10) unsigned NOT NULL default '0',
  `r` int(10) unsigned NOT NULL default '0',
  `level` int(10) unsigned NOT NULL default '0',
  `onoff` int(1) default '1',
  `rewrite` varchar(255) default NULL,
  `rewrite_path` text,
  `title` varchar(255) default NULL,
  `content` text,
  PRIMARY KEY  (`node_id`),
  KEY `c_left` (`l`,`r`,`level`)
) ENGINE=MyISAM;

-- --------------------------------------------------------

-- 
-- Структура таблицы `catalog_products`
-- 

CREATE TABLE `catalog_products` (
  `product_id` int(11) unsigned NOT NULL auto_increment,
  `node` int(11) default '0',
  `rewrite` varchar(255) default NULL,
  `title` varchar(255) default NULL,
  `content` text,
  PRIMARY KEY  (`product_id`)
) ENGINE=MyISAM;

-- --------------------------------------------------------

-- 
-- Структура таблицы `catalog_subproducts`
-- 

CREATE TABLE `catalog_subproducts` (
  `subproduct_id` int(11) unsigned NOT NULL auto_increment,
  `product` int(11) default '0',
  `title` varchar(255) default NULL,
  `content` text,
  `price` int(11) default '0',
  `onoff` int(1) default '1',
  PRIMARY KEY  (`subproduct_id`)
) ENGINE=MyISAM;

-- --------------------------------------------------------

-- 
-- Структура таблицы `catalog_images`
-- 

CREATE TABLE `catalog_images` (
  `image_id` int(10) unsigned NOT NULL auto_increment,
  `subproduct` int(11) default NULL,
  `s_ext` varchar(4) default NULL,
  `b_ext` varchar(4) default NULL,
  `sort` int(11) default '0',
  `onoff` int(1) default '1',
  PRIMARY KEY  (`image_id`),
  KEY `product` (`subproduct`)
) ENGINE=MyISAM;

prolis · 17 Дек 2009

Автор оригинала: gv0zd
[sql]
-- --------------------------------------------------------
--
-- Структура таблицы `catalog_subproducts`
--

CREATE TABLE `catalog_subproducts` (
`subproduct_id` int(11) unsigned NOT NULL auto_increment,
`product` int(11) default '0',
`title` varchar(255) default NULL,
`content` text,
`price` int(11) default '0',
`onoff` int(1) default '1',
PRIMARY KEY (`subproduct_id`)
) ENGINE=MyISAM;
[/sql]

- тут по полю product индекс просится
и по обоим title при желании.
Создай и покажи запрос и эксплайн

gv0zd · 17 Дек 2009

Добавил индекс на product
Eplain по мичуринскому запросу

PHP:

1  	PRIMARY  	<derived2>  	ALL  	NULL  	NULL  	NULL  	NULL  	29  	 
1 	PRIMARY 	<derived6> 	ALL 	NULL 	NULL 	NULL 	NULL 	4521 	 
6 	DERIVED 	<derived7> 	ALL 	NULL 	NULL 	NULL 	NULL 	4521 	Using temporary; Using filesort
6 	DERIVED 	t1 	ALL 	NULL 	NULL 	NULL 	NULL 	5314 	Using where
7 	DERIVED 	<derived8> 	ALL 	NULL 	NULL 	NULL 	NULL 	5314 	Using temporary; Using filesort
8 	DERIVED 	alltels_catalog_images 	ALL 	NULL 	NULL 	NULL 	NULL 	5314 	Using where
2 	DERIVED 	n 	const 	PRIMARY 	PRIMARY 	4 	  	1 	Using temporary; Using filesort
2 	DERIVED 	<derived3> 	ALL 	NULL 	NULL 	NULL 	NULL 	4943 	 
2 	DERIVED 	p 	eq_ref 	PRIMARY,node 	PRIMARY 	4 	s.product 	1 	Using where
3 	DERIVED 	<derived4> 	ALL 	NULL 	NULL 	NULL 	NULL 	4943 	Using temporary; Using filesort
3 	DERIVED 	t1 	ref 	product,product_2 	product 	5 	t2.product 	1 	Using where
4 	DERIVED 	<derived5> 	ALL 	NULL 	NULL 	NULL 	NULL 	5482 	Using temporary; Using filesort
5 	DERIVED 	alltels_catalog_subproducts 	ALL 	NULL 	NULL 	NULL 	NULL 	5482 	Using where

Время получения запроса увеличилось секунды на 2 почему-то

По вашему запросу

PHP:

1  	PRIMARY  	n  	const  	PRIMARY  	PRIMARY  	4  	const  	1  	Using temporary; Using filesort
1 	PRIMARY 	<derived2> 	ALL 	NULL 	NULL 	NULL 	NULL 	4943 	 
1 	PRIMARY 	s 	ref 	product,product_2 	product 	5 	t1.product 	1 	Using where
1 	PRIMARY 	p 	eq_ref 	PRIMARY,node 	PRIMARY 	4 	alltels.s.product 	1 	Using where
2 	DERIVED 	alltels_catalog_subproducts 	ALL 	NULL 	NULL 	NULL 	NULL 	5482 	Using where; Using temporary; Using filesort

Время выполнения запроса сократилось до 0,18-0,20

Индексы на title ставить во все таблицы? в узлы, товары и модификации?

prolis · 17 Дек 2009

Автор оригинала: gv0zd
Время выполнения запроса сократилось до 0,18-0,20
Индексы на title ставить во все таблицы? в узлы, товары и модификации?

- странно, что время сократилось в два раза, план-то не поменялся. Приводи запрос перед публикацией эксплайна, а то моя телепат мода остывает к вечеру.
-индексы на титлы оставить на когда-нибудь потом, а для ответа на вопрос, куда их ставить (узлы, товары и модификации) научись читать ехсплайны (google mysql explain), там все подсказывается.
-далее пока прикрути к запросу вывод фоток, аналогично используемому способу, только с LEFT JOIN

gv0zd · 17 Дек 2009

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

Ок спасибо за помощь, буду разбираться
Удачи

Каталог товаров оптимизация запросов

gv0zd

Новичок

prolis

Новичок

gv0zd

Новичок

fixxxer

К.О.

gv0zd

Новичок

fixxxer

К.О.

gv0zd

Новичок

prolis

Новичок

gv0zd

Новичок

prolis

Новичок

gv0zd

Новичок

prolis

Новичок

gv0zd

Новичок