Indexes and Left Joins

[Crusader]

Please help me figure out what index to put in for this. I see this in all my slow logs since it doesn't use indexes. It's called on pretty much every page on my forum.

 

SELECT moderator.mid as is_mod, moderator.allow_warn, m.*, g.*
FROM ibf_members m
LEFT JOIN ibf_groups g ON (g.g_id=m.mgroup)
LEFT JOIN ibf_moderators moderator ON (moderator.member_id=m.id OR moderator.group_id=m.mgroup )
WHERE m.id=592;

 

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	m 	const 	PRIMARY,id 	PRIMARY 	3 	const 	1 	 
1 	SIMPLE 	g 	const 	PRIMARY 	PRIMARY 	4 	const 	1 	 
1 	SIMPLE 	moderator 	ALL 	group_id,member_id 	NULL 	NULL 	NULL 	30

 

I can't, for the life of me, figure this out.

[luca200]

I think you actually have indexes on group_id and member_id, otherwise your question would not make a sense.

Try splitting last join in two: the first on member_id and the second on group_id

[fenway]

It won't use proper indexes with an OR in the join condition.