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:
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?
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 > 11and check the additional conditions on the WHERE clause. Better to put
topic_idlast 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