SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 02.04.2016 13:05:15

Maksim
Участник
Зарегистрирован: 02.04.2016
Сообщений: 2

Оптимизация ORDER BY

Добрый день, уважаемые форумчане.

Столкнулся с проблемой долгой сортировки, поэтому, прошу помощи подсказать путь решения этой проблемы?

Запрос такого плана:

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

Неактивен

 

#2 02.04.2016 15:26:32

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5239

Re: Оптимизация ORDER BY

у вас сортировка по вычисляемому полю, поэтому избавится от Using temporary; Using filesort не получится
разве что посмотреть не сбрасываются ли в процессе данные на жесткий

Неактивен

 

#3 02.04.2016 20:03:29

Maksim
Участник
Зарегистрирован: 02.04.2016
Сообщений: 2

Re: Оптимизация ORDER BY

Сортировка по другому полю, картины не меняет.

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 `t1`.`fld_name`
LIMIT 15

скорость выполнения ~1.3345 сек.

explain запроса
id     select_type     table     type     possible_keys     key     key_len     ref     rows     Extra    
1     SIMPLE     t1     ALL     fld_code_1c     NULL    NULL    NULL    10799     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


Добавил ключ по полю "fld_name"
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`),
 KEY `fld_name` (`fld_name`)
) ENGINE=InnoDB AUTO_INCREMENT=11175 DEFAULT CHARSET=utf8 COMMENT='каталог'


Пробовал по разным полям сортировать, время выборки превышает 1 секунду.

Отредактированно Maksim (02.04.2016 20:04:09)

Неактивен

 

#4 02.04.2016 20:28:06

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5239

Re: Оптимизация ORDER BY

тогда уж нужен составной

проверьте переменные sort_merge_passes и created_tmp_disk_tables
при необходимости увеличьте sort_buffer_size и max_heap_table_size, tmp_table_size

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson