MySQL Performance: MySQL => Multiple-column index not working

Multiple-column index not working [message #1512]

Wed, 04 July 2007 00:46

tomp_gl
Messages: 9
Registered: October 2006

Junior Member

I have been trying to optimize a slow query with indexes, but it won't work and i can't figure out why.

This is the slow query:

# Query_time: 14 Lock_time: 0 Rows_sent: 1 Rows_examined: 15923
SELECT COUNT(id) FROM messages WHERE recipientid=123444 AND status=1;

I have a multiple column index for recipientid and status (in that order) to satisfy the query WHERE syntax. Despite this, the query still takes 14 seconds Sad

messages.recipientid is a MEDIUMINT (7) UNSIGNED NOT NULL
messages.status is a TINYINT (1) UNSIGNED NOT NULL

This is the EXPLAIN results:

id	select_type	table		type	possible_keys 	key 		key_len 	ref 		rows 	Extra
1	SIMPLE		messages 	ref 	recipientid 	recipientid 	4 		const,const 	8352

It seems as though MySQL is not traversing the multiple column index to the status column. I have tried FORCE INDEX (recipientid) but this doesn't help.

Any ideas?

Thanks

[Updated on: Wed, 04 July 2007 00:51]

Report message to a moderator