Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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 '13 at 20:27
add comment

1 Answer

up vote 2 down vote accepted

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
share|improve this answer
    
+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. –  ypercube May 18 '13 at 18:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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