How to hint MySQL which indexes use?

[jy2003]

EXPLAIN SELECT A_id, B_id FROM A, B WHERE B_id =
'\\\\path\subpa th\morepath\001-0466.html' AND A_id = 4 AND B_code=A_code;

Things get complicated when more than one tables are involved.
Let's say I have indexes for A_id, A_code on table A, and an index for B_id
on table B. How can I hint MySQL that which indexes it should use? In the
example above, I expect one index on table A and one index on table B are
actually used. When I looked at it with the EXPLAIN command, I found out
that only the index for B_id is used.

[Siemel Naran]

Re: How to hint MySQL which indexes use?

"jy2003" <jy2003@sbcglob al.net> wrote in message news:zqxvc.7824 9
[color=blue]
> EXPLAIN SELECT A_id, B_id FROM A, B WHERE B_id =
> '\\\\path\subpa th\morepath\001-0466.html' AND A_id = 4 AND B_code=A_code;
>
> Things get complicated when more than one tables are involved.
> Let's say I have indexes for A_id, A_code on table A, and an index for[/color]
B_id[color=blue]
> on table B. How can I hint MySQL that which indexes it should use? In the
> example above, I expect one index on table A and one index on table B are
> actually used. When I looked at it with the EXPLAIN command, I found out
> that only the index for B_id is used.[/color]

Take a look at the concurrent thread "SQL index problem" where there are 2
solutions: using ORDER BY, and MySql use index extensions.

[jy2003]

Re: How to hint MySQL which indexes use?

[color=blue]
> Take a look at the concurrent thread "SQL index problem" where there are 2
> solutions: using ORDER BY, and MySql use index extensions.[/color]

How do you force indexes for more than one table? Let's say index_a for
table A, and index_b for table B. FORCE INDEX(index_a, index_b)?

[Siemel Naran]

Re: How to hint MySQL which indexes use?

"jy2003" <jy2003@sbcglob al.net> wrote in message news:Fszvc.4308
[color=blue]
> How do you force indexes for more than one table? Let's say index_a for
> table A, and index_b for table B. FORCE INDEX(index_a, index_b)?[/color]

In the FROM clause after each table put a force index to specify a list of
possible indexes for that table. So

FROM
A force index(indexes_o f_A, ...)
inner join B on ... force index(...)

[jy2003]

Re: How to hint MySQL which indexes use?

> In the FROM clause after each table put a force index to specify a list of[color=blue]
> possible indexes for that table. So
>
> FROM
> A force index(indexes_o f_A, ...)
> inner join B on ... force index(...)
>[/color]

Can you force index for a DELETE or INSERT query?
For example, I have an index on (Path(255), Col_A, Col_B):
I tried the following, but it didnt' work:
DELETE FROM Document FROCE INDEX(Path(255) , Col_A, Col_B) WHERE
Path='f:\\path\ \subpath\\fiele-00157.htm' AND Col_A=1 AND Col_B=3;

Also, EXPLAIN only works for SELECT query, do we have anything similar for
other queries like INSERT/DELETE?

[Siemel Naran]

Re: How to hint MySQL which indexes use?

"jy2003" <jy2003@sbcglob al.net> wrote in message
news:b3Tvc.7946 1$q25.69531@new ssvr25.news.pro digy.com...
[color=blue]
> Can you force index for a DELETE or INSERT query?
> For example, I have an index on (Path(255), Col_A, Col_B):
> I tried the following, but it didnt' work:
> DELETE FROM Document FROCE INDEX(Path(255) , Col_A, Col_B) WHERE
> Path='f:\\path\ \subpath\\fiele-00157.htm' AND Col_A=1 AND Col_B=3;[/color]

In the FORCE INDEX you give the name of the index, not the column names.

I don't know if it works for delete statements, but I think it should.
[color=blue]
> Also, EXPLAIN only works for SELECT query, do we have anything similar for
> other queries like INSERT/DELETE?[/color]

I don't know. When I use the graphical MySql Control Center, available on
mysql.com, the Explain tab is grayed out for delete statements. Seems like
it should work. After all, one can say delete from ... where exists (select
* from ...).