Index on a query with order

Question

I have the following table with >1M rows:

CREATE TABLE `wishlist_place` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `wishlist_id` int(11) DEFAULT NULL,
  `place_id` int(11) DEFAULT NULL,
  `city_id` bigint(20) DEFAULT NULL,
  `created_by_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `wishlist_place_unique` (`wishlist_id`,`place_id`),
  KEY `IDX_20B83B35FB8E54CD` (`wishlist_id`),
  KEY `IDX_20B83B358BAC62AF` (`city_id`),
  KEY `new_places_on_wishlist` (`wishlist_id`,`place_id`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3079689 DEFAULT CHARSET=utf8;

And I'm doing the following query to get the newest places added to that wishlist:

SELECT place_id 
FROM wishlist_place as wp
WHERE wishlist_id = 113 
ORDER BY wp.id desc 
LIMIT 0, 9

I thought the index 'new_places_on_wishlist' would work for this query, but it uses the index 'wishlist_place_unique' instead:

id  select_type table   type    possible_keys                                                       key                     key_len ref     rows    Extra
1   SIMPLE      wp      ref     wishlist_place_unique,IDX_20B83B35FB8E54CD,new_places_on_wishlist   wishlist_place_unique   5       const   28      Using where; Using index; Using filesort

UPDATE

I've noticed that the index that works best is:

KEY `new_places_on_wishlist` (`wishlist_id`,`id`,`place_id`)

but I still have to use the USE INDEX statement:

SELECT place_id 
FROM wishlist_place as wp
USE INDEX(new_places_on_wishlist)
WHERE wishlist_id = 113 
ORDER BY wp.id desc 
LIMIT 0, 9

Why does MySQL takes the UNIQUE index first? The order for the indexes are fields in where, fields in order, fields in select?

--

And another related question, if I do a JOIN with the city table to get the wishlists of that city, is the index IDX_20B83B358BAC62AF the best one or it would be better (city_id, wishlist_id)?

Answer 1

Try switching the index to be:

KEY `new_places_on_wishlist` (`wishlist_id`, `id`, `place_id`)

You are effectively doing the part of order by optimization manual that says it can't use the index because of ordering by non-consecutive parts of the key, although their example is different than your situation.

Basically you are currently doing

SELECT key_part2 FROM foo WHERE key_part1=constant ORDER BY key_part3