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
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.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
