ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Тяжелый запрос с Using temporary, Using filesort из-за ORDER
15 сообщений из 15, страница 1 из 1
Тяжелый запрос с Using temporary, Using filesort из-за ORDER
    #39224690
supermike
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
supermike
Гость
Прошу помочь ускорить запрос или подсказать как правильно его переделать(или переделать структуру).

Имеется вот такой запрос:
Код: plaintext
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.
SELECT
SQL_NO_CACHE
	`content`.`pagetitle`,`content`.`id`,

	`a16`.`value` AS `a16`,
	`a60`.`value` AS `a60`,
	`a87`.`value` AS `a87`,
	`a89`.`value` AS `a89`,
	`a59`.`value` AS `a59`,
	`a94`.`value` AS `a94`,
	`a15`.`value` AS `a15`,
	`a6`.`value` AS `a6`,
	`a86`.`value` AS `a86`,
	`a19`.`value` AS `a19`
FROM
	`modx_site_content` AS `content`
	LEFT JOIN `product_tmplvar_contentvalues` AS `a16` ON `a16`.`contentid` = `content`.`id` AND `a16`.`tmplvarid` = 16
	LEFT JOIN `product_tmplvar_contentvalues` AS `a60` ON `a60`.`contentid` = `content`.`id` AND `a60`.`tmplvarid` = 60
	LEFT JOIN `product_tmplvar_contentvalues` AS `a87` ON `a87`.`contentid` = `content`.`id` AND `a87`.`tmplvarid` = 87
	<...>
	LEFT JOIN `product_tmplvar_contentvalues` AS `a19` ON `a19`.`contentid` = `content`.`id` AND `a19`.`tmplvarid` = 19
WHERE
	1 = 1
	AND `content`.`parent` = 66860
	AND `a83`.`value` = "42160" 

ORDER BY menuindex
LIMIT 100

Если в выборку попадает более 200к позиций(учитывая условия where), запрос выполняется от 5 до 60 секунд. Желательно не более 2-3 секунд.

Если указать индекс:
Код: plaintext
1.
2.
`modx_site_content` AS `content`
USE INDEX (`parent-menuindex`)
LEFT JOIN `product_tmplvar_contentvalues` AS `a16` ON `a16`.`contentid` = `content`.`id` AND `a16`.`tmplvarid` = 16
- конкретно в этом запросе проблема решается: Using temporary, Using filesort пропадают и вместо 7 сек этот запрос выполнится за 0.01с. Но это помогает только в ~50% случаев, в остальные 50% случаев этот индекс наоборот тормозит, либо никак не влияет. Взаимосвязь найти не смог. Если убрать сортировку, также пропадает using filesort, using temporary и запрос выполняется 0.01с.

ORDER заставляет БД лопатить все записи. Если добавить еще условий, напр. `a16`.`value` = "21334" и в результате это ограничит количество позиций до каких-нибудь 10к - запрос выполнится быстро. В самой таблице может быть несколько миллионов записей, на скорость влияет именно количество, попадающее в выборку(есть исключения, но общая картина такова). Если в выборку попадает несколько десятков тысяч записей(много) - часто помогает USE INDEX - но не всегда.

Пробовал выделять оперативку для временных таблиц, key_buffer_size и прочие параметры - практически не влияет на производительность. Обновление с MySQL 5.1 до 5.6 ускорило запрос, но ненамного. Вернулся на 5.1.
Пытался решить эту проблему уже несколько раз, многое перепробовал и честно говоря уже запутался, не хватает опыта и свежего взгляда.

Что делает запрос:
Здесь есть 2 таблицы: "предметы" и "характеристики" к ним. Соответственно идет выборка предметов по условиям характеристик.
Может быть я неправильно организовал структуру хранения данных?

Буду рад любым соображениям.

EXPLAIN без USE INDEX
 
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
    id  select_type  table    type    possible_keys                                  key                  key_len  ref                               rows  Extra                                         
