| Multiple-column index not working [message #1512] |
Wed, 04 July 2007 00:46  |
 tomp_gl
Messages: 6
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 
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]
|
|
|
| Re: Multiple-column index not working [message #1514 is a reply to message #1512 ] |
Wed, 04 July 2007 10:38  |
Speeple
Messages: 91
Registered: August 2006 |
Member |
|
|
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.
Martin Gallagher | Speeple: The latest news
|
|
|
| Re: Multiple-column index not working [message #1517 is a reply to message #1512 ] |
Thu, 05 July 2007 05:58 |
sterin
Messages: 323
Registered: March 2007
Location: Sweden |
Senior Member |
|
|
Do you have a special reason why you are counting nr of ID?
| tomp_gl wrote on Wed, 04 July 2007 06:46 |
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.
|
|
|
Members
Search
Help
Register
Login
Home
