Professional Web Applications Themes

Index question... - MySQL

Hi, We always have query in the following form... e.g. SELECT * FROM a,b where a.id = b.fid Should we add index to both a.id and b.fid? Thanks......

  1. #1

    Default Index question...

    Hi,

    We always have query in the following form...

    e.g.

    SELECT * FROM a,b where a.id = b.fid

    Should we add index to both a.id and b.fid?

    Thanks...

    howachen@gmail.com Guest

  2. #2

    Default Re: Index question...

    [email]howachen[/email] wrote:
    > SELECT * FROM a,b where a.id = b.fid
    >
    > Should we add index to both a.id and b.fid?
    If a.id and b.fid are primary key and foreign key, respectively, they
    already have indexes.

    If not, then yes, creating indexes would be good. Do you know how to
    use the EXPLAIN yzer to tell you what indexes are being used by a
    given query?

    See [url]http://dev.mysql.com/doc/refman/5.0/en/explain.html[/url]

    Regards,
    Bill K.
    Bill Karwin Guest

  3. #3

    Default Re: Index question...


    Bill Karwin 寫道:
    > [email]howachen[/email] wrote:
    > > SELECT * FROM a,b where a.id = b.fid
    > >
    > > Should we add index to both a.id and b.fid?
    >
    > If a.id and b.fid are primary key and foreign key, respectively, they
    > already have indexes.
    >
    > If not, then yes, creating indexes would be good. Do you know how to
    > use the EXPLAIN yzer to tell you what indexes are being used by a
    > given query?
    >
    > See [url]http://dev.mysql.com/doc/refman/5.0/en/explain.html[/url]
    >
    > Regards,
    > Bill K.
    1. i am using MyISAM, not InnoDB, so no foreign key.

    2. if both indexes are added, under explain statement, i see only one
    index is used at a time. the optimizer seems will select the best index
    to use?

    howachen@gmail.com Guest

  4. #4

    Default Re: Index question...

    [email]howachen[/email] wrote:
    > 1. i am using MyISAM, not InnoDB, so no foreign key.
    Aha. Then you should create an index explicitly on b.fid.
    > 2. if both indexes are added, under explain statement, i see only one
    > index is used at a time. the optimizer seems will select the best index
    > to use?
    MySQL may use one index per table in a query. But the optimizer may
    judge in some cases, that reading the index into memory is more
    expensive than just reading the rows of data.

    Regards,
    Bill K.
    Bill Karwin Guest

  5. #5

    Default Re: Index question...


    Bill Karwin 寫道:
    > [email]howachen[/email] wrote:
    > > 1. i am using MyISAM, not InnoDB, so no foreign key.
    >
    > Aha. Then you should create an index explicitly on b.fid.
    i heard that index on foreign key is needed only if you have foreign
    key, otherwise, there is no need to, is it true?
    >
    > > 2. if both indexes are added, under explain statement, i see only one
    > > index is used at a time. the optimizer seems will select the best index
    > > to use?
    >
    > MySQL may use one index per table in a query. But the optimizer may
    > judge in some cases, that reading the index into memory is more
    > expensive than just reading the rows of data.
    >
    what does it mean?
    > Regards,
    > Bill K.
    howachen@gmail.com Guest

  6. #6

    Default Re: Index question...

    [email]howachen[/email] wrote:
    > Bill Karwin 寫道:
    >
    >
    >>howachen wrote:
    >>
    >>>1. i am using MyISAM, not InnoDB, so no foreign key.
    >>
    >>Aha. Then you should create an index explicitly on b.fid.
    >
    >
    > i heard that index on foreign key is needed only if you have foreign
    > key, otherwise, there is no need to, is it true?
    >
    It's only REQUIRED if you have a foreign key. Whether it's NEEDED or
    not depends on the database, number of records in the database, queries
    made to the database, how much performance hit you can stand and about
    50 other variables.

    But yes, usually an index on a foreign key can help performance, often
    significantly for big tables.

    >
    >>>2. if both indexes are added, under explain statement, i see only one
    >>>index is used at a time. the optimizer seems will select the best index
    >>>to use?
    >>
    >>MySQL may use one index per table in a query. But the optimizer may
    >>judge in some cases, that reading the index into memory is more
    >>expensive than just reading the rows of data.
    >>
    >
    >
    > what does it mean?
    >
    It means the optimizer decided it was faster to read the data directly
    from the table than to read in the index then access the table through
    the pointers in the index. This most often occurs when you only have a
    few rows in a table (but a table scan can also occur for other reason).
    >
    >>Regards,
    >>Bill K.
    >
    >

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  7. #7

    Default Re: Index question...


    Jerry Stuckle 寫道:
    > [email]howachen[/email] wrote:
    > > Bill Karwin 寫道:
    > >
    > >
    > >>howachen wrote:
    > >>
    > >>>1. i am using MyISAM, not InnoDB, so no foreign key.
    > >>
    > >>Aha. Then you should create an index explicitly on b.fid.
    > >
    > >
    > > i heard that index on foreign key is needed only if you have foreign
    > > key, otherwise, there is no need to, is it true?
    > >
    >
    > It's only REQUIRED if you have a foreign key. Whether it's NEEDED or
    > not depends on the database, number of records in the database, queries
    > made to the database, how much performance hit you can stand and about
    > 50 other variables.
    >
    > But yes, usually an index on a foreign key can help performance, often
    > significantly for big tables.
    >
    when joining a table, IF ONLY ONE index can be used (i.e. primary key),
    then add another index to the FK is useless, no matter the table size
    (in fact, it will affect the performance of upadating/inserting)

    i don't understand how index on FK will improve performane, IF ONLY ONE
    (i.e. PK) index can be used at a time
    >
    > >
    > >>>2. if both indexes are added, under explain statement, i see only one
    > >>>index is used at a time. the optimizer seems will select the best index
    > >>>to use?
    > >>
    > >>MySQL may use one index per table in a query. But the optimizer may
    > >>judge in some cases, that reading the index into memory is more
    > >>expensive than just reading the rows of data.
    > >>
    > >
    > >
    > > what does it mean?
    > >
    >
    > It means the optimizer decided it was faster to read the data directly
    > from the table than to read in the index then access the table through
    > the pointers in the index. This most often occurs when you only have a
    > few rows in a table (but a table scan can also occur for other reason).
    >
    Thanks

    howachen@gmail.com Guest

  8. #8

    Default Re: Index question...


    Peter H. Coffin 寫道:
    > On 18 Jul 2006 20:01:44 -0700, [email]howachen[/email] wrote:
    > >
    > > Jerry Stuckle ???
    > >
    > >> [email]howachen[/email] wrote:
    > >> > Bill Karwin ???
    > >> >
    > >> >
    > >> >>howachen wrote:
    > >> >>
    > >> >>>1. i am using MyISAM, not InnoDB, so no foreign key.
    > >> >>
    > >> >>Aha. Then you should create an index explicitly on b.fid.
    > >> >
    > >> >
    > >> > i heard that index on foreign key is needed only if you have foreign
    > >> > key, otherwise, there is no need to, is it true?
    > >> >
    > >>
    > >> It's only REQUIRED if you have a foreign key. Whether it's NEEDED or
    > >> not depends on the database, number of records in the database, queries
    > >> made to the database, how much performance hit you can stand and about
    > >> 50 other variables.
    > >>
    > >> But yes, usually an index on a foreign key can help performance, often
    > >> significantly for big tables.
    > >>
    > >
    > > when joining a table, IF ONLY ONE index can be used (i.e. primary key),
    > > then add another index to the FK is useless, no matter the table size
    > > (in fact, it will affect the performance of upadating/inserting)
    > >
    > > i don't understand how index on FK will improve performane, IF ONLY ONE
    > > (i.e. PK) index can be used at a time
    >
    > An index is REQUIRED on a foreign key.
    this is apply to InnoDB or other DB such as Oracle, not apply to MySQL
    ISAM table

    howachen@gmail.com Guest

  9. #9

    Default Re: Index question...

    [email]howachen[/email] 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.
    > this is apply to InnoDB or other DB such as Oracle, not apply to MySQL
    > ISAM table
    We're not talking about Oracle.

    MySQL has a limitation that it uses one index per table. This is true
    whether you use InnoDB or MyISAM.

    Actually, there are cases in MySQL 5.0 where it can merge indexes, and
    so it uses more than one index per table. But only for certain types of
    queries. Read the docs about merge indexes:
    [url]http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html[/url]

    Regards,
    Bill K.
    Bill Karwin Guest

  10. #10

    Default Re: Index question...


    Bill Karwin 寫道:
    > [email]howachen[/email] 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 Guest

  11. #11

    Default Re: Index question...

    [email]howachen[/email] wrote:
    > Bill Karwin 寫道:
    >
    >
    >>howachen 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.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  12. #12

    Default Re: Index question...


    Jerry Stuckle 寫道:
    > [email]howachen[/email] wrote:
    > > Bill Karwin 寫道:
    > >
    > >
    > >>howachen 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.
    > [email]jstucklexattglobal.net[/email]
    > ==================

    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@gmail.com Guest

  13. #13

    Default Re: Index question...

    [email]howachen[/email] wrote:
    > Jerry Stuckle 寫道:
    >
    >
    >>howachen wrote:
    >>
    >>>Bill Karwin 寫道:
    >>>
    >>>
    >>>
    >>>>howachen 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.
    >>jstucklexattglobal.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.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  14. #14

    Default Re: Index question...


    Jerry Stuckle 寫道:
    > [email]howachen[/email] wrote:
    > > Jerry Stuckle 寫道:
    > >
    > >
    > >>howachen wrote:
    > >>
    > >>>Bill Karwin 寫道:
    > >>>
    > >>>
    > >>>
    > >>>>howachen 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.
    > >>jstucklexattglobal.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 Guest

  15. #15

    Default Re: Index question...

    [email]howachen[/email] wrote:
    > Jerry Stuckle 寫道:
    >
    >
    >>howachen wrote:
    >>
    >>>Jerry Stuckle 寫道:
    >>>
    >>>
    >>>
    >>>>howachen wrote:
    >>>>
    >>>>
    >>>>>Bill Karwin 寫道:
    >>>>>
    >>>>>
    >>>>>
    >>>>>
    >>>>>>howachen 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.
    >>>>jstucklexattglobal.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.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  16. #16

    Default Re: Index question...


    Jerry Stuckle 寫道:
    > [email]howachen[/email] wrote:
    > > Jerry Stuckle 寫道:
    > >
    > >
    > >>howachen wrote:
    > >>
    > >>>Jerry Stuckle 寫道:
    > >>>
    > >>>
    > >>>
    > >>>>howachen wrote:
    > >>>>
    > >>>>
    > >>>>>Bill Karwin 寫道:
    > >>>>>
    > >>>>>
    > >>>>>
    > >>>>>
    > >>>>>>howachen 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.
    > >>>>jstucklexattglobal.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 Guest

  17. #17

    Default Re: Index question...

    [email]howachen[/email] wrote:
    > Jerry Stuckle 寫道:
    >
    >> [email]howachen[/email] wrote:
    >>> Jerry Stuckle 寫道:
    >>>
    >>>
    >>>> [email]howachen[/email] wrote:
    >>>>
    >>>>> Jerry Stuckle 寫道:
    >>>>>
    >>>>>
    >>>>>
    >>>>>> [email]howachen[/email] wrote:
    >>>>>>
    >>>>>>
    >>>>>>> Bill Karwin 寫道:
    >>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>>>> [email]howachen[/email] 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.
    >>>>>> [email]jstucklexattglobal.net[/email]
    >>>>>> ==================
    >>>>>
    >>>>>
    >>>>> 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
    Kai Ruhnau Guest

Similar Threads

  1. Question toad: Index utilized for xx%
    By ITAPORT06 in forum Oracle Server
    Replies: 1
    Last Post: July 7th, 01:46 PM
  2. Index question
    By Harry in forum Oracle Server
    Replies: 2
    Last Post: July 2nd, 09:42 AM
  3. Another index question
    By dsb in forum Oracle Server
    Replies: 1
    Last Post: July 1st, 08:46 PM
  4. INDEX ACCESS QUESTION
    By radhika in forum IBM DB2
    Replies: 2
    Last Post: June 26th, 08:05 PM
  5. Index Usage Question
    By Ryan in forum Oracle Server
    Replies: 2
    Last Post: June 24th, 02:55 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139