ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Нужна помощь с индексами.
19 сообщений из 19, страница 1 из 1
Нужна помощь с индексами.
    #38525969
stupor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
stupor
Гость
Здравствуйте.
Как можно оптимизировать запрос? С индексами что можно сделать? Запрос занимает 5-6 секунд.
В таблице report более 4 млн. записей. В остальных таблицах не более 20-и.
Насколько я понимаю Using temporary; Using filesort указывает на какую-то проблему.

-- --------------------------------------------------------
-- Server version: 5.1.69 - Source distribution
-- --------------------------------------------------------
Код: 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.
CREATE TABLE IF NOT EXISTS `report` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_unique_id` varchar(255) NOT NULL,
  `facility_id` int(11) NOT NULL,
  `room_id` int(11) NOT NULL DEFAULT '0',
  `user_type` int(20) DEFAULT NULL,
  `current_timestamp` bigint(20) NOT NULL,
  `timestamp` bigint(20) NOT NULL,
  `message` text NOT NULL,
  `type` int(11) NOT NULL,
  `general_field1` longtext,
  `general_field2` longtext,
  `general_field3` longtext,
  `general_field4` longtext,
  `general_field5` longtext,
  PRIMARY KEY (`id`),
  KEY `general` (`general_field1`(100),`general_field2`(100),`general_field3`(100)),
  KEY `report` (`type`,`facility_id`,`user_unique_id`),
  KEY `timestamp` (`timestamp`),
  KEY `User_ID` (`user_unique_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


explain SELECT SQL_CALC_FOUND_ROWS r.id, r.room_id, r.general_field1, r.general_field2, r.general_field3, r.general_field4, r.general_field5, IF(p.severity IS NULL, s.field_report_severity_target_id, p.severity) AS event_severity, r.current_timestamp, t.name AS event_name, r.user_unique_id
FROM report r
LEFT JOIN drupal_field_report_type_unique_id f ON f.field_report_type_unique_id_value = r.type AND f.deleted=0
LEFT JOIN drupal_taxonomy_term t ON t.tid = f.entity_id
LEFT JOIN drupal_report_severity s ON s.entity_id=t.tid AND s.deleted=0
LEFT JOIN event_properties p ON p.event_id=f.entity_id AND p.facility_id=r.facility_id
LEFT JOIN drupal_field_user_identifier i ON i.field_user_identifier_value = r.user_unique_id AND i.deleted=0
WHERE
t.vid=4 AND
( (p.id IS NULL AND s.field_report_severity_target_id !=89) OR (p.id IS NOT NULL AND p.severity !=89)) AND
r.facility_id=1648 AND
i.entity_id IS NOT NULL AND
( (r.type=15 AND r.general_field1=1 AND r.general_field2=1) OR r.type!=15) AND
( (r.type=16 AND r.general_field1=1 AND r.general_field2=1) OR r.type!=16) AND
( (r.type=17 AND r.general_field1=1 AND r.general_field2=1) OR r.type!=17) AND
( (r.type=32 AND r.general_field3=0) OR r.type!=20) AND
( (r.type=21 AND r.general_field1=0) OR r.type!=21) AND
( (r.type=22 AND r.general_field1=0) OR r.type!=22) AND
( (r.type=23 AND r.general_field1=1 AND r.general_field2=0 AND r.general_field3=0) OR r.type!=23)
GROUP BY r.id
ORDER BY event_severity ASC , r.id DESC ;



Я прикрепил файл с EXPLAIN
...
Рейтинг: 0 / 0
14.01.2014, 17:25:28
| Ответить | Цитировать | Написать  
Нужна помощь с индексами.
    #38526030
Фотография ScareCrow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ScareCrow
Участник
сделай индексы по полям джойнов
...
Рейтинг: 0 / 0
14.01.2014, 18:06:17
| Ответить | Цитировать | Написать  
Нужна помощь с индексами.
    #38526048
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Я фигею без баяна...
Код: sql
1.
WHERE t.vid=4

и связывание уже INNER, а ни фига не LEFT...
Код: sql
1.
r.facility_id=1648

ещё одно...
Код: sql
1.
i.entity_id IS NOT NULL

и ещё...
Код: sql
1.
GROUP BY r.id

По первичному индексу, ага...

Тут нечего оптимизировать. Надо всё удалить и начать заново. С нуля.
...
Рейтинг: 0 / 0
14.01.2014, 18:13:25
| Ответить | Цитировать | Написать  
Нужна помощь с индексами.
    #38526168
