поиск всех сообщений пользователя на большой базе

[Blood Angel]

есть функция search_get_all_user_query (находит все сообщения пользователя)

 

SELECT p.*, t.*, t.posts as topic_posts, t.title as topic_title, m.*, me.*
FROM ibf_posts p
LEFT JOIN ibf_topic t ON (p.topic_id=t.tid)
LEFT JOIN ibf_members m ON (m.id=p.author_id)
LEFT JOIN ibf_member_extra me ON (me.id=p.author_id)
WHERE p.queued=0 AND t.forum_id IN({$a['forums']}) AND p.author_id={$a['mid']}
order by post_date DESC;

 

где вместо а подставляется список форумов доступных пользователю и id пользователя соответственно.

есть форум на котором 9 тысяч тем и 6 миллионов сообщений.

в реалии селект выглядит так

SELECT p.*, t.*, t.posts as topic_posts, t.title as topic_title, m.*, me.*
FROM ibf_posts p
LEFT JOIN ibf_topics t ON (p.topic_id=t.tid)
LEFT JOIN ibf_members m ON (m.id=p.author_id)
LEFT JOIN ibf_member_extra me ON (me.id=p.author_id)
WHERE p.queued=0 AND t.forum_id IN(43,45,42,44,48,35,36,46,47,52,23,33,70,66,32,34,56,60,67,50,58,59,57,25,49,5
5,69,63,6,7,61,2,3,4,5,71,12,13,51,8,19,11,9,17,18,14,16,15,10,20,38,39,26,27,28,
64,65,68,53,54) AND p.author_id=1361
order by post_date desc

у этого пользователя 2,5 тысячи сообщений и эксплейн на него такой

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	p	ref	topic_id,author_id	author_id	4	const	2620	Using where; Using filesort
1	SIMPLE	m	const	PRIMARY	PRIMARY	8	const	1	
1	SIMPLE	me	const	PRIMARY	PRIMARY	8	const	1	
1	SIMPLE	t	eq_ref	PRIMARY,forum_id	PRIMARY	8	p.topic_id	1	Using where

а у этого пользователя уже 9 тысяч сообщений

SELECT p.*, t.*, t.posts as topic_posts, t.title as topic_title, m.*, me.*
FROM ibf_posts p
LEFT JOIN ibf_topics t ON (p.topic_id=t.tid)
LEFT JOIN ibf_members m ON (m.id=p.author_id)
LEFT JOIN ibf_member_extra me ON (me.id=p.author_id)
WHERE p.queued=0 AND t.forum_id IN(43,45,42,44,48,35,36,46,47,52,23,33,70,66,32,34,56,60,67,50,58,59,57,25,49,5
5,69,63,6,7,61,2,3,4,5,71,12,13,51,8,19,11,9,17,18,14,16,15,10,20,38,39,26,27,28,
64,65,68,53,54) AND p.author_id=6634
order by post_date desc

и эксплейн выглядит по другому

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t	ALL	PRIMARY,forum_id	-	-	-	7973	Using where; Using temporary; Using filesort
1	SIMPLE	p	ref	topic_id,author_id	topic_id	9	t.tid,const	1	Using where
1	SIMPLE	m	const	PRIMARY	PRIMARY	8	const	1	
1	SIMPLE	me	const	PRIMARY	PRIMARY	8	const	1

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

какие-нибудь мысли будут?

 

p.s. этот селект я конечно потом переписал, если не возникнет идей никаких, то покажу как. но меня пугают разные эксплейны.

[FatCat]

во время работы такое ощущение, что делается фуллскан таблички с постами

Так и есть, баг разработчиков MySQL, еще с год назад отправляли им в багрепорт, но пока тишина.

 

А запрос лучше разбить на несколько, чтобы обойти бажную конструкцию JOIN ... WHERE ... IN(...)

Лучше пусть будет число запросов с джойнами по числу постов на странице, при указании единственного айдишника они будут выполняться буквально по 0.001 секунде каждый и даже 50 штук выполнятся влёт.

[Blood Angel]

во время работы такое ощущение, что делается фуллскан таблички с постами

Так и есть, баг разработчиков MySQL, еще с год назад отправляли им в багрепорт, но пока тишина.

 

А запрос лучше разбить на несколько, чтобы обойти бажную конструкцию JOIN ... WHERE ... IN(...)

Лучше пусть будет число запросов с джойнами по числу постов на странице, при указании единственного айдишника они будут выполняться буквально по 0.001 секунде каждый и даже 50 штук выполнятся влёт.

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

я сделал проще, сначала селект * со всеми джоинами, а потом select * IN

какой айдишник указывать? мы пытаемся найти все это и отсортировать, прежде чем тыкать айдишниками..

 

и еще в тройке изменили ли данную конструкцию? исходники 2.3.6 говорят, что нет.

Изменено 12 Апреля 2010 пользователем Blood Angel

[Song]

Это не проблема запроса, запрос написан правильно. Почему-то СУБД оценивает неправильно.

Можно в запросе указать насильно какой индекс ему использовать.

Может индексы надо починить или перестроить, где-то неправильна статистика.

[Blood Angel]

Это не проблема запроса, запрос написан правильно. Почему-то СУБД оценивает неправильно.

Можно в запросе указать насильно какой индекс ему использовать.

Может индексы надо починить или перестроить, где-то неправильна статистика.

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