| |
|
|
| |
SELECT
c.id card,
b.name b_name,
b.document_url b_url,
ug.id_package,
o.id,
o.title,
o.description,
o.telephone,
o.publish_site,
o.date_create,
o.city adress,
s.domain site
FROM `usage_groups` ug
JOIN `organizations` o ON ug.`id_organization` = o.id
JOIN `cards` c ON ug.`id_organization` = c.`id_organization`
JOIN links l ON o.id = l.`id_organization`
JOIN `usage` u ON l.`id_usage` = u.`id`
JOIN `businesses` b ON b.id = l.id_business
JOIN `sites` s ON o.id = s.id_organization
LEFT JOIN `businesses` b2 ON b2.`id` = b.`id_parent`
LEFT JOIN `businesses` b3 ON b3.`id` = b2.`id_parent`
LEFT JOIN `businesses` b4 ON b4.`id` = b3.`id_parent`
WHERE o.publish = 'Y'
AND 189 IN (
l.`id_business`,
b.`id_parent`,
b2.`id_parent`,
b3.`id_parent`,
b4.`id_parent`
)
GROUP BY ug.`id_organization`
ORDER BY ug.id_package DESC, o.date_create
LIMIT 100, 10
CREATE TABLE `organizations` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_creator` int(10) unsigned DEFAULT NULL,
`id_region` int(10) unsigned DEFAULT NULL,
`id_business` int(10) unsigned DEFAULT NULL,
`title` varchar(255) NOT NULL DEFAULT '',
`inn` varchar(20) DEFAULT NULL,
`kpp` varchar(20) DEFAULT NULL,
`bank` varchar(255) DEFAULT NULL,
`bank_bik` varchar(20) DEFAULT NULL,
`bank_c_account` varchar(20) DEFAULT NULL,
`bank_account` varchar(20) DEFAULT NULL,
`date_create` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`date_disable` datetime DEFAULT NULL,
`disable_reason` varchar(255) DEFAULT NULL,
`description` text,
`city` varchar(255) DEFAULT NULL,
`publish` char(1) DEFAULT NULL,
`publish_site` char(1) DEFAULT NULL,
`logo` varchar(255) DEFAULT NULL,
`telephone` varchar(60) DEFAULT NULL,
`fax` varchar(20) DEFAULT NULL,
`site` varchar(255) DEFAULT NULL,
`email` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK_organizations_region` (`id_region`),
KEY `FK_organizations_business` (`id_business`),
KEY `FK_organizations_creator` (`id_creator`),
KEY `publish` (`publish`),
KEY `date_create` (`date_create`),
CONSTRAINT `FK_organizations_business` FOREIGN KEY (`id_business`) REFERENCES `businesses` (`id`),
CONSTRAINT `FK_organizations_creator` FOREIGN KEY (`id_creator`) REFERENCES `users` (`id`),
CONSTRAINT `FK_organizations_region` FOREIGN KEY (`id_region`) REFERENCES `regions` (`id`)
) ENGINE=InnoDB DEFAULT;
CREATE TABLE `usage_groups` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_package` int(10) unsigned NOT NULL DEFAULT '0',
`id_organization` int(10) unsigned NOT NULL DEFAULT '0',
`date_create` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`note` varchar(255) DEFAULT NULL,
`card` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK_usage_group_package` (`id_package`),
KEY `FK_usage_group_organiuzation` (`id_organization`),
KEY `id_package` (`id_package`,`id_organization`),
CONSTRAINT `FK_usage_group_organization` FOREIGN KEY (`id_organization`) REFERENCES `organizations` (`id`),
CONSTRAINT `FK_usage_group_package` FOREIGN KEY (`id_package`) REFERENCES `packages` (`id`)
) ENGINE=InnoDB DEFAULT;
|
EXPLAIN:
id select_type table type key key_len ref rows Extra
1 SIMPLE o ref publish 4 const 1295 Using where; Using temporary; Using filesort
1 SIMPLE ug ref FK_usage_group_organiuzation 4 test.o.id 1
1 SIMPLE s ref FK_sites_organization 5 test.ug.id_organization 1 Using where
1 SIMPLE l ref FK_links_organizations_id 4 test.o.id 1
1 SIMPLE b eq_ref PRIMARY 4 test.l.id_business 1
1 SIMPLE b2 eq_ref PRIMARY 4 test.b.id_parent 1
1 SIMPLE b3 eq_ref PRIMARY 4 test.b2.id_parent 1
1 SIMPLE b4 eq_ref PRIMARY 4 test.b3.id_parent 1 Using where
1 SIMPLE u eq_ref PRIMARY 4 test.l.id_usage 1 Using index
1 SIMPLE c ref FK_cards_organization 5 test.ug.id_organization 7 Using where; Using index
|
Время выполнения 0.16 сек. Хочется избавиться от filesort и temporary. Или ускорить запрос другими путями.
Подскажите какие индексы не используются или используются неверно. | |
| |
|
|
| |
|
|
| |
для: Mikasto
(25.09.2007 в 13:57)
| | | Это ужасно! Mikasto у вас в выражении select указаны поля, которые отсутствуют в group by - полная ерунда. Читаем про агрегирование. | |
| |
|
|
|
