Simple query not using index
I have a simple query that only uses its index *sometimes*. The desired query is the first one I explain below. The strange thing is that the second query below uses the right index when I use a different value for user_id. The third query also uses an index if I remove the order by. I could always force the index, but certainly with such a simple query I shouldn't have to. This is with mysql-5.1.36-xtradb6 (and thus innodb plugin 1.0.3). Anyone have any ideas?
mysql> explain SELECT SQL_NO_CACHE * FROM pictures WHERE pictures.user_id = 1262761 ORDER BY pictures.id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: pictures
type: index
possible_keys: index_pictures_on_user_id,index_pictures_on_user_id_and_is_private
key: PRIMARY
key_len: 4
ref: NULL
rows: 6161948
Extra: Using where
1 row in set (0.00 sec)
mysql> explain SELECT SQL_NO_CACHE * FROM pictures WHERE pictures.user_id = 7 ORDER BY pictures.id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: pictures
type: ref
possible_keys: index_pictures_on_user_id,index_pictures_on_user_id_and_is_private
key: index_pictures_on_user_id
key_len: 5
ref: const
rows: 302
Extra: Using where
1 row in set (0.00 sec)
mysql> explain SELECT SQL_NO_CACHE * FROM pictures WHERE pictures.user_id = 1262761\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: pictures
type: ref
possible_keys: index_pictures_on_user_id,index_pictures_on_user_id_and_is_private
key: index_pictures_on_user_id_and_is_private
key_len: 5
ref: const
rows: 6
Extra: Using where
1 row in set (0.00 sec)
mysql> show create table pictures\G
*************************** 1. row ***************************
Table: pictures
Create Table: CREATE TABLE `pictures` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
`content_type` varchar(255) DEFAULT NULL,
`filename` varchar(255) DEFAULT NULL,
`thumbnail` varchar(255) DEFAULT NULL,
`size` int(11) DEFAULT NULL,
`width` int(11) DEFAULT NULL,
`height` int(11) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`is_private` tinyint(1) DEFAULT '0',
`description` varchar(255) DEFAULT NULL,
`updated_at` datetime NOT NULL DEFAULT '2007-11-29 00:00:00',
`file_hash` varchar(255) DEFAULT NULL,
`flooding` tinyint(1) DEFAULT '0',
`status` int(11) DEFAULT NULL,
`date_time_original` datetime DEFAULT NULL,
`view_count` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `index_pictures_on_parent_id` (`parent_id`),
KEY `index_pictures_on_user_id` (`user_id`),
KEY `index_pictures_on_is_private` (`is_private`),
KEY `index_pictures_on_created_at` (`created_at`),
KEY `index_pictures_on_flooding` (`flooding`),
KEY `index_pictures_on_status` (`status`),
KEY `index_pictures_on_status_and_is_private_and_flooding` (`status`,`is_private`,`flooding`),
KEY `index_pictures_on_user_id_and_is_private` (`user_id`,`is_private`),
KEY `index_pictures_on_updated_at` (`updated_at`),
KEY `index_pictures_on_file_hash` (`file_hash`)
) ENGINE=InnoDB AUTO_INCREMENT=10587102 DEFAULT CHARSET=utf8