оптимизация запроса

predator · 15 Апр 2009

оптимизация запроса

было

PHP:

	SELECT 
		p.id, p.site_id, p.type, t.title, t.url, p.status, p.status_comments, k.mode, k.generate_period, p.created_date, 
		count(distinct d.id ) noofcreated , count(distinct z.id) AS `noofkeys`, k.generate_keywords AS `generate` 
	FROM hct_projects_tb p 
	LEFT JOIN hct_portals_sites_tb t ON p.site_id = t.id 
	LEFT JOIN hct_keywords_projects_tb k ON p.id = k.project_id 
	LEFT JOIN hct_keyword_source_tb s ON k.id = s.keyword_project_id 
	LEFT JOIN hct_keyword_details_tb d ON ( s.id = d.keyword_source_id AND d.generated = 'Y' ) 
	LEFT JOIN hct_keyword_details_tb z on (s.id = z.keyword_source_id AND z.generated = 'N') 
	WHERE p.user_id=13242 and p.id = k.project_id AND k.id = s.keyword_project_id 
	GROUP BY p.id

стало

PHP:

	SELECT 
		p.id, p.site_id, p.type, p.status, p.status_comments, p.created_date, 
        t.title, t.url, 
        k.mode, k.generate_period, k.generate_keywords AS `generate`, 
        (SELECT count(*) FROM hct_keyword_details_tb WHERE s.id=keyword_source_id AND generated='Y') noofcreated,
        (SELECT count(*) FROM hct_keyword_details_tb WHERE s.id=keyword_source_id AND generated='N') AS `noofkeys`
	FROM hct_projects_tb p 
	LEFT JOIN hct_portals_sites_tb t ON p.site_id = t.id 
	LEFT JOIN hct_keywords_projects_tb k ON p.id = k.project_id 
	LEFT JOIN hct_keyword_source_tb s ON k.id = s.keyword_project_id 
	WHERE p.user_id=13242 and p.id = k.project_id AND k.id = s.keyword_project_id 
	GROUP BY p.id

первый вариант вешал сервер когда данных стало много
я насчёт distinct хочу уточнить
поидее он там нужен только для того чтобы избежать NULL который может
появиться при LEFT JOIN

я прав?

Wicked · 15 Апр 2009

поидее он там нужен только для того чтобы избежать NULL который может
появиться при LEFT JOIN

тебе там наверняка в нескольких местах нужен inner join, потому что "left join ... k" + "where ... p.id = k.project_id" - взаимоисключающие вещи.

остальное не читал, т.к. не знаю, что должны эти запросы делать, делают ли они сейчас это, и как они это делают с точки зрения плана выполения

predator · 15 Апр 2009

Автор оригинала: Wicked
тебе там наверняка в нескольких местах нужен inner join, потому что "left join ... k" + "where ... p.id = k.project_id" - взаимоисключающие вещи.

проект писали индусы )
этот момент проглядел кстати
вещи не взаимоисключающие а просто дублирующие
но в where убрал, спасибо

Автор оригинала: Wicked
остальное не читал, т.к. не знаю, что должны эти запросы делать, делают ли они сейчас это, и как они это делают с точки зрения плана выполения

вобще я тоже смутно себе представляю что они делают )
вот explain до

PHP:

1;"SIMPLE";"k";"ALL";"PRIMARY,project_id";NULL;NULL;NULL;197;"Using temporary; Using filesort"
1;"SIMPLE";"s";"ref";"keyword_project_id";"keyword_project_id";"4";"db_cnm_test.k.id";1;"Using where"
1;"SIMPLE";"p";"eq_ref";"PRIMARY";"PRIMARY";"4";"db_cnm_test.k.project_id";1;"Using where"
1;"SIMPLE";"t";"eq_ref";"PRIMARY";"PRIMARY";"4";"db_cnm_test.p.site_id";1;""
1;"SIMPLE";"d";"ref";"keyword_source_id";"keyword_source_id";"4";"db_cnm_test.s.id";558;""
1;"SIMPLE";"z";"ref";"keyword_source_id";"keyword_source_id";"4";"db_cnm_test.s.id";558;""

и после

PHP:

1;"PRIMARY";"k";"ALL";"PRIMARY,project_id";NULL;NULL;NULL;197;"Using temporary; Using filesort"
1;"PRIMARY";"s";"ref";"keyword_project_id";"keyword_project_id";"4";"db_cnm_test.k.id";1;"Using where"
1;"PRIMARY";"p";"eq_ref";"PRIMARY";"PRIMARY";"4";"db_cnm_test.k.project_id";1;"Using where"
1;"PRIMARY";"t";"eq_ref";"PRIMARY";"PRIMARY";"4";"db_cnm_test.p.site_id";1;""
3;"DEPENDENT SUBQUERY";"hct_keyword_details_tb";"ref";"keyword_source_id";"keyword_source_id";"4";"func";558;"Using where"
2;"DEPENDENT SUBQUERY";"hct_keyword_details_tb";"ref";"keyword_source_id";"keyword_source_id";"4";"func";558;"Using where"

это одна из частей UNION'а кстати. вобщем они там накропали кода ))
сейчас рефакторим т.е. постепенно переписываем, но баги надо править - приходиться по живому )

Gas · 15 Апр 2009

predator
а если на таблицу hct_portals_sites_tb навесить индекс (id, user_id), станет она первой в explain'е? Если нет, то попробуй force index и кинь полученный explain

Wicked · 16 Апр 2009

по-моему первой должна идти таблица p, потому что для нее есть и where, и group by. Еще в таблице p должен быть идекс (user_id, id).

