howachen (AT) gmail (DOT) com wrote:
Howard,

You're missing the point. MySQL CAN use one index PER TABLE. Since you
have two tables, it can use one index in each, or a total of two indexes.

As to why it's only using one index. If you don't have an index on the
foreign key, it obviously can't use that index. But just because you do
have an index doesn't mean MySQL WILL use it. The optimizer may
determine it would be faster to just read the table than to use the
index. This is something it does on the fly

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Jerry Stuckle 寫道：


Hi all, I think I should give a detail example to show my
question...sorry for that first....

DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`fid` INT NOT NULL
) TYPE = myisam;

INSERT INTO `a` ( `id` , `fid` )
VALUES (NULL , '2'), (NULL , '3'), (NULL , '4'), (NULL , '5'), (NULL ,
'6'), (NULL , '7');

DROP TABLE IF EXISTS `b`;
CREATE TABLE `b` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`test` INT NOT NULL
) TYPE = myisam;

INSERT INTO `b` ( `id` , `test` )
VALUES (NULL , '1'), (NULL , '2'), (NULL , '3'), (NULL , '4'), (NULL ,
'5'), (NULL , '6');

ALTER TABLE `a` ADD INDEX ( `fid` )

EXPLAIN SELECT * FROM a FORCE INDEX(fid), b WHERE a.fid = b.id

id select_type table type possible_keys key key_len ref rows
Extra
1 SIMPLE a ALL fid NULL NULL NULL 6
1 SIMPLE b eq_ref PRIMARY PRIMARY 4 test.a.fid 1


Q. the index `fid` is completely useless, we can't have both b.id and
a.fid being used at the same time!
why not drop the a.fid (consider we don't use FK integrity checking
when using MyISAM)

howachen (AT) gmail (DOT) com wrote:
Howard,

As Peter indicated, fid is not defined as a foreign key. But you are
using it for matching.

Obviously, the optimizer in this case determined it would be more
efficient to just scan the table instead of using the index. It is
perfectly valid for it to do so - and in fact, is a good thing. It
means the optimizer is doing its job.

Just because an index exists doesn't mean the optimizer has to use it.
The optimizer is perfectly able to ignore the index if access may be slower.




--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Jerry Stuckle 寫道：

i have used the "FORCE INDEX(fid)"

why table scan still occur?

howachen (AT) gmail (DOT) com wrote:
Probably because "FORCE INDEX" is a recommendation, not an order. But
it also depends on the versions of MySQL you're using.

Try filling the table with 200K rows and see if things change.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Jerry Stuckle 寫道：

accorinding to the doc...

You can also use FORCE INDEX, which acts like USE INDEX (key_list) but
with the addition that a table scan is assumed to be very expensive. In
other words, a table scan is used only if there is no way to use one of
the given indexes to find rows in the table.

USE INDEX is a recommendation, FORCE INDEX is not used ONLY if there is
no way to use one of the given indexes to find rows in the table.

so this also confirmed that fid is useless in joining table?

thanks....

howachen (AT) gmail (DOT) com wrote:
No, plase do what Jerry proposed: Fill both tables with 200,000 entries
and try again. You will notice that many things change, once the
optimizer has to do *real work*.
Also try to fill one table with far less entries than the other table.
For example table a ~4000 Entries and table b ~200,000 Entries and the
other way around. Try distributing fid uneven for example let 150,000
entries from b point to the same entry in a and let only 2 entries from
b point to another one in a.
You will notice, that the optimizer takes different paths through the
joined tables, uses different indexes depending on for example
additional WHERE-conditions.

FORCE INDEX as you quoted from the manual only makes the table scan very
expensive. That does not mean, that MySQL will not use a table scan.
When you work with your little 6-entry table MySQL has the options to a)
read the Index and then read the corresponding data entries or b) read
all data entries (that means with the data used to join).
For your little table a) means two (expensive) read operations and b)
means only one. Which option would you choose?

Kai