On 1/24/08, Chris <[EMAIL PROTECTED]> wrote:
>
> mysql mysql wrote:
> > Thanks for the response Chris, although I can't seem to reproduce the
> > problem now, but I'm sure you're right.
> >
> > There's something else strange that I've encountered while trying to
> > optimize this query. I've got two machines, dev and production. After
> > adding the index to the title attribute on the dev machine, my query was
> > reduced from 2 minutes to virtually instantaneous (since the query is
> sorted
> > by title). But when executing the query on the production machine, the
> > query doesn't use the indexed title attribute. Here's what explain
> says:
>
>
> > Now, why is the production machine performing a filesort and including
> ALL
> > the entries? It doesn't seem to be using the indexed title attribute at
> > all. Why would two different machines with the same indexes and schema
> > perform two different queries? Could this be happening because I'm
> running
> > two slightly different versions of mysql?
>
> Normally queries with like '%xxx%' can't be indexes so maybe the
> different version number is playing a part here. Because that string
> ('xxx') can be anywhere in the text, there's no way for the optimizer to
> tell without looking at each row. If you're doing a lot of those sort of
> queries maybe look at full text indexes:
> http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
yeah, I've seen that you can't use an index on like with the wildcard at the
front, but I'm trying to use the index when sorting, not on the where
condition..
Do you have the same data or at least the same amount of data in your
> dev machine? If you don't, you can't compare because the optimizer will
> do different things based on the type & amount of data.
>
> eg adding 3 rows to a table is useless and you'll never notice a
> problem. When you add say 30,000 or 300,000 rows - then you'll really
> notice it.
>
yeah, almost the exact same dataset. Both around 800,000 rows.
I guess the only way to narrow this down is to upgrade both machines to a
more recent version of mysql and see if the problem persists.. Thanks for
the response