------  -----------  -------  ------  ---------------------------------------------  -------------------  -------  ------------------------------  ------  ----------------------------------------------
     1  SIMPLE       a83      ref     contentid-tmplvarid,tmplvarid,value-tmplvarid  value-tmplvarid      12       const,const                      22337  Using where; Using temporary; Using filesort  
     1  SIMPLE       content  eq_ref  PRIMARY,parent-menuindex                       PRIMARY              4        fluid-line.a83.contentid             1  Using where                                   
     1  SIMPLE       a81      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1  Using index                                   
     1  SIMPLE       a88      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1  Using index                                   
     1  SIMPLE       a92      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.content.id,const          1  Using index                                   
     1  SIMPLE       a93      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.content.id,const          1  Using index                                   
     1  SIMPLE       a65      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1  Using index                                   
     1  SIMPLE       a95      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1  Using index                                   
     1  SIMPLE       a86      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1                                                
     1  SIMPLE       a19      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1                                                
     1  SIMPLE       a16      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1                                                
     1  SIMPLE       a60      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1                                                
     1  SIMPLE       a87      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1                                                
     1  SIMPLE       a89      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1                                                
     1  SIMPLE       a59      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.content.id,const          1                                                
     1  SIMPLE       a94      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.content.id,const          1                                                
     1  SIMPLE       a15      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1                                                
     1  SIMPLE       a6       ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1


EXPLAIN без USE INDEX, но без ORDER
 
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
    id  select_type  table    type    possible_keys                                  key                  key_len  ref                               rows  Extra                                         
------  -----------  -------  ------  ---------------------------------------------  -------------------  -------  ------------------------------  ------  -------------
     1  SIMPLE       a83      ref     contentid-tmplvarid,tmplvarid,value-tmplvarid  value-tmplvarid      12       const,const                      22337  Using where  
     1  SIMPLE       content  eq_ref  PRIMARY,parent-menuindex                       PRIMARY              4        fluid-line.a83.contentid             1  Using where  
     1  SIMPLE       a81      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1  Using index  
     1  SIMPLE       a88      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1  Using index  
     1  SIMPLE       a92      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.content.id,const          1  Using index  
     1  SIMPLE       a93      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.content.id,const          1  Using index  
     1  SIMPLE       a65      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1  Using index  
     1  SIMPLE       a95      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1  Using index  
     1  SIMPLE       a86      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1               
     1  SIMPLE       a19      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1               
     1  SIMPLE       a16      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1               
     1  SIMPLE       a60      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1               
     1  SIMPLE       a87      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1               
     1  SIMPLE       a89      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1               
     1  SIMPLE       a59      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.content.id,const          1               
     1  SIMPLE       a94      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.content.id,const          1               
     1  SIMPLE       a15      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1               
     1  SIMPLE       a6       ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1


EXPLAIN с USE INDEX
 
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
    id  select_type  table    type    possible_keys                                  key                  key_len  ref                               rows  Extra        
------  -----------  -------  ------  ---------------------------------------------  -------------------  -------  ------------------------------  ------  -------------
     1  SIMPLE       content  ref     parent-menuindex                               parent-menuindex     4        const                           224253  Using where  
     1  SIMPLE       a83      ref     contentid-tmplvarid,tmplvarid,value-tmplvarid  contentid-tmplvarid  8        fluid-line.content.id,const          1  Using where  
     1  SIMPLE       a81      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1  Using index  
     1  SIMPLE       a88      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1  Using index  
     1  SIMPLE       a92      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.content.id,const          1  Using index  
     1  SIMPLE       a93      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.content.id,const          1  Using index  
     1  SIMPLE       a65      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1  Using index  
     1  SIMPLE       a95      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1  Using index  
     1  SIMPLE       a86      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1               
     1  SIMPLE       a19      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1               
     1  SIMPLE       a16      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.content.id,const          1               
     1  SIMPLE       a60      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1               
     1  SIMPLE       a87      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1               
     1  SIMPLE       a89      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1               
     1  SIMPLE       a59      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.content.id,const          1               
     1  SIMPLE       a94      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1               
     1  SIMPLE       a15      ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1               
     1  SIMPLE       a6       ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  8        fluid-line.a83.contentid,const       1


ТАБЛИЦЫ
 
Код: plaintext
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 `modx_site_content` (
  `id` int(10) NOT NULL auto_increment,
  `pagetitle` varchar(255) collate utf8_unicode_ci NOT NULL default '',
  `parent` int(10) NOT NULL default '0',
  `isfolder` int(1) NOT NULL default '0',
  `menuindex` int(10) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `aliasidx` (`alias`),
  KEY `typeidx` (`type`),
  KEY `pub_date` (`pub_date`),
  KEY `unpub_date` (`unpub_date`),
  KEY `isfolder` (`isfolder`),
  KEY `parent-menuindex` (`parent`,`menuindex`),
  KEY `pagetitle` (`pagetitle`(15))
) ENGINE=MyISAM AUTO_INCREMENT=6609027 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Contains the site document tree.'

