Вот запрос который генерируется CMS на одном из блоков страницы: 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.
SELECT DISTINCT SQL_CALC_FOUND_ROWS
h.id AS id, h.rel AS pid
FROM
cms3_hierarchy h,
cms3_permissions p,
cms3_objects o
LEFT JOIN
cms3_object_content oc_509_lj ON oc_509_lj.obj_id = o.id
AND oc_509_lj.field_id = '509'
LEFT JOIN
cms3_object_content oc_516_lj ON oc_516_lj.obj_id = o.id
AND oc_516_lj.field_id = '516'
LEFT JOIN
cms3_object_content oc_517_lj ON oc_517_lj.obj_id = o.id
AND oc_517_lj.field_id = '517'
WHERE
h.type_id IN (56)
AND ((oc_509_lj.int_val = '1'
OR oc_516_lj.int_val = '1'
OR oc_517_lj.int_val = '1'))
AND h.lang_id = '1'
AND h.is_deleted = '0'
AND h.is_active = '1'
AND (p.rel_id = h.id AND p.level & 1
AND p.owner_id IN (579))
AND h.id IN (33 , 28, 24, 21, 20, 19, 18, 17, 16, 15, 14, 13, 12, 11, 10, 9, 104, 22, 121, 61, 37, 34, 58, 35, 41, 56)
AND h.obj_id = o.id
ORDER BY h.ord ASC
LIMIT 0 , 10
Как видите тут 3 лефтджойна. Их может быть больше. Они подключают много раз одну и ту же таблицу. В таблице этой может быть много данных и запрос из-за этого тормозит.
Вот структура таблицы cms3_hierarchy: 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.
CREATE TABLE `cms3_hierarchy` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`rel` int(10) unsigned NOT NULL,
`type_id` int(10) unsigned NOT NULL,
`lang_id` int(10) unsigned NOT NULL,
`domain_id` int(10) unsigned NOT NULL,
`obj_id` int(10) unsigned NOT NULL,
`ord` int(11) DEFAULT '0',
`tpl_id` int(10) unsigned DEFAULT NULL,
`alt_name` varchar(128) DEFAULT NULL,
`is_active` tinyint(1) DEFAULT NULL,
`is_deleted` tinyint(1) DEFAULT NULL,
`is_visible` tinyint(1) DEFAULT NULL,
`updatetime` int(11) DEFAULT NULL,
`is_default` tinyint(1) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `types rels_FK` (`type_id`),
KEY `Prefix from lang_id_FK` (`lang_id`),
KEY `Domain from domain_id relation_FK` (`domain_id`),
KEY `hierarchy to plain object image_FK` (`obj_id`),
KEY `Getting template data_FK` (`tpl_id`),
KEY `is_default` (`is_default`),
KEY `alt_name` (`alt_name`),
KEY `is_deleted` (`is_deleted`),
KEY `is_active` (`is_active`),
KEY `ord` (`ord`),
KEY `rel` (`rel`),
KEY `updatetime` (`updatetime`),
KEY `is_visible` (`is_visible`),
CONSTRAINT `FK_Domain from domain_id relation` FOREIGN KEY (`domain_id`) REFERENCES `cms3_domains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_Getting template data` FOREIGN KEY (`tpl_id`) REFERENCES `cms3_templates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_Prefix from lang_id` FOREIGN KEY (`lang_id`) REFERENCES `cms3_langs` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_hierarchy to plain object image` FOREIGN KEY (`obj_id`) REFERENCES `cms3_objects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_types rels` FOREIGN KEY (`type_id`) REFERENCES `cms3_hierarchy_types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=186 DEFAULT CHARSET=utf8;
cms3_objects 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
CREATE TABLE `cms3_objects` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`guid` varchar(64) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`is_locked` tinyint(1) DEFAULT NULL,
`type_id` int(10) unsigned DEFAULT NULL,
`owner_id` int(10) unsigned DEFAULT NULL,
`ord` int(10) unsigned DEFAULT '0',
`updatetime` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `Object to type relation_FK` (`type_id`),
KEY `name` (`name`),
KEY `owner_id` (`owner_id`),
KEY `is_locked` (`is_locked`),
KEY `ord` (`ord`),
KEY `guid` (`guid`),
KEY `updatetime` (`updatetime`),
CONSTRAINT `FK_Object to type relation` FOREIGN KEY (`type_id`) REFERENCES `cms3_object_types` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=805 DEFAULT CHARSET=utf8;
cms3_object_content 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.
CREATE TABLE `cms3_object_content` (
`obj_id` int(10) unsigned DEFAULT NULL,
`field_id` int(10) unsigned DEFAULT NULL,
`int_val` bigint(20) DEFAULT NULL,
`varchar_val` varchar(255) DEFAULT NULL,
`text_val` mediumtext,
`rel_val` int(10) unsigned DEFAULT NULL,
`tree_val` int(10) unsigned DEFAULT NULL,
`float_val` double DEFAULT NULL,
KEY `Content to object relation_FK` (`obj_id`),
KEY `Contents field id relation_FK` (`field_id`),
KEY `Relation value reference_FK` (`rel_val`),
KEY `content2tree_FK` (`tree_val`),
KEY `int_val` (`int_val`),
KEY `varchar_val` (`varchar_val`),
KEY `float_val` (`float_val`),
KEY `text_val` (`text_val`(8)),
KEY `K_Complex_FieldIdAndRelVal` (`field_id`,`rel_val`),
KEY `K_Complex_FieldIdAndTreeVal` (`field_id`,`tree_val`),
KEY `K_Complex_ObjIdAndFieldId` (`obj_id`,`field_id`),
CONSTRAINT `FK_Content to object relation` FOREIGN KEY (`obj_id`) REFERENCES `cms3_objects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_Contents field id relation` FOREIGN KEY (`field_id`) REFERENCES `cms3_object_fields` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_Relation value reference` FOREIGN KEY (`rel_val`) REFERENCES `cms3_objects` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_content2tree` FOREIGN KEY (`tree_val`) REFERENCES `cms3_hierarchy` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Как тут можно переписать запрос, чтобы он не тормозил?
У меня идея создать таблицу с контентом в которой все поля конкретного внутреннего типа в одной записи и упростить запрос до. 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
SELECT DISTINCT SQL_CALC_FOUND_ROWS
h.id AS id, h.rel AS pid
FROM
cms3_hierarchy h,
cms3_permissions p,
(SELECT
id
FROM
kss_type_56 kt56in
WHERE
kt56in.fld_509 = '1'
AND kt56in.fld_516 = '1'
AND kt56in.fld_517 = '1') kt56
WHERE
h.type_id IN (56) AND h.lang_id = '1'
AND h.is_deleted = '0'
AND h.is_active = '1'
AND (p.rel_id = h.id AND p.level & 1
AND p.owner_id IN (579))
AND h.id = kt56.id
ORDER BY h.ord ASC
LIMIT 0 , 10
Подозреваю, что это будет выполняться значительно быстрее. Вот как раз актуальность таблички kss_type_56 думаю поддерживать триггерами. |
