ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Хочу избавиться от USING temporary
14 сообщений из 14, страница 1 из 1
Хочу избавиться от USING temporary
    #39043222
Light91
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Light91
Гость
Добрый день, столкнулся с необходимостью оптимизировать ряд запросов и постоянно натыкаюсь на USING temprory, собственно сам запрос
Код: sql
1.
2.
3.
4.
5.
EXPLAIN SELECT SQL_NO_CACHE B.ID
FROM b_iblock B
INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
ORDER BY BE.SORT ASC , BE.ID DESC
LIMIT 200

А вот что получаю в результате
Код: plaintext
1.
2.
3.
id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	B 	index 	PRIMARY,LID_2 	LID 	2 	NULL	7 	Using index; Using temporary; Using filesort
1 	SIMPLE 	BE 	ref 	ix_iblock_element_1,ix_iblock_element_4,ix_iblock_... 	ix_iblock_element_4 	4 	test4.B.ID 	5131 	NULL
И не пойму почему он делает сортировку таблицы B(!), при этом создавая временную таблицу, в то время как указана сортировка таблицы BE по существующему индексу


Код: 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.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
-- --------------------------------------------------------

--
-- Структура таблицы `b_iblock`
--

CREATE TABLE IF NOT EXISTS `b_iblock` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `TIMESTAMP_X` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `IBLOCK_TYPE_ID` varchar(50) NOT NULL,
  `LID` char(2) NOT NULL,
  `CODE` varchar(50) DEFAULT NULL,
  `NAME` varchar(255) NOT NULL,
  `ACTIVE` char(1) NOT NULL DEFAULT 'Y',
  `SORT` int(11) NOT NULL DEFAULT '500',
  `LIST_PAGE_URL` varchar(255) DEFAULT NULL,
  `DETAIL_PAGE_URL` varchar(255) DEFAULT NULL,
  `SECTION_PAGE_URL` varchar(255) DEFAULT NULL,
  `CANONICAL_PAGE_URL` varchar(255) DEFAULT NULL,
  `PICTURE` int(18) DEFAULT NULL,
  `DESCRIPTION` text,
  `DESCRIPTION_TYPE` char(4) NOT NULL DEFAULT 'text',
  `RSS_TTL` int(11) NOT NULL DEFAULT '24',
  `RSS_ACTIVE` char(1) NOT NULL DEFAULT 'Y',
  `RSS_FILE_ACTIVE` char(1) NOT NULL DEFAULT 'N',
  `RSS_FILE_LIMIT` int(11) DEFAULT NULL,
  `RSS_FILE_DAYS` int(11) DEFAULT NULL,
  `RSS_YANDEX_ACTIVE` char(1) NOT NULL DEFAULT 'N',
  `XML_ID` varchar(255) DEFAULT NULL,
  `TMP_ID` varchar(40) DEFAULT NULL,
  `INDEX_ELEMENT` char(1) NOT NULL DEFAULT 'Y',
  `INDEX_SECTION` char(1) NOT NULL DEFAULT 'N',
  `WORKFLOW` char(1) NOT NULL DEFAULT 'Y',
  `BIZPROC` char(1) NOT NULL DEFAULT 'N',
  `SECTION_CHOOSER` char(1) DEFAULT NULL,
  `LIST_MODE` char(1) DEFAULT NULL,
  `RIGHTS_MODE` char(1) DEFAULT NULL,
  `SECTION_PROPERTY` char(1) DEFAULT NULL,
  `PROPERTY_INDEX` char(1) DEFAULT NULL,
  `VERSION` int(11) NOT NULL DEFAULT '1',
  `LAST_CONV_ELEMENT` int(11) NOT NULL DEFAULT '0',
  `SOCNET_GROUP_ID` int(18) DEFAULT NULL,
  `EDIT_FILE_BEFORE` varchar(255) DEFAULT NULL,
  `EDIT_FILE_AFTER` varchar(255) DEFAULT NULL,
  `SECTIONS_NAME` varchar(100) DEFAULT NULL,
  `SECTION_NAME` varchar(100) DEFAULT NULL,
  `ELEMENTS_NAME` varchar(100) DEFAULT NULL,
  `ELEMENT_NAME` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `ix_iblock` (`IBLOCK_TYPE_ID`,`LID`,`ACTIVE`),
  KEY `LID` (`LID`),
  KEY `LID_2` (`ID`,`LID`)
) ENGINE=InnoDB  DEFAULT CHARSET=cp1251 AUTO_INCREMENT=14 ;



