Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Нужна помощь с индексами. / 19 сообщений из 19, страница 1 из 1
14.01.2014, 17:25:28
    #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, 18:06:17
    #38526030
ScareCrow
ScareCrow
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Нужна помощь с индексами.
сделай индексы по полям джойнов
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.01.2014, 18:13:25
    #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, 19:43:36
    #38526168
retvizan
retvizan
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Нужна помощь с индексами.
Akina[/src]и ещё...
Код: sql
1.
GROUP BY r.id

По первичному индексу, ага...Зато нет проблем с тем, что выбираются поля, не входящие в группировку и без группирующей функции :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.01.2014, 19:50:29
    #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, 21:08:05
    #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:29:01
    #38526249
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Нужна помощь с индексами.
Akinastupor
Код: sql
1.
SELECT SQL_CALC_FOUND_ROWS

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

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

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

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

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

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

SELECT 42;

и у тебя стазу классный план там покажется.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.01.2014, 18:32:05
    #38528716
stupor
stupor
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Нужна помощь с индексами.
tanglir,
я имел ввиду, что одна и та же запись может несколько раз выбраться. Из-за LEFT JOIN.
Хотя, я подумал, такой вариант вряд ли возможен, и можно действительно убрать GROUP BY
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.01.2014, 14:34:20
    #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
| Ответить | Цитировать | Написать  
20.01.2014, 06:05:58
    #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
| Ответить | Цитировать | Написать  
30.01.2014, 19:23:06
    #38544159
stupor
stupor
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Нужна помощь с индексами.
tanglir,
я убрал event_severity из SELECT полей и ORDER BY, но Using temporary; Using filesort остались.
Скорее всего это что-то другое.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.01.2014, 20:20:35
    #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, 23:18:53
    #38544325
ScareCrow
ScareCrow
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Нужна помощь с индексами.
авторMySQL не умеет использовать индекс для сортировки, если одно поле ASC, а другое - DESC (даже если event_severity и r.id - это поля одной таблицы ).
а MySql об этом знает?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.01.2014, 23:28:15
    #38544334
Aleksandr Kuzminsky
Aleksandr Kuzminsky
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Нужна помощь с индексами.
ScareCrow
а MySql об этом знает?

А Вы внимательно на EXPLAIN посмотрите.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.02.2014, 18:46:00
    #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
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Нужна помощь с индексами. / 19 сообщений из 19, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 361ms
Закрыть
start [/forum/topic.php?fid=47&tablet=1&tid=1835295]:
 0ms
get settings:
 9ms
get forum list:
 11ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 48ms
get topic data:
 6ms
get forum data:
 1ms
get page messages:
 35ms
get tp. blocked users:
 1ms
others: 244ms
total:  361ms
x
x
Закрыть


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