CREATE TABLE `product_tmplvar_contentvalues` (
  `id` int(11) NOT NULL auto_increment,
  `contentid` int(11) NOT NULL,
  `tmplvarid` int(11) NOT NULL,
  `value` int NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `contentid-tmplvarid` (`contentid`,`tmplvarid`),
  KEY `tmplvarid` (`tmplvarid`),
  KEY `value-tmplvarid` (`value`,`tmplvarid`)
) ENGINE=MyISAM AUTO_INCREMENT=54304242 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Здесь хранятся значения свойств товара: цвет, кол-во и т.п.'
...
Рейтинг: 0 / 0
26.04.2016, 12:59
| Ответить | Цитировать | Написать  
Тяжелый запрос с Using temporary, Using filesort из-за ORDER
    #39224708
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
supermike
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
FROM
	`modx_site_content` AS `content`
	LEFT JOIN `product_tmplvar_contentvalues` AS `a16` ON `a16`.`contentid` = `content`.`id` AND `a16`.`tmplvarid` = 16
	LEFT JOIN `product_tmplvar_contentvalues` AS `a60` ON `a60`.`contentid` = `content`.`id` AND `a60`.`tmplvarid` = 60
	LEFT JOIN `product_tmplvar_contentvalues` AS `a87` ON `a87`.`contentid` = `content`.`id` AND `a87`.`tmplvarid` = 87
	<...>
	LEFT JOIN `product_tmplvar_contentvalues` AS `a19` ON `a19`.`contentid` = `content`.`id` AND `a19`.`tmplvarid` = 19
WHERE
	1 = 1
	AND `content`.`parent` = 66860
	AND `a83`.`value` = "42160"


Надеюсь, не надо объяснять, что
Код: sql
1.
AND `a83`.`value` = "42160"

превращает соотв. LEFT JOIN в INNER JOIN?
...
Рейтинг: 0 / 0
26.04.2016, 13:12
| Ответить | Цитировать | Написать  
Тяжелый запрос с Using temporary, Using filesort из-за ORDER
    #39224717
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
supermikeПрошу помочь ускорить запрос или подсказать как правильно его переделать(или переделать структуру).

Имеется вот такой запрос:
Код: sql
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.
SELECT
SQL_NO_CACHE
	`content`.`pagetitle`,`content`.`id`,

	`a16`.`value` AS `a16`,
	`a60`.`value` AS `a60`,
	`a87`.`value` AS `a87`,
	`a89`.`value` AS `a89`,
	`a59`.`value` AS `a59`,
	`a94`.`value` AS `a94`,
	`a15`.`value` AS `a15`,
	`a6`.`value` AS `a6`,
	`a86`.`value` AS `a86`,
	`a19`.`value` AS `a19`
FROM
	`modx_site_content` AS `content`
	LEFT JOIN `product_tmplvar_contentvalues` AS `a16` ON `a16`.`contentid` = `content`.`id` AND `a16`.`tmplvarid` = 16
	LEFT JOIN `product_tmplvar_contentvalues` AS `a60` ON `a60`.`contentid` = `content`.`id` AND `a60`.`tmplvarid` = 60
	LEFT JOIN `product_tmplvar_contentvalues` AS `a87` ON `a87`.`contentid` = `content`.`id` AND `a87`.`tmplvarid` = 87
	<...>
	LEFT JOIN `product_tmplvar_contentvalues` AS `a19` ON `a19`.`contentid` = `content`.`id` AND `a19`.`tmplvarid` = 19
WHERE
	1 = 1
	AND `content`.`parent` = 66860
	AND `a83`.`value` = "42160" 

ORDER BY menuindex
LIMIT 100


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
SELECT
SQL_NO_CACHE
	 content.`pagetitle`,content.`id`
	,max(case when aLST.tmplvarid = 16 then aLST.`value` end) AS `a16`
	,max(case when aLST.tmplvarid = 60 then aLST.`value` end) AS `a60`
	,max(case when aLST.tmplvarid = 87 then aLST.`value` end) AS `a87`
	,...
	,max(case when aLST.tmplvarid = 19 then aLST.`value` end) AS `a19`