ALTER TABLE `b_iblock`
  ADD CONSTRAINT `b_iblock_ibfk_1` FOREIGN KEY (`LID`) REFERENCES `b_lang` (`LID`),
  ADD CONSTRAINT `b_iblock_ibfk_2` FOREIGN KEY (`LID`) REFERENCES `b_lang` (`LID`);
-- --------------------------------------------------------

--
-- Структура таблицы `b_iblock_element`
--

CREATE TABLE IF NOT EXISTS `b_iblock_element` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `TIMESTAMP_X` datetime DEFAULT NULL,
  `MODIFIED_BY` int(18) DEFAULT NULL,
  `DATE_CREATE` datetime DEFAULT NULL,
  `CREATED_BY` int(18) DEFAULT NULL,
  `IBLOCK_ID` int(11) NOT NULL DEFAULT '0',
  `IBLOCK_SECTION_ID` int(11) DEFAULT NULL,
  `ACTIVE` char(1) NOT NULL DEFAULT 'Y',
  `ACTIVE_FROM` datetime DEFAULT NULL,
  `ACTIVE_TO` datetime DEFAULT NULL,
  `SORT` int(11) NOT NULL DEFAULT '500',
  `NAME` varchar(255) NOT NULL,
  `PREVIEW_PICTURE` int(18) DEFAULT NULL,
  `PREVIEW_TEXT` text,
  `PREVIEW_TEXT_TYPE` varchar(4) NOT NULL DEFAULT 'text',
  `DETAIL_PICTURE` int(18) DEFAULT NULL,
  `DETAIL_TEXT` longtext,
  `DETAIL_TEXT_TYPE` varchar(4) NOT NULL DEFAULT 'text',
  `SEARCHABLE_CONTENT` text,
  `WF_STATUS_ID` int(18) DEFAULT '1',
  `WF_PARENT_ELEMENT_ID` int(11) DEFAULT NULL,
  `WF_NEW` char(1) DEFAULT NULL,
  `WF_LOCKED_BY` int(18) DEFAULT NULL,
  `WF_DATE_LOCK` datetime DEFAULT NULL,
  `WF_COMMENTS` text,
  `IN_SECTIONS` char(1) NOT NULL DEFAULT 'N',
  `XML_ID` varchar(255) DEFAULT NULL,
  `CODE` varchar(255) DEFAULT NULL,
  `TAGS` varchar(255) DEFAULT NULL,
  `TMP_ID` varchar(40) DEFAULT NULL,
  `WF_LAST_HISTORY_ID` int(11) DEFAULT NULL,
  `SHOW_COUNTER` int(18) DEFAULT NULL,
  `SHOW_COUNTER_START` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `SORT_2` (`SORT`,`ID`),
  UNIQUE KEY `ID` (`ID`,`SORT`),
  UNIQUE KEY `IBLOCK_ID_2` (`ACTIVE_TO`,`ACTIVE_FROM`,`ACTIVE`,`WF_STATUS_ID`,`WF_PARENT_ELEMENT_ID`,`ID`,`SORT`),
  KEY `ix_iblock_element_1` (`IBLOCK_ID`,`IBLOCK_SECTION_ID`),
  KEY `ix_iblock_element_4` (`IBLOCK_ID`,`XML_ID`,`WF_PARENT_ELEMENT_ID`),
  KEY `ix_iblock_element_3` (`WF_PARENT_ELEMENT_ID`),
  KEY `ix_iblock_element_code` (`IBLOCK_ID`,`CODE`),
  KEY `NAME` (`NAME`),
  KEY `SORT` (`SORT`),
  KEY `ID_2` (`ID`,`IBLOCK_ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=cp1251 AUTO_INCREMENT=65299 ;

ALTER TABLE `b_iblock_element`
  ADD CONSTRAINT `b_iblock_element_ibfk_1` FOREIGN KEY (`IBLOCK_ID`) REFERENCES `b_iblock` (`ID`);


Я уже понатыкал ключей "лишь бы были" так и это не помагает.
...
Рейтинг: 0 / 0
04.09.2015, 11:34:44
| Ответить | Цитировать | Написать  
Хочу избавиться от USING temporary
    #39043228
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Добавлю подсветку, ежели ты сам не умеешь...
Light91собственно сам запрос
Код: sql
1.
2.
3.
4.
5.
6.
EXPLAIN 
SELECT SQL_NO_CACHE B.ID
FROM b_iblock B
INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
ORDER BY BE.SORT ASC , BE.ID DESC
LIMIT 200


Ты сортируешь данные одной таблицы по данным другой. Чтобы выполнить сортировку, нужно сначала получить совокупную таблицу. Это невозможно сделать без USING temporary.
...
Рейтинг: 0 / 0
04.09.2015, 11:40:17
| Ответить | Цитировать | Написать  
Хочу избавиться от USING temporary
    #39043236
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
Конкретно для этого запроса я бы попробовал индекс (SORT, ID, IBLOCK_ID) на таблице b_iblock_element.
Но, во-первых, запрос не похож на логичный.
Во-вторых, а что это вы в битриксе запросы правите? Неужто кощунствуете, ядро правя?
...
Рейтинг: 0 / 0
04.09.2015, 11:44:32
| Ответить | Цитировать | Написать  
Хочу избавиться от USING temporary
    #39043240
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
Я бы предложил индекс (IBLOCK_ID,SORT), и select be.IBLOCK_ID вместо B.ID.
Да и FROM b_iblock - так ли уж необходимо?
...
Рейтинг: 0 / 0
04.09.2015, 11:50:18
| Ответить | Цитировать | Написать  
Хочу избавиться от USING temporary
    #39043243
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
Cygapb-007Да и FROM b_iblock - так ли уж необходимо?Всмысле - при наличии FK достаточно отфильтровать NOT NULL
...
Рейтинг: 0 / 0
04.09.2015, 11:53:27
| Ответить | Цитировать | Написать  
Хочу избавиться от USING temporary
    #39043254
Light91
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Light91
Гость
Ну давайте по порядку, нет, я не вношу правок в ядро и не склонен этого делать, просто раскручиваю сложные запросы и пытаюсь разобраться как их можно оптимизировать за счет создания ключей и/или правок настроек mysql.
Так что нужно пожалуй уточнить, никаких изменений запроса сделать не смогу.
Индексы сейчас попробую добавить.
Спасибо за скорые ответы.


Ты сортируешь данные одной таблицы по данным другой. Чтобы выполнить сортировку, нужно сначала получить совокупную таблицу. Это невозможно сделать без USING temporary.
А вот это уже интересно, что скажешь об этом?

EXPLAIN SELECT SQL_NO_CACHE B.ID
FROM b_iblock B
INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
ORDER BY BE.SORT asc ,BE.ID desc
LIMIT 200;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE BE ALL ix_iblock_element_1,ix_iblock_element_4,ix_iblock_element_code,IBLOCK_ID 50990 Using filesort
1 SIMPLE B eq_ref PRIMARY PRIMARY 4 test4.BE.IBLOCK_ID 1 Using index

Запрос выполнил на другой версии mysql, с другими настройками, тндексы по моемпу те
...
Рейтинг: 0 / 0
04.09.2015, 12:01:43
| Ответить | Цитировать | Написать  
Хочу избавиться от USING temporary
    #39043255
Light91
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Light91
Гость
по моему те же.
ps
Не могу отредактировать своё предыдущее сообщение.
...
Рейтинг: 0 / 0
04.09.2015, 12:02:49
| Ответить | Цитировать | Написать  
Хочу избавиться от USING temporary
    #39043371
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Light91, по-моему, Вам дан правильный совет - трансформировать запрос в
Код: sql
1.
2.
3.
4.
SELECT BE.IBLOCK_ID
FROM b_iblock B, b_iblock_element BE 
WHERE BE.IBLOCK_ID = B.ID
ORDER BY BE.SORT ASC , BE.ID DESC


ну или во всякоразное
Код: sql
1.
2.
3.
4.
SELECT BE.IBLOCK_ID
FROM b_iblock_element BE 
WHERE EXISTS (SELECT 1 FROM b_iblock B WHERE BE.IBLOCK_ID = B.ID)
ORDER BY BE.SORT ASC , BE.ID DESC
...
Рейтинг: 0 / 0
04.09.2015, 13:14:35
| Ответить | Цитировать | Написать  
Хочу избавиться от USING temporary
    #39043390
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
AkinaLight91, по-моему, Вам дан правильный совет - трансформировать запрос в
не совсем так:
Код: sql
1.
2.
3.
4.
SELECT /*DISTINCT*/ BE.IBLOCK_ID
FROM b_iblock_element BE 
WHERE BE.IBLOCK_ID IS NOT NULL
-- ORDER BY BE.SORT ASC , BE.ID DESC

Тот же результат
Да и нафига сортировка, если ее не видно?

Хотя если запросы исправить нельзя, только индексы и остаются для подстройки...
...
Рейтинг: 0 / 0
04.09.2015, 13:27:46
| Ответить | Цитировать | Написать  
Хочу избавиться от USING temporary
    #39043391
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
Опс, сорь, протупил с лимитом... Нужна сортировка...
...
Рейтинг: 0 / 0
04.09.2015, 13:29:05
| Ответить | Цитировать | Написать  
Хочу избавиться от USING temporary
    #39043396
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
Light91Добрый день, столкнулся с необходимостью оптимизировать ряд запросов и постоянно натыкаюсь на USING temprory, собственно сам запрос
[src sql]
EXPLAIN SELECT SQL_NO_CACHE B.ID
FROM b_iblock B
INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
ORDER BY BE.SORT ASC , BE.ID DESC
LIMIT 200


Само по себе USING temporary -- ни хорошо, и ни плохо, так что избавляться от него как такового не нужно.


Но конкретно тут у тебя плохой запрос, в нём нет WHERE вообще, а значит, сортируются все записи, а затем из них отбираются 200 первых.

Тут надо избавляться от USING temprory, точнее, надо ускорять сортировку.

Для этого запроса достаточно создать индекс по двум условиям сортировки

(SORT ASC , ID DESC)
на таблицу b_iblock_element. (индекс, естественно, BTREE)

Теперь надо вспомнить, умеет ли MySQL создавать индексы с явно указанной сортировкой...
глядим: https://dev.mysql.com/doc/refman/5.0/en/create-index.html

авторAn index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.


Ой-ёй! Не умеет... А последняя версия 5.7 ? -- ай, тоже нет...

Ну, ты, мужик, попал!

Тебе либо надо избавиться от второго поля в ORDER BY BE.SORT ASC, BE.ID DESC
, если по логике приложения это подходит, либо сделать другое поле, зависимое от be.ID,
и растущее в противоположную сторону, и по нему сортировать и создавать индекс с его участием.
...
Рейтинг: 0 / 0
04.09.2015, 13:30:41
| Ответить | Цитировать | Написать  
Хочу избавиться от USING temporary
    #39043399
Cygapb-007
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Cygapb-007
Участник
MasterZivДля этого запроса достаточно создать индекс по двум условиям сортировки

(SORT ASC , ID DESC)
на таблицу b_iblock_element. (индекс, естественно, BTREE)
Стесняюсь напомнить, что индекс по SORT уже есть, а ID - РК, то есть содержится в любом другом индексе...
...
Рейтинг: 0 / 0
04.09.2015, 13:35:23
| Ответить | Цитировать | Написать  
Хочу избавиться от USING temporary
    #39043413
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
Cygapb-007MasterZivДля этого запроса достаточно создать индекс по двум условиям сортировки

(SORT ASC , ID DESC)
на таблицу b_iblock_element. (индекс, естественно, BTREE)
Стесняюсь напомнить, что индекс по SORT уже есть, а ID - РК, то есть содержится в любом другом индексе...

Я не смотрел структуру...
...
Рейтинг: 0 / 0
04.09.2015, 13:47:31
| Ответить | Цитировать | Написать  
Хочу избавиться от USING temporary
    #39043442
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Cygapb-007Тот же результат
А, да, там же FK...
...
Рейтинг: 0 / 0
04.09.2015, 14:06:59
| Ответить | Цитировать | Написать  
14 сообщений из 14, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Хочу избавиться от USING temporary
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&msg=39043254&tid=1832756]:
 0ms
get settings:
 9ms
get forum list:
 16ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 20ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 46ms
get tp. blocked users:
 1ms
others: 204ms
total:  311ms
созд. / загр.: 311ms
x
x
Закрыть


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