I have the following table: -- -- Table structure for table 'media' -- CREATE TABLE media ( id int(10) unsigned NOT NULL auto_increment, user_id int(10) unsigned default NULL, title varchar(255) NOT NULL, description text NOT NULL, `hash` varchar(255) NOT NULL, length float(9,2) NOT NULL, created timestamp NOT NULL default CURRENT_TIMESTAMP, `type` enum('video','image') default NULL, `status` enum('new','processing','suspended','active','deleted','failed','pending') NOT NULL default 'new', flags int(20) NOT NULL, PRIMARY KEY (id), UNIQUE KEY `hash` (`hash`), KEY `type` (`type`), KEY user_id (user_id), KEY created (created), KEY `status` (`status`), KEY flags (flags) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
the table has about 200,000 rows. the following query takes about .56 seconds on a completely empty system: SELECT * FROM media WHERE media.status = 'active' AND user_id = '190' AND id != '13660' AND media.flags & 3 = 0 and media.type = 'video' ORDER BY media.id DESC LIMIT 0, 6 When I do explain, I can see it shows PRIMARY as a viable index to use, but instead its using and index merge with user_id,status,type. when I add "use index (PRIMARY)", the query drops to 0.02. Any ideas why the optimizer isn't using the primary? since i'm ordering by that, it seems it would make sense to use that.