FROM modx_site_content AS content
JOIN product_tmplvar_contentvalues a83 on a83.contentid=content.id and a83.tmplvarid = 83 and a83.value = '42160'
JOIN product_tmplvar_contentvalues aLST on aLST.contentid=content.id and aLST.tmplvarid in (16,60,87,...,19)
WHERE 1 = 1
  AND `content`.`parent` = 66860
GROUP BY `content`.`pagetitle`,`content`.`id`,`content`.menuindex
ORDER BY `content`.menuindex
LIMIT 100
...
Рейтинг: 0 / 0
26.04.2016, 13:18
| Ответить | Цитировать | Написать  
Тяжелый запрос с Using temporary, Using filesort из-за ORDER
    #39224730
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
Даже, наверное, так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
SELECT
SQL_NO_CACHE
	 content.`pagetitle`,content.`id`
	,max(case when aLST.tmplvarid = 16 then aLST.`value` end) AS `a16`
	,max(case when aLST.tmplvarid = 60 then aLST.`value` end) AS `a60`
	,max(case when aLST.tmplvarid = 87 then aLST.`value` end) AS `a87`
	,...
	,max(case when aLST.tmplvarid = 19 then aLST.`value` end) AS `a19`
FROM modx_site_content AS content
JOIN product_tmplvar_contentvalues aLST on aLST.contentid=content.id and aLST.tmplvarid in (16,60,87,...,19)
WHERE 1 = 1
  AND `content`.`parent` = 66860
  AND 1=(
    select count(distinct tmplvarid) 
    from product_tmplvar_contentvalues 
    where contentid=content.id and (tmplvarid,value) in ((83,'42160'))
    )
GROUP BY `content`.`pagetitle`,`content`.`id`,`content`.menuindex
ORDER BY `content`.menuindex
LIMIT 100
...
Рейтинг: 0 / 0
26.04.2016, 13:29
| Ответить | Цитировать | Написать  
Тяжелый запрос с Using temporary, Using filesort из-за ORDER
    #39224732
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
Cygapb-007
Код: sql
1.
2.
GROUP BY `content`.`pagetitle`,`content`.`id`,`content`.menuindex
ORDER BY `content`.menuindex

Это можно заменить на:
Код: sql
1.
GROUP BY `content`.menuindex, `content`.`pagetitle`, `content`.`id`

Будет и короче, и быстрее.
...
Рейтинг: 0 / 0
26.04.2016, 13:30
| Ответить | Цитировать | Написать  
Тяжелый запрос с Using temporary, Using filesort из-за ORDER
    #39224734
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
miksoft,

да, забыл про сортировку в GROUP BY)))
...
Рейтинг: 0 / 0
26.04.2016, 13:33
| Ответить | Цитировать | Написать  
Тяжелый запрос с Using temporary, Using filesort из-за ORDER
    #39224766
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
supermike,

Код: sql
1.
AND `a83`.`value` = "42160"



Где таблица a83?


Сколько записей подходять под это условие?
Код: sql
1.
`content`.`parent` = 66860
...
Рейтинг: 0 / 0
26.04.2016, 14:04
| Ответить | Цитировать | Написать  
Тяжелый запрос с Using temporary, Using filesort из-за ORDER
    #39224807
supermike
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
supermike
Гость
MasterZiv,

222144 записей.. Если это количество в районе 60к, то можно делать целую кучу условий по характеристикам и все равно запрос будет очень быстро выполняться.
...
Рейтинг: 0 / 0
26.04.2016, 14:27
| Ответить | Цитировать | Написать  
Тяжелый запрос с Using temporary, Using filesort из-за ORDER
    #39224812
supermike
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
supermike
Гость
MasterZivsupermike,

Код: sql
1.
AND `a83`.`value` = "42160"



Где таблица a83?

Это product_tmplvar_contentvalues
...
Рейтинг: 0 / 0
26.04.2016, 14:29
| Ответить | Цитировать | Написать  
Тяжелый запрос с Using temporary, Using filesort из-за ORDER
    #39224831
supermike
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
supermike
Гость
Cygapb-007,

К сожалению, получилось дольше - 17 секунд против 7. Причем если убрать ORDER, скорость не возрастает.
EXPLAIN
 
Код: plaintext
1.
2.
3.
4.
5.
    id  select_type         table                          type    possible_keys                                  key                  key_len  ref                            rows  Extra                                         
