On 09/12/2009, at 5:10 PM, Jeetendra Ranjan wrote:
Hi,
After analysing slow query log i found that some queries are not
using index and so i used the force index command in query and test
it and now it starts using index properly.Accordingly i implemented
the same query with force index in my application code and
regeneratet the slow query log. Now i found that the same queries
having force index clause are againg not using index and
surprisingly its starting using index without any force index clause.
Please suggest how it happened and should i continue with the force
index command in that query or remove the force index clause from
those queries ?
One of the things to be aware of is that "force index" only forces
the index if the optimizer chooses to use an index. That is, if the
optimizer decides it is better to do a table scan or the join order
changes so the index cannot be used, then it will not use it. It
might be worth trying to do an "EXPLAIN EXTENDED ..." followed by
"SHOW WARNINGS" to see how the optimizer has reorganized the query.
Hope that helps.
- Jesper
Thanks & Regards
Jeetendra Ranjan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org