[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql-bugs
Subject:    Re: merge bug
From:       Sergei Golubchik <serg () mysql ! com>
Date:       2003-07-19 18:51:03
[Download RAW message or body]

Hi!

On Jul 20, Yusuke Naito wrote:
> Thank you so much!
> I believed the PRIMARY KEY option not contribute
> the structural comparison.
> The result by adding to children tables PRIMARY KEY
> are successful.
> However, the most of tables in my DB, I could succeeded
> to merge tables without children's PRIMARY KEY.

It's because for some queries MyQL decided ot use an index, so it does
not matter if child tables have it. But if MySQL optimizer decided to
resolve the query by using an index - then MERGE table will try to use
the same index of underlying MyISAM tables, so it won't work if they
don't have one.

>Then I miss
> understood the error is bug. For example, following script
> is successful. Is this bug? I believe this is not bug,
> so that we can merge tables without defining PRIMARY KEY or
> index in those tables. If we can use PRIMARY KEY or index
> by defining in type=merge table only, it is very convenient.

No, it's meaningless. MERGE tables do not create indexes on their own,
having an index in MERGE table only is an indication to optimizer that
it can use this index of underlying MyISAM tables, if it will be
reasonable. So, it's no sense in having an index declared for MERGE
table if it's not present in underlying MyISAM tables.
Opposite situation is possible - if underlying tables have some index
that is not declared for MERGE table, than it won't be an error,
but simply this index will never be used when accessing MERGE table (of
course it can be used if MyISAM tables are accessed in the query
directly).

Regards,
Sergei

-- 
   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <serg@mysql.com>
 / /|_/ / // /\ \/ /_/ / /__  MySQL AB, Senior Software Developer
/_/  /_/\_, /___/\___\_\___/  Osnabrueck, Germany
       <___/  www.mysql.com

-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/bugs?unsub=mysql-bugs@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]