------  ------------------  -----------------------------  ------  ---------------------------------------------  -------------------  -------  ---------------------------  ------  ----------------------------------------------
     1  PRIMARY             content                        ref     PRIMARY,parent-menuindex                       parent-menuindex     4        const                        247473  Using where; Using temporary; Using filesort  
     1  PRIMARY             aLST                           ref     contentid-tmplvarid,tmplvarid                  contentid-tmplvarid  4        fluid-line.content.id            13  Using where                                   
     2  DEPENDENT SUBQUERY  product_tmplvar_contentvalues  ref     contentid-tmplvarid,tmplvarid,value-tmplvarid  contentid-tmplvarid  8        fluid-line.content.id,const       1  Using where

ПОЛНЫЙ ЗАПРОС
 
Код: plaintext
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
SQL_NO_CACHE
	 content.`pagetitle`,content.`id`
	,MAX(CASE WHEN aLST.tmplvarid = 16 THEN aLST.`value` END) AS `a16`
	,MAX(CASE WHEN aLST.tmplvarid = 60 THEN aLST.`value` END) AS `a60`
	,MAX(CASE WHEN aLST.tmplvarid = 87 THEN aLST.`value` END) AS `a87`
	,MAX(CASE WHEN aLST.tmplvarid = 89 THEN aLST.`value` END) AS `a89`
	,MAX(CASE WHEN aLST.tmplvarid = 59 THEN aLST.`value` END) AS `a59`
	,MAX(CASE WHEN aLST.tmplvarid = 94 THEN aLST.`value` END) AS `a94`
	,MAX(CASE WHEN aLST.tmplvarid = 15 THEN aLST.`value` END) AS `a15`
	,MAX(CASE WHEN aLST.tmplvarid = 6 THEN aLST.`value` END) AS `a6`
	,MAX(CASE WHEN aLST.tmplvarid = 83 THEN aLST.`value` END) AS `a83`
	,MAX(CASE WHEN aLST.tmplvarid = 81 THEN aLST.`value` END) AS `a81`
	,MAX(CASE WHEN aLST.tmplvarid = 88 THEN aLST.`value` END) AS `a88`
	,MAX(CASE WHEN aLST.tmplvarid = 92 THEN aLST.`value` END) AS `a92`
	,MAX(CASE WHEN aLST.tmplvarid = 93 THEN aLST.`value` END) AS `a93`
	,MAX(CASE WHEN aLST.tmplvarid = 87 THEN aLST.`value` END) AS `a87`
	,MAX(CASE WHEN aLST.tmplvarid = 65 THEN aLST.`value` END) AS `a65`
	,MAX(CASE WHEN aLST.tmplvarid = 95 THEN aLST.`value` END) AS `a95`
	,MAX(CASE WHEN aLST.tmplvarid = 86 THEN aLST.`value` END) AS `a86`
	,MAX(CASE WHEN aLST.tmplvarid = 19 THEN aLST.`value` END) AS `a19`
FROM modx_site_content AS content
JOIN product_tmplvar_contentvalues aLST on aLST.contentid=content.id and aLST.tmplvarid in (16,60,87,89,59,94,15,6,83,81,88,92,93,87,65,95,86,19)
WHERE 1 = 1
  AND `content`.`parent` = 66860
  AND 1=(
    select count(distinct tmplvarid) 
    from product_tmplvar_contentvalues 
    where contentid=content.id and (tmplvarid,value) in ((83,'42160'))
    )
GROUP BY `content`.`pagetitle`,`content`.`id`,`content`.menuindex
ORDER BY `content`.menuindex
LIMIT 100
...
Рейтинг: 0 / 0
26.04.2016, 14:45
| Ответить | Цитировать | Написать  
Тяжелый запрос с Using temporary, Using filesort из-за ORDER
    #39224857
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
supermike,

а если попробовать индекс contentid-tmplvarid-value ?
там время может теряться на RID - поиск записи по PK для извлечения Value
или неподъемным получится?
...
Рейтинг: 0 / 0
26.04.2016, 15:04
| Ответить | Цитировать | Написать  
Тяжелый запрос с Using temporary, Using filesort из-за ORDER
    #39224962
supermike
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
supermike
Гость
Cygapb-007,
Индекс очень долго создавался, нужно потестировать - завтра отпишусь о результатх.
...
Рейтинг: 0 / 0
26.04.2016, 16:49
| Ответить | Цитировать | Написать  
Тяжелый запрос с Using temporary, Using filesort из-за ORDER
    #39225101
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
supermike,