retvizan
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
retvizan
Участник
Akina[/src]и ещё...
Код: sql
1.
GROUP BY r.id

По первичному индексу, ага...Зато нет проблем с тем, что выбираются поля, не входящие в группировку и без группирующей функции :)
...
Рейтинг: 0 / 0
14.01.2014, 19:43:36
| Ответить | Цитировать | Написать  
Нужна помощь с индексами.
    #38526175
stupor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
stupor
Гость
спасибо. Переделал запрос, исправил на INNER где можно было. В event_properties может не быть соответств. записи, поэтому LEFT JOIN.
Добавил индекс по drupal_field_user_identifier.field_user_identifier_value. По остальным полям есть индексы.
1648 - переменная, если делаю отдельный индех по r.facility_id и r.type - это наоборот замедляет.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
SELECT SQL_CALC_FOUND_ROWS r.id, r.room_id, r.general_field1, r.general_field2, r.general_field3, r.general_field4, r.general_field5, IF(p.severity IS NULL, s.field_report_severity_target_id, p.severity) AS event_severity, r.current_timestamp, t.name AS event_name, r.user_unique_id 
FROM report r 
	INNER JOIN drupal_field_report_type_unique_id f ON f.field_report_type_unique_id_value = r.type AND f.deleted=0 
	INNER JOIN drupal_taxonomy_term t ON t.tid = f.entity_id AND t.vid=4
	INNER JOIN drupal_report_severity s ON s.entity_id=t.tid AND s.deleted=0 
	LEFT JOIN event_properties p ON p.event_id=f.entity_id AND p.facility_id=1648
	INNER JOIN drupal_field_user_identifier i ON i.field_user_identifier_value = r.user_unique_id AND i.deleted=0 
WHERE 
	r.facility_id=1648 AND 
	( (p.id IS NULL AND s.field_report_severity_target_id !=89) OR (p.id IS NOT NULL AND p.severity !=89)) AND 
	( (r.type=15 AND r.general_field1=1 AND r.general_field2=1) OR r.type!=15) AND 
	( (r.type=16 AND r.general_field1=1 AND r.general_field2=1) OR r.type!=16) AND 
	( (r.type=17 AND r.general_field1=1 AND r.general_field2=1) OR r.type!=17) AND 
	( (r.type=32 AND r.general_field3=0) OR r.type!=20) AND 
	( (r.type=21 AND r.general_field1=0) OR r.type!=21) AND 
	( (r.type=22 AND r.general_field1=0) OR r.type!=22) AND 
	( (r.type=23 AND r.general_field1=1 AND r.general_field2=0 AND r.general_field3=0) OR r.type!=23) 
GROUP BY r.id 
ORDER BY event_severity ASC , r.id DESC ;



GROUP BY r.id мне нужно на тот случай если будет несколько записей в event_properties.
прикрепил файл с EXPLAIN
...
Рейтинг: 0 / 0
14.01.2014, 19:50:29
| Ответить | Цитировать | Написать  
Нужна помощь с индексами.
    #38526236
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
stupor
Код: sql
1.
SELECT SQL_CALC_FOUND_ROWS

Вот оно тут нахрена? особенно с учётом того, что у тебя НЕТ лимита...

stuporGROUP BY r.id мне нужно на тот случай если будет несколько записей в event_properties.
И в этом случае тебя устроит любое из event_properties.severity... а тогда ты уверен, что оно тебе вообще нужно?
...
Рейтинг: 0 / 0
14.01.2014, 21:08:05
| Ответить | Цитировать | Написать  
Нужна помощь с индексами.
    #38526249
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
Akinastupor
Код: sql
1.
SELECT SQL_CALC_FOUND_ROWS

Вот оно тут нахрена? особенно с учётом того, что у тебя НЕТ лимита...а почему бы и нет?
...
Рейтинг: 0 / 0
14.01.2014, 21:29:01
| Ответить | Цитировать | Написать  
Нужна помощь с индексами.
    #38526278
stupor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
stupor
Гость
Akina,

LIMIT есть, просто я его в EXPLAIN не вставлял.

