I have the following table with >1M rows:

CREATE TABLE `wishlist_place` (
  `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`)

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


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)?

  • new_places_on_wishlist is redundant because id is primary key, and you use INNODB engine; thus, it's stored with each secondary index anyway (wishlist_place_unique has id implicitly )
    – a1ex07
    May 3, 2013 at 20:27

1 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
  • +1. On another note, the new_places_on_wishlist is redundant as it is essentially (for InnoDB) the same as the wishlist_place_unique (wishlist_id,place_id) index. So, you can remove it. May 18, 2013 at 18:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.