Добрый день, уважаемые форумчане.
Столкнулся с проблемой долгой сортировки, поэтому, прошу помощи подсказать путь решения этой проблемы?
Запрос такого плана:
SELECT `t1`.`fld_name`, `t1`.`fld_articul`, `t1`.`fld_brend`, `t1`.`fld_image`, SUM(t2.fld_qty) AS fld_qty, `t2`.`fld_price`, `t2`.`fld_id`
FROM `tbl_products` AS `t1`
JOIN `tbl_products_store` AS `t2` ON `t2`.`fld_code_1c` = `t1`.`fld_code_1c`
JOIN `tbl_providers` AS `t3` ON `t3`.`fld_id` = `t2`.`fld_store_id`
WHERE `t3`.`fld_group_id` =0
GROUP BY `t1`.`fld_code_1c`
ORDER BY `fld_qty` DESC
LIMIT 15
скорость выполнения ~1.1345 сек.
Без ORDER BY скорость ~0.0010 сек.
Explain запроса
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 ALL fld_code_1c NULL NULL NULL 11044 Using temporary; Using filesort
1 SIMPLE t2 ref comb comb 302 newsite.t1.fld_code_1c 2 Using index condition
1 SIMPLE t3 eq_ref PRIMARY,fld_group_id PRIMARY 4 newsite.t2.fld_store_id 1 Using where
Структура таблиц:
CREATE TABLE `tbl_products` (
`fld_id` int(11) NOT NULL AUTO_INCREMENT,
`fld_code_1c` char(100) NOT NULL,
`fld_name` varchar(255) NOT NULL,
`fld_articul` varchar(100) NOT NULL,
`fld_brend` varchar(100) NOT NULL,
`fld_analog` text,
`fld_image` varchar(255) DEFAULT NULL,
`fld_group` int(11) DEFAULT NULL,
`fld_category` int(11) NOT NULL,
PRIMARY KEY (`fld_id`),
KEY `fld_code_1c` (`fld_code_1c`),
KEY `fld_articul` (`fld_articul`,`fld_brend`)
) ENGINE=InnoDB AUTO_INCREMENT=11175 DEFAULT CHARSET=utf8 COMMENT='каталог'
CREATE TABLE `tbl_products_store` (
`fld_id` int(11) NOT NULL AUTO_INCREMENT,
`fld_code_1c` varchar(100) NOT NULL,
`fld_qty` int(11) NOT NULL DEFAULT '0',
`fld_price` decimal(10,2) NOT NULL DEFAULT '0.00',
`fld_store_id` int(11) NOT NULL,
PRIMARY KEY (`fld_id`),
KEY `comb` (`fld_code_1c`,`fld_store_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=87881 DEFAULT CHARSET=utf8
CREATE TABLE `tbl_providers` (
`fld_id` int(11) NOT NULL AUTO_INCREMENT,
`fld_priority` int(11) NOT NULL DEFAULT '1',
`fld_name` varchar(255) NOT NULL,
`fld_city` varchar(255) NOT NULL,
`fld_markup` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'Наценка для склада',
`fld_term` varchar(50) DEFAULT NULL COMMENT 'Сроки доставки',
`fld_enabled` tinyint(1) NOT NULL DEFAULT '1',
`fld_group_id` int(11) NOT NULL,
PRIMARY KEY (`fld_id`),
KEY `fld_group_id` (`fld_group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
