Hi,
On Wed, Feb 20, 2008 at 7:23 PM, Tanner Postert
<[EMAIL PROTECTED]> wrote:
> 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.
As far as I know, the optimizer's cost metric doesn't account for the
extra work caused by merging the index scans, so it probably thinks
it'll be cheaper to do so. I think this is one of the cases where the
human is smarter than the optimizer.
I always try to avoid manually adding hints, but sometimes you have
to. It may be a good idea to check when you upgrade MySQL and
determine if it has gotten smart enough to execute the query faster
than your forced execution plan. Otherwise you might be locking it
into a worse plan than it might be able to use in future versions.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
