Multiple-column index not working

MySQL & MariaDB Other MySQL® Questions
1

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 (

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 Extra1 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

2

I’ve been working on a messaging system recently and found it faster there other way around:

(flag,author)

So in your case try:
INDEX(status,recipientid);

SELECT COUNT(id) FROM messages WHERE status=1 AND recipientid=123444;

Might have some performance gain.

3

Do you have a special reason why you are counting nr of ID?

[B]tomp_gl wrote on Wed, 04 July 2007 06:46[/B]

SELECT COUNT(id) FROM messages WHERE recipientid=123444 AND status=1;

Aren’t you just interested in the number of rows that this query returns?
Because if you change the COUNT(id) to a COUNT(*) I bet that your query will go fast again and that it will use the combined index.

Because the reason that your query is slow is that mysql can use the index to find the matching rows.
BUT since you are selecting a column that is not part of the index it has to perform a lookup in the original table and that means random reads which is very slow.

If you are counting nr of rows ( COUNT(*) ) or if you are counting a column that is part of the index (COUNT(recipientid) it will notice this and use the index both for finding and retrieving the rows. And avoiding the lookups in the table altogether.

So change to COUNT(*) and your query will be fast again.