|
Помогите с оптимизаци запроса
#39414672
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Достался мне по наследству очень медленный запрос, выполнятся больше 4х минут. Помогите пожалуйста советом каким образом можно его оптимизировать.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
select
cpt.ptype_name, cpt.ptype_name_link,
cc.title as ctitle, cc.alias as calias,
pcc.title as pctitle,
(
select count(*)
from de_rus_catalog_products as cp
where cp.category_id = tbl.cid and cp.ptype_id = tbl.ptid
) as prod_count
from
(
select
c.id as cid,
pt.id as ptid
from (select id from de_rus_catalog_categories where is_property = 0) as c
cross join de_rus_catalog_ptypes as pt
where
(
select bd.id
from de_rus_catalog_brand_descr as bd
where bd.title = pt.ptype_name and bd.category_id = c.id
) is null
and (
select count(*)
from de_rus_catalog_products as p
where p.category_id = c.id and p.ptype_id = pt.id
)
) as tbl
left join de_rus_catalog_categories as cc on cc.id = tbl.cid
left join de_rus_catalog_categories as pcc on pcc.cleft < cc.cleft and pcc.cright > cc.cright and pcc.clevel = 1
left join de_rus_catalog_ptypes as cpt on cpt.id = tbl.ptid
order by prod_count desc
EXPLAIN
Таблицы
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
de_rus_catalog_products "CREATE TABLE `de_rus_catalog_products` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`category_id` int(11) NOT NULL DEFAULT '0',
`group_id` int(11) NOT NULL DEFAULT '0',
`ptype_id` int(11) NOT NULL DEFAULT '0',
`product_code` varchar(127) NOT NULL DEFAULT '',
`product_title` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
`alias` varchar(150) DEFAULT NULL,
`product_inf` text CHARACTER SET utf8 NOT NULL,
`product_description` text CHARACTER SET utf8 NOT NULL,
`currency` varchar(255) NOT NULL DEFAULT '',
`price_1` decimal(16,2) unsigned NOT NULL DEFAULT '0.00',
`price_2` decimal(16,2) unsigned NOT NULL DEFAULT '0.00',
`price_3` decimal(16,2) unsigned NOT NULL DEFAULT '0.00',
`price_4` decimal(16,2) unsigned NOT NULL DEFAULT '0.00',
`price_5` decimal(16,2) unsigned NOT NULL DEFAULT '0.00',
`image_middle` varchar(255) DEFAULT NULL,
`image_big` varchar(255) DEFAULT NULL,
`image_thumb` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`image_prod` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
`product_availability` varchar(10) NOT NULL DEFAULT '',
`active` tinyint(1) NOT NULL DEFAULT '0',
`rank` int(11) NOT NULL DEFAULT '0',
`leader` tinyint(1) NOT NULL DEFAULT '0',
`novelty` tinyint(1) DEFAULT '0',
`seo_title` varchar(255) NOT NULL DEFAULT '',
`description` text,
`keywords` text,
`hits` bigint(20) unsigned NOT NULL DEFAULT '0',
`owner_id` int(10) unsigned NOT NULL DEFAULT '0',
`usr_group_id` int(10) unsigned NOT NULL DEFAULT '0',
`rights` int(10) unsigned NOT NULL DEFAULT '0',
`prod_url` text CHARACTER SET cp1250 NOT NULL,
`prod_sizes` varchar(255) CHARACTER SET utf8 NOT NULL,
`prod_color` varchar(255) CHARACTER SET utf8 NOT NULL,
`shop` varchar(50) NOT NULL,
`up_ntd` tinyint(1) NOT NULL DEFAULT '0',
`set_ntd` tinyint(1) NOT NULL DEFAULT '0',
`add_ntd` tinyint(1) NOT NULL DEFAULT '0',
`sort_ntd` int(11) DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `alias` (`alias`),
KEY `category_id` (`category_id`),
KEY `id` (`id`),
KEY `ptype_id` (`ptype_id`)
) ENGINE=MyISAM AUTO_INCREMENT=42699180 DEFAULT CHARSET=cp1251"
de_rus_catalog_categories "CREATE TABLE `de_rus_catalog_categories` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`cleft` int(10) unsigned NOT NULL DEFAULT '0',
`cright` int(10) unsigned NOT NULL DEFAULT '0',
`clevel` int(10) unsigned NOT NULL DEFAULT '0',
`parent_id` int(11) NOT NULL DEFAULT '0',
`active` tinyint(1) NOT NULL DEFAULT '1',
`title` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
`alias` varchar(150) CHARACTER SET utf8 DEFAULT NULL,
`group_id` int(10) unsigned NOT NULL DEFAULT '0',
`description` text,
`img` varchar(127) NOT NULL DEFAULT '',
`page_description` text CHARACTER SET utf8,
`keywords` text CHARACTER SET utf8,
`rank` int(11) NOT NULL DEFAULT '0',
`commentable` tinyint(1) unsigned NOT NULL DEFAULT '0',
`owner_id` int(10) unsigned NOT NULL DEFAULT '0',
`usr_group_id` int(10) unsigned NOT NULL DEFAULT '0',
`rights` int(10) unsigned NOT NULL DEFAULT '0',
`seo_title` text CHARACTER SET utf8 NOT NULL,
`synonyms` text CHARACTER SET utf8 NOT NULL,
`is_property` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
UNIQUE KEY `alias` (`alias`),
KEY `clevel` (`clevel`),
KEY `is_property` (`is_property`),
KEY `cleft` (`cleft`),
KEY `cright` (`cright`)
) ENGINE=MyISAM AUTO_INCREMENT=835 DEFAULT CHARSET=cp1251"
de_rus_catalog_brand_descr "CREATE TABLE `de_rus_catalog_brand_descr` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) CHARACTER SET utf8 NOT NULL,
`description` text CHARACTER SET utf8 NOT NULL,
`category_id` varchar(10) CHARACTER SET utf8 NOT NULL DEFAULT '0',
`text_header` varchar(256) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
KEY `title` (`title`),
KEY `category_id` (`category_id`)
) ENGINE=MyISAM AUTO_INCREMENT=778 DEFAULT CHARSET=cp1251"
de_rus_catalog_ptypes "CREATE TABLE `de_rus_catalog_ptypes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ptype_name` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
`link` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT '',
`ptype_name_link` varchar(255) CHARACTER SET utf8 NOT NULL,
`set_ntd` int(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
KEY `ptype_name` (`ptype_name`)
) ENGINE=MyISAM AUTO_INCREMENT=418942 DEFAULT CHARSET=cp1251"
|
|
|
