Force index command in sql query

------=_NextPart_000_0072_01CA78C4.6C272BB0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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 ?


Thanks & Regards
Jeetendra Ranjan



------=_NextPart_000_0072_01CA78C4.6C272BB0--

Re: Force index command in sql query

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=gcdmg-mysql-2@m.gmane.org