Gas · 16 Апр 2009

я сначала тоже думал что индекс (user_id, id) нужен, но судя по ману, в этом случае будет Tight index scan, что якобы хуже чем Loose index scan, который должен быть при индексе (id, user_id).
Но я это не утверждаю и тесты не гонял.

predator · 16 Апр 2009

спасибо за советы
всё работает вопрос снят

насчёт id, user_id (или наоборот) не делал т.к. убрал из where - and p.id = k.project_id по сему это уже не актуально как я понимаю

ещё раз спасибо )

Wicked · 16 Апр 2009

1)

насчёт id, user_id (или наоборот) не делал т.к. убрал из where - and p.id = k.project_id по сему это уже не актуально как я понимаю

тока вот это самое условие тут не причем

просто

FROM hct_projects_tb p
LEFT JOIN hct_keywords_projects_tb k ON p.id = k.project_id
WHERE p.id = k.project_id

по факту то же самое, что

FROM hct_projects_tb p
INNER JOIN hct_keywords_projects_tb k ON p.id = k.project_id

2) GROUP BY p.id вроде должен использовать индекс (p.user_id, p.id), потому что user_id = const, но если ТС все время будет кланяться и благодарить, то мы так и не узнаем, правда это или нет. Потому что ставить эксперимент мне лень

Gas · 16 Апр 2009

Wicked
по логике в данном случае действительно (p.user_id, p.id) должен быть намного лучше, не птому что const, а потому что количество групп при индексе (p.id, p.user_id) будет равно количеству записей, про это я как-то не подумал

Ну а какой индекс будет лучше не в таком частном случае - мне тестить тоже лень

predator · 16 Апр 2009

Wicked

1) по факту тоже самое но Mysql оптимизатор смотрит то что в ON и то что в WHERE отдельно - поэтому в данном случае этот индекс (по id, user_id) ПМСМ использоваться не будет (проверять лень - но как-то я в этом разбирался, правда было это давно ))

2) GROUP BY p.id будет использовать индекс p.id

можно конечно отключать оптимизатор и всё такое )

но главное сервер перестал виснуть - этого достаточно )

Gas · 16 Апр 2009

predator
мы с Wicked'ом тебе советовали добиться ситуации, когда таблица p будет первой в explain'е, чтоб попробовать исключить "Using temporary; Using filesort" из-за наличия group by. Возможно это даст хороший прирост в скорости (а может и нет), но как говорится, хозяин - барин.

Wicked · 17 Апр 2009

Gas

когда таблица p будет первой в explain'е, чтоб попробовать исключить "Using temporary; Using filesort" из-за наличия group by.

рад, что мы друг друга все-таки поняли

а теперь о деталях:
ну да, индекс будет лучше не потому что конст, но и не потому, что кол-во групп равно кол-ву записей тоже

индекс будет лучше, потому что он сможет быть очень хорошо использован и для where, и для группировки одновременно.

вот пример: есть таблица с 5к записями пользователей, каждый из которых относится к какому-то магазину (belong_to_store_id). Кол-во пользователей для store_id = 47 равно 7. Есть индекс на (belong_to_store_id, email).

Код:

mysql> explain select * from p where belong_to_store_id = 47 group by email;
+----+-------------+-------+------+---------------------------------------------------+----------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys                                     | key            | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------------------------------------------+----------------+---------+-------+------+-------------+
|  1 | SIMPLE      | p     | ref  | *********_belong_to_store_id_index,store_id_email | store_id_email | 5       | const |    7 | Using where |
+----+-------------+-------+------+---------------------------------------------------+----------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

А такого индекса нету:

Код:

mysql> explain select * from p where belong_to_store_id = 47 group by login;
+----+-------------+-------+------+---------------------------------------------------+----------------+---------+-------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys                                     | key            | key_len | ref   | rows | Extra                                        |
+----+-------------+-------+------+---------------------------------------------------+----------------+---------+-------+------+----------------------------------------------+
|  1 | SIMPLE      | p     | ref  | *********_belong_to_store_id_index,store_id_email | store_id_email | 5       | const |    7 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------+---------------------------------------------------+----------------+---------+-------+------+----------------------------------------------+
1 row in set (0.00 sec)

-~{}~ 17.04.09 08:34:

predator

1) по факту тоже самое но Mysql оптимизатор смотрит то что в ON и то что в WHERE отдельно - поэтому в данном случае этот индекс (по id, user_id) ПМСМ использоваться не будет (проверять лень - но как-то я в этом разбирался, правда было это давно ))

2) GROUP BY p.id будет использовать индекс p.id

можно конечно отключать оптимизатор и всё такое )

что за бред?

в 1) я про индексы даже не говорил. Я говорил про то, что что-то там явно лишнее. Но просто так эту часть where без замены left join на inner join, как правило, убрать нельзя. Ну т.е. можно, но результаты запроса могут измениться.

2) думаешь, MySQL сама не разберется, какой индекс ей лучше? Вот что-то мне подсказывает, что отдельный индекс p.id она даже в рассмотрение брать не будет.

Вообщем, все, что мне от тебя нужно - это добавить _составной_ индекс по (p.user_id, p.id) и показать explain твоего запроса.

оптимизация запроса

predator

web designer

Wicked

Новичок

predator

web designer

Gas

может по одной?

Wicked

Новичок

Gas

может по одной?

predator

web designer

Wicked

Новичок

Gas

может по одной?

predator

web designer

Gas

может по одной?

Wicked

Новичок