event_properties.severity может быть несколько, и поэтому без GROUP BY r.id будет несколько одинаковых записей из report
...
Рейтинг: 0 / 0
14.01.2014, 22:04:18
| Ответить | Цитировать | Написать  
Нужна помощь с индексами.
    #38526280
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
stuporLIMIT есть, просто я его в EXPLAIN не вставлял.А вот это зря. Запросы с LIMIT-ом и без - разные запросы и у них может быть разные и план, и время выполнения.
...
Рейтинг: 0 / 0
14.01.2014, 22:10:22
| Ответить | Цитировать | Написать  
Нужна помощь с индексами.
    #38526468
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
stuporevent_properties.severity может быть несколько, и поэтому без GROUP BY r.id будет несколько одинаковых записей из report То есть вы можете гарантировать, что для каждого r.id они все и всегда будут одинаковыми?
...
Рейтинг: 0 / 0
15.01.2014, 07:20:15
| Ответить | Цитировать | Написать  
Нужна помощь с индексами.
    #38526632
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
stuporAkina,

LIMIT есть, просто я его в EXPLAIN не вставлял.

event_properties.severity может быть несколько, и поэтому без GROUP BY r.id будет несколько одинаковых записей из report

мужик, тогда вставь в EXPLAIN сразу запрос

SELECT 42;

и у тебя стазу классный план там покажется.
...
Рейтинг: 0 / 0
15.01.2014, 10:32:12
| Ответить | Цитировать | Написать  
Нужна помощь с индексами.
    #38528716
stupor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
stupor
Гость
tanglir,
я имел ввиду, что одна и та же запись может несколько раз выбраться. Из-за LEFT JOIN.
Хотя, я подумал, такой вариант вряд ли возможен, и можно действительно убрать GROUP BY
...
Рейтинг: 0 / 0
16.01.2014, 18:32:05
| Ответить | Цитировать | Написать  
Нужна помощь с индексами.
    #38531000
stupor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
stupor
Гость
убрал GROUP BY r.id. Вот то что осталось:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
SELECT SQL_CALC_FOUND_ROWS r.id, r.room_id, r.general_field1, r.general_field2, r.general_field3, r.general_field4, r.general_field5, IF(p.severity IS NULL, s.field_report_severity_target_id, p.severity) AS event_severity, r.current_timestamp, t.name AS event_name, r.user_unique_id 
FROM report r 
	INNER JOIN drupal_field_report_type_unique_id f ON f.field_report_type_unique_id_value = r.type AND f.deleted=0 
	INNER JOIN drupal_taxonomy_term t ON t.tid = f.entity_id AND t.vid=4
	INNER JOIN drupal_report_severity s ON s.entity_id=t.tid AND s.deleted=0 
	LEFT JOIN event_properties p ON p.event_id=f.entity_id AND p.facility_id=1648
	INNER JOIN drupal_field_user_identifier i ON i.field_user_identifier_value = r.user_unique_id AND i.deleted=0 
WHERE 
	r.facility_id=1648 AND 
	( (p.id IS NULL AND s.field_report_severity_target_id !=89) OR (p.id IS NOT NULL AND p.severity !=89)) AND 
	( (r.type=15 AND r.general_field1=1 AND r.general_field2=1) OR r.type!=15) AND 
	( (r.type=16 AND r.general_field1=1 AND r.general_field2=1) OR r.type!=16) AND 
	( (r.type=17 AND r.general_field1=1 AND r.general_field2=1) OR r.type!=17) AND 
	( (r.type=32 AND r.general_field3=0) OR r.type!=20) AND 
	( (r.type=21 AND r.general_field1=0) OR r.type!=21) AND 
	( (r.type=22 AND r.general_field1=0) OR r.type!=22) AND 
	( (r.type=23 AND r.general_field1=1 AND r.general_field2=0 AND r.general_field3=0) OR r.type!=23) 
ORDER BY event_severity ASC , r.id DESC 
LIMIT 0, 100;


запрос бежит гораздо быстрее, но все равно осталолись Using temporary; Using filesort ; как я понимаю это указывает на то, что mysql делает временный индекс.
...
Рейтинг: 0 / 0
19.01.2014, 14:34:20
| Ответить | Цитировать | Написать  
Нужна помощь с индексами.
    #38531339
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
stuporкак я понимаю это указывает на то, что mysql делает временный индекс.Это значит, что, поскольку по этому выражению
Код: sql
1.
IF(p.severity IS NULL, s.field_report_severity_target_id, p.severity) AS event_severity

нет и не может быть индекса, серверу приходится выбирать всё, что подходит под условия в where, после чего сортировать результат.
Т.е. сортировка происходит не "сразу" в процессе чтения данных, а отдельным этапом. И если вам принципиально важна именно такая сортировка, то оптимизировать тут, похоже, уже нечего.
...
Рейтинг: 0 / 0
20.01.2014, 06:05:58
| Ответить | Цитировать | Написать  
Нужна помощь с индексами.
    #38544159
stupor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
stupor
Гость
tanglir,
я убрал event_severity из SELECT полей и ORDER BY, но Using temporary; Using filesort остались.
Скорее всего это что-то другое.
...
Рейтинг: 0 / 0
30.01.2014, 19:23:06
| Ответить | Цитировать | Написать  
Нужна помощь с индексами.
    #38544216
Aleksandr Kuzminsky
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Aleksandr Kuzminsky
Гость
stupor,

Код: sql
1.
ORDER BY event_severity ASC , r.id DESC ;



Вот это делает невозможным использование любого индекса. MySQL не умеет использовать индекс для сортировки, если одно поле ASC, а другое - DESC (даже если event_severity и r.id - это поля одной таблицы ).

я бы упрощал запрос. возможно, часть логики есть смысл отдать приложению. два-три простых, но хороших запроса будут лучше, чем один сложный.
...
Рейтинг: 0 / 0
30.01.2014, 20:20:35
| Ответить | Цитировать | Написать  
Нужна помощь с индексами.
    #38544325
Фотография ScareCrow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ScareCrow
Участник
авторMySQL не умеет использовать индекс для сортировки, если одно поле ASC, а другое - DESC (даже если event_severity и r.id - это поля одной таблицы ).
а MySql об этом знает?
...
Рейтинг: 0 / 0
30.01.2014, 23:18:53
| Ответить | Цитировать | Написать  
Нужна помощь с индексами.
    #38544334
Aleksandr Kuzminsky
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Aleksandr Kuzminsky
Гость
ScareCrow
а MySql об этом знает?

А Вы внимательно на EXPLAIN посмотрите.
...
Рейтинг: 0 / 0
30.01.2014, 23:28:15
| Ответить | Цитировать | Написать  
Нужна помощь с индексами.
    #38546468
stupor
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
stupor
Гость
ребята, спасибо за помощь!
как я уже сказал, я убрал event_severity (для поиска проблемы). Вот как запрос выглядит сейчас:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
SELECT SQL_CALC_FOUND_ROWS r.id, r.room_id, r.general_field1, r.general_field2, r.general_field3, r.general_field4, r.general_field5,  r.current_timestamp, t.name AS event_name, r.user_unique_id 
FROM report r 
	INNER JOIN drupal_field_report_type_unique_id f ON f.field_report_type_unique_id_value = r.type AND f.deleted=0 
	INNER JOIN drupal_taxonomy_term t ON t.tid = f.entity_id AND t.vid=4
	INNER JOIN drupal_report_severity s ON s.entity_id=t.tid AND s.deleted=0 
	LEFT JOIN event_properties p ON p.event_id=f.entity_id AND p.facility_id=1648
	INNER JOIN drupal_field_user_identifier i ON i.field_user_identifier_value = r.user_unique_id AND i.deleted=0 
WHERE 
	r.facility_id=1648 AND 
	( (p.id IS NULL AND s.field_report_severity_target_id !=89) OR (p.id IS NOT NULL AND p.severity !=89)) AND 
	( (r.type=15 AND r.general_field1=1 AND r.general_field2=1) OR r.type!=15) AND 
	( (r.type=16 AND r.general_field1=1 AND r.general_field2=1) OR r.type!=16) AND 
	( (r.type=17 AND r.general_field1=1 AND r.general_field2=1) OR r.type!=17) AND 
	( (r.type=32 AND r.general_field3=0) OR r.type!=20) AND 
	( (r.type=21 AND r.general_field1=0) OR r.type!=21) AND 
	( (r.type=22 AND r.general_field1=0) OR r.type!=22) AND 
	( (r.type=23 AND r.general_field1=1 AND r.general_field2=0 AND r.general_field3=0) OR r.type!=23) 
ORDER BY r.id DESC 
LIMIT 0, 100;


присоединяю explain
...
Рейтинг: 0 / 0
02.02.2014, 18:46:00
| Ответить | Цитировать | Написать  
19 сообщений из 19, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Нужна помощь с индексами.
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&tid=1835295]:
 0ms
get settings:
 8ms
get forum list:
 18ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 51ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 69ms
get tp. blocked users:
 2ms
others: 215ms
total:  381ms
созд. / загр.: 381ms
x
x
Закрыть


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