Bill Karwin 寫道：

> howachen@gmail.com wrote:
> >>> i don't understand how index on FK will improve performane, IF ONLY ONE
> >>> (i.e. PK) index can be used at a time
>
> MySQL can use one index per table. Your example named table "a" and
> table "b". So it may use the primary key index from table "a" and the
> foreign key index from table "b".
>
> That's one index per table. If you have two tables, it may use two indexes.
>

thanks for your reply first...but the point is:

1. when joining two tables as above, only one index is used in the
whole query execution

my question is : why we need to create additional index on FK if MySQL
only can either use index PK or FK? why not force them to use PK (a.id)
and drop the index on b.fid (so as to save space and improve
insert/update performance)

thanks...

howachen@gmail.com wrote:
> Bill Karwin 寫道：
>
>
>>howachen@gmail.com wrote:
>>
>>>>>i don't understand how index on FK will improve performane, IF ONLY ONE
>>>>>(i.e. PK) index can be used at a time
>>
>>MySQL can use one index per table. Your example named table "a" and
>>table "b". So it may use the primary key index from table "a" and the
>>foreign key index from table "b".
>>
>>That's one index per table. If you have two tables, it may use two indexes.
>>
>
>
> thanks for your reply first...but the point is:
>
> 1. when joining two tables as above, only one index is used in the
> whole query execution
>
> my question is : why we need to create additional index on FK if MySQL
> only can either use index PK or FK? why not force them to use PK (a.id)
> and drop the index on b.fid (so as to save space and improve
> insert/update performance)
>
> thanks...
>

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@attglobal.net
==================

Jerry Stuckle 寫道：

> howachen@gmail.com wrote:
> > Bill Karwin 寫道：
> >
> >
> >>howachen@gmail.com wrote:
> >>
> >>>>>i don't understand how index on FK will improve performane, IF ONLY ONE
> >>>>>(i.e. PK) index can be used at a time
> >>
> >>MySQL can use one index per table. Your example named table "a" and
> >>table "b". So it may use the primary key index from table "a" and the
> >>foreign key index from table "b".
> >>
> >>That's one index per table. If you have two tables, it may use two indexes.
> >>
> >
> >
> > thanks for your reply first...but the point is:
> >
> > 1. when joining two tables as above, only one index is used in the
> > whole query execution
> >
> > my question is : why we need to create additional index on FK if MySQL
> > only can either use index PK or FK? why not force them to use PK (a.id)
> > and drop the index on b.fid (so as to save space and improve
> > insert/update performance)
> >
> > thanks...
> >
>
> 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@attglobal.net
> ==================


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)

On 19 Jul 2006 07:14:40 -0700, howachen@gmail.com wrote:
> 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)

fid is not, formally and for purposes of this discussion, then a foreign
key at all. It's just a column that happens to have some values that
have been set to correspond to some other values in another table.

--
Time is a great teacher, but unfortunately it kills all its pupils.
-- Hector Berlioz

howachen@gmail.com wrote:
> Jerry Stuckle 寫道：
>
>
>>howachen@gmail.com wrote:
>>
>>>Bill Karwin 寫道：
>>>
>>>
>>>
>>>>howachen@gmail.com wrote:
>>>>
>>>>
>>>>>>>i don't understand how index on FK will improve performane, IF ONLY ONE
>>>>>>>(i.e. PK) index can be used at a time
>>>>
>>>>MySQL can use one index per table. Your example named table "a" and
>>>>table "b". So it may use the primary key index from table "a" and the
>>>>foreign key index from table "b".
>>>>
>>>>That's one index per table. If you have two tables, it may use two indexes.
>>>>
>>>
>>>
>>>thanks for your reply first...but the point is:
>>>
>>>1. when joining two tables as above, only one index is used in the
>>>whole query execution
>>>
>>>my question is : why we need to create additional index on FK if MySQL
>>>only can either use index PK or FK? why not force them to use PK (a.id)
>>>and drop the index on b.fid (so as to save space and improve
>>>insert/update performance)
>>>
>>>thanks...
>>>
>>
>>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@attglobal.net
>>==================
>
>
>
> 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)
>

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@attglobal.net
==================

Jerry Stuckle 寫道：

> howachen@gmail.com wrote:
> > Jerry Stuckle 寫道：
> >
> >
> >>howachen@gmail.com wrote:
> >>
> >>>Bill Karwin 寫道：
> >>>
> >>>
> >>>
> >>>>howachen@gmail.com wrote:
> >>>>
> >>>>
> >>>>>>>i don't understand how index on FK will improve performane, IF ONLY ONE
> >>>>>>>(i.e. PK) index can be used at a time
> >>>>
> >>>>MySQL can use one index per table. Your example named table "a" and
> >>>>table "b". So it may use the primary key index from table "a" and the
> >>>>foreign key index from table "b".
> >>>>
> >>>>That's one index per table. If you have two tables, it may use two indexes.
> >>>>
> >>>
> >>>
> >>>thanks for your reply first...but the point is:
> >>>
> >>>1. when joining two tables as above, only one index is used in the
> >>>whole query execution
> >>>
> >>>my question is : why we need to create additional index on FK if MySQL
> >>>only can either use index PK or FK? why not force them to use PK (a.id)
> >>>and drop the index on b.fid (so as to save space and improve
> >>>insert/update performance)
> >>>
> >>>thanks...
> >>>
> >>
> >>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@attglobal.net
> >>==================
> >
> >
> >
> > 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)
> >
>
> 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.
>
>

i have used the "FORCE INDEX(fid)"

why table scan still occur?

howachen@gmail.com wrote:
> Jerry Stuckle 寫道：
>
>
>>howachen@gmail.com wrote:
>>
>>>Jerry Stuckle 寫道：
>>>
>>>
>>>
>>>>howachen@gmail.com wrote:
>>>>
>>>>
>>>>>Bill Karwin 寫道：
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>howachen@gmail.com wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>>>>i don't understand how index on FK will improve performane, IF ONLY ONE
>>>>>>>>>(i.e. PK) index can be used at a time
>>>>>>
>>>>>>MySQL can use one index per table. Your example named table "a" and
>>>>>>table "b". So it may use the primary key index from table "a" and the
>>>>>>foreign key index from table "b".
>>>>>>
>>>>>>That's one index per table. If you have two tables, it may use two indexes.
>>>>>>
>>>>>
>>>>>
>>>>>thanks for your reply first...but the point is:
>>>>>
>>>>>1. when joining two tables as above, only one index is used in the
>>>>>whole query execution
>>>>>
>>>>>my question is : why we need to create additional index on FK if MySQL
>>>>>only can either use index PK or FK? why not force them to use PK (a.id)
>>>>>and drop the index on b.fid (so as to save space and improve
>>>>>insert/update performance)
>>>>>
>>>>>thanks...
>>>>>
>>>>
>>>>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@attglobal.net
>>>>==================
>>>
>>>
>>>
>>>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)
>>>
>>
>>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.
>>
>>
>
>
> i have used the "FORCE INDEX(fid)"
>
> why table scan still occur?
>

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@attglobal.net
==================

Jerry Stuckle 寫道：

> howachen@gmail.com wrote:
> > Jerry Stuckle 寫道：
> >
> >
> >>howachen@gmail.com wrote:
> >>
> >>>Jerry Stuckle 寫道：
> >>>
> >>>
> >>>
> >>>>howachen@gmail.com wrote:
> >>>>
> >>>>
> >>>>>Bill Karwin 寫道：
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>>howachen@gmail.com wrote:
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>>>>>i don't understand how index on FK will improve performane, IF ONLY ONE
> >>>>>>>>>(i.e. PK) index can be used at a time
> >>>>>>
> >>>>>>MySQL can use one index per table. Your example named table "a" and
> >>>>>>table "b". So it may use the primary key index from table "a" and the
> >>>>>>foreign key index from table "b".
> >>>>>>
> >>>>>>That's one index per table. If you have two tables, it may use twoindexes.
> >>>>>>
> >>>>>
> >>>>>
> >>>>>thanks for your reply first...but the point is:
> >>>>>
> >>>>>1. when joining two tables as above, only one index is used in the
> >>>>>whole query execution
> >>>>>
> >>>>>my question is : why we need to create additional index on FK if MySQL
> >>>>>only can either use index PK or FK? why not force them to use PK (a.id)
> >>>>>and drop the index on b.fid (so as to save space and improve
> >>>>>insert/update performance)
> >>>>>
> >>>>>thanks...
> >>>>>
> >>>>
> >>>>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 youdo
> >>>>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@attglobal.net
> >>>>==================
> >>>
> >>>
> >>>
> >>>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)
> >>>
> >>
> >>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.
> >>
> >>
> >
> >
> > i have used the "FORCE INDEX(fid)"
> >
> > why table scan still occur?
> >
>
> 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.
>

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@gmail.com wrote:
> Jerry Stuckle 寫道：
>
>> howachen@gmail.com wrote:
>>> Jerry Stuckle 寫道：
>>>
>>>
>>>> howachen@gmail.com wrote:
>>>>
>>>>> Jerry Stuckle 寫道：
>>>>>
>>>>>
>>>>>
>>>>>> howachen@gmail.com wrote:
>>>>>>
>>>>>>
>>>>>>> Bill Karwin 寫道：
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>> howachen@gmail.com wrote:
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>>>> i don't understand how index on FK will improve performane, IF ONLY ONE
>>>>>>>>>>> (i.e. PK) index can be used at a time
>>>>>>>> MySQL can use one index per table. Your example named table "a" and
>>>>>>>> table "b". So it may use the primary key index from table "a" and the
>>>>>>>> foreign key index from table "b".
>>>>>>>>
>>>>>>>> That's one index per table. If you have two tables, it may use two indexes.
>>>>>>>>
>>>>>>>
>>>>>>> thanks for your reply first...but the point is:
>>>>>>>
>>>>>>> 1. when joining two tables as above, only one index is used in the
>>>>>>> whole query execution
>>>>>>>
>>>>>>> my question is : why we need to create additional index on FK if MySQL
>>>>>>> only can either use index PK or FK? why not force them to use PK (a.id)
>>>>>>> and drop the index on b.fid (so as to save space and improve
>>>>>>> insert/update performance)
>>>>>>>
>>>>>>> thanks...
>>>>>>>
>>>>>> 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@attglobal.net
>>>>>> ==================
>>>>>
>>>>>
>>>>> 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)
>>>>>
>>>> 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.
>>>>
>>>>
>>>
>>> i have used the "FORCE INDEX(fid)"
>>>
>>> why table scan still occur?
>>>
>> 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.
>>
>
> 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?

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