MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Perrin Harkins Date:January 3 2008 10:32pm
Subject:Re: problem forcing indexes
View as plain text  
On Jan 3, 2008 4:23 PM, Tanner Postert <tanner.postert@stripped> wrote:
> When I try to add a force index (PRIMARY) after the media table to try and
> make is use PRIMARY, rather than TYPE, the optimizer switches and uses no
> key at all.

It usually knows better than you do about indexes.

> I've tried to change the order in which the tables are selected,
> but it seems to have no effect.

It should be able to choose the best order most of the time.  You can
force it, but that's nearly always a mistake.

> In some scenarios it will switch and use the
> media_views table, but the rows is still 125,000+ using temporary and
> filesort.

For this relatively small result set, temporary and filesort may not
be a big deal.  They are probably being used to handle your ORDER BY.

> how can I get this query time down?

You can try some combined indexes, like one on media (id, status,
type, created) and one on media_views (media_id, 24h).  I don't think
you can eliminate the temp table with that ORDER BY though.

- Perrin
problem forcing indexesTanner Postert3 Jan
  • Re: problem forcing indexesPerrin Harkins3 Jan