....выкинте ВСЕ связки и все таблицы которые не участвуют в
поиске хатактеристик. отсортируйте результат и выберете 100 товаров.
а потом к ним подсоединяйте остальные характеристики...


а то получается что все-на-все умножаете, потом сортируете и выбираете 100 верхних...
...
Рейтинг: 0 / 0
26.04.2016, 19:50
| Ответить | Цитировать | Написать  
Тяжелый запрос с Using temporary, Using filesort из-за ORDER
    #39226666
supermike
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
supermike
Гость
Прошу прощения за задержку с ответом - много времени ушло на тестирование, ведь запросы на сервер идут разной степени сложности и нужно было все проверить.

Cygapb-007supermike,
а если попробовать индекс contentid-tmplvarid-value ?
там время может теряться на RID - поиск записи по PK для извлечения Value
или неподъемным получится?

USE INDEX(contentid, tmplvarid, value), на сколько я заметил, по эффективности вышел таким же как и индекс по столбцам "contentid, tmplvarid" - разницы между ними практически никакой. Эти индексы в некоторых случаях наоборот замедляют запрос, причем на порядок. Из-за того, что индекс много весит, пробовал увеличить key_buffer_size - не помогло, видимо все-таки проблема не в этом.

Соответственно я писал так:
Код: plaintext
1.
2.
LEFT JOIN `product_tmplvar_contentvalues` AS `a60` USE INDEX(`contentid-tmplvarid-value`)  ON `a60`.`contentid` = `content`.`id` AND `a60`.`tmplvarid` = 60
LEFT JOIN `product_tmplvar_contentvalues` AS `a21` USE INDEX(`contentid-tmplvarid-value`) ON `a21`.`contentid` = `content`.`id` AND `a21`.`tmplvarid` = 21
LEFT JOIN `product_tmplvar_contentvalues` AS `a16` USE INDEX(`contentid-tmplvarid-value`) ON `a16`.`contentid` = `content`.`id` AND `a16`.`tmplvarid` = 16

javajdbcsupermike,
....выкинте ВСЕ связки и все таблицы которые не участвуют в
поиске хатактеристик. отсортируйте результат и выберете 100 товаров.
а потом к ним подсоединяйте остальные характеристики...
а то получается что все-на-все умножаете, потом сортируете и выбираете 100 верхних...

Резонно, этот вариант ускоряет выборку в среднем раза в 2-3, что очень хорошо, спасибо за подсказку! К сожалению, это все же не решает проблему полностью. Сейчас в каждом разделе по 200+к позиций(там где parent = n), и уже все это дело еле движется. В некоторых случаях задержки по 4 сек. А потребуется выгрузить в раздел не 200к, а 500к, к примеру - тогда все будет висеть. Кэшировать не вариант, т.к. данные каждый день изменяются да и невозможно закэшировать все варианты фильтра. Как реализовывают такой фильтр продукции на других сайтах? Неужели через NOSQL? Или создают огромную таблицу с кучей столбцов-характеристик? Можно конечно все это дело вынести в оперативку, но неужели нет другого решения? Поделитесь опытом...
...
Рейтинг: 0 / 0
28.04.2016, 14:24
| Ответить | Цитировать | Написать  
Тяжелый запрос с Using temporary, Using filesort из-за ORDER
    #39226699
supermike
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
supermike
Гость
Хотя, наверно проблема надуманная, т.к. большие задержки возникают если в выборку попадает много записей, т.е. в фильтре не указаны основные характеристики продукции, а указаны только второстепенные, напр. при подборе машины(допустим) не указан ни тип кузова, ни цвет, а только размер дисков и наличие дворника - в результате в выборку попадают почти все машины и здесь поиск подходящих по фильтру машин выполняется очень медленно. Видимо нужно обозначать основные характеристики продукции, которые будут обязательными для выбора в фильтре...
...
Рейтинг: 0 / 0
28.04.2016, 14:55
| Ответить | Цитировать | Написать  
15 сообщений из 15, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Тяжелый запрос с Using temporary, Using filesort из-за ORDER
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&msg=39224734&tid=1831860]:
 0ms
get settings:
 9ms
get forum list:
 22ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 44ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 73ms
get tp. blocked users:
 2ms
others: 210ms
total:  381ms
созд. / загр.: 381ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]