Picking Fields in a Multiple-Field Index on MySQL

Question

I have this MySQL query that I'd like to optimize:

SELECT min(`topic_id`) FROM `gallery_topics` where `topic_id` > 11 AND `topic_poster` = 5 AND `topic_status` = 0

I wanted to add an index with multiple fields to optimize this query, so I added a key I named previous_by_author with these three keys in this order:

1. topic_id
2. topic_poster
3. topic_status

I did an EXPLAIN of this query, with the expectation that the EXPLAIN query would tell me that the query uses my new previous_by_author index. However, EXPLAIN reported that the query uses the user_start_time index, which uses the following two fields: 1. topic_poster and 2. topic_start_time.

I don't get it, topic_start_time is a datimetime field that isn't even in my query!

Here's the full result of the EXPLAIN query:

id  select_type  table           type   possible_keys      
1   SIMPLE       gallery_topics ref     PRIMARY,user_start_time,previous_by_author

(rows continued)

key              key_len  ref   rows   Extra
user_start_time  8        const 4      Using where

What fields should I use in my multiple-field index so that my query can take advantage of the best index?

Answer 1

Without knowing what all available indexes are, it's difficult to say why one in particular was chosen. But it's easier to see why your new index wasn't chosen: since you have a greater-than condition on topic_id, and the index stores values sorted first by topic_id, MySQL will have to read in (at least from the index) all rows with topic_id > 11 and check the additional conditions on the WHERE clause. Better to put topic_id last in the multicolumn index, so MySQL will group values in the index by the two equality conditions; then, checking the greater-than condition is easy.

Also, how many rows are in the table? If you don't have representative test data, all bets are off on what the query planner will decide to do.

Summary: for optimizing queries exactly like the one you show, try the index

1. topic_status
2. topic_poster
3. topic_id