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.
