List:General Discussion« Previous MessageNext Message »
From:Tanner Postert Date:February 21 2008 12:23am
Subject:Optimizer problem?
View as plain text  
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.

Thread
Optimizer problem?Tanner Postert21 Feb
  • Re: Optimizer problem?Baron Schwartz21 Feb