From: Michael Widenius
Date: April 30 2001 7:48am
Subject: MERGE Tables
List-Archive: http://lists.mysql.com/internals/742
Message-Id: <15085.6331.127387.468912@narttu.mysql.fi>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


Hi!

>>>>> "Matthew" == Matthew Shaw <mshaw@stripped> writes:

Matthew> Hi,
Matthew> I have only just joined this list today any help would be greatly
Matthew> appreciated.
Matthew> I have implemented the new MERGE table feature as part of my new install
Matthew> on version 3.23.
Matthew> I am finding a few small problems with usage of this functionality as
Matthew> follows.

Matthew> There are 4 identical tables namely asic_cache1, asic_cache2,
Matthew> asic_cache3, asic_cache4. Each table is structured identically as
Matthew> follows:

Matthew>  field_index                         int(11)            PRI  DEFAULT 0
Matthew>  subject                               varchar(16)        PRI
Matthew>  search_type                     varchar(8)         PRI
Matthew>  segment                            char(3)
Matthew>  grp_code                           int(11)     DEFAULT  0
Matthew>  field_nr                               int(11)      DEFAULT 0
Matthew>  value                                  text          DEFAULT NULL
Matthew>  segment_header             int(1)       DEFAULT 0
Matthew>  record_nr                          int(11)     DEFAULT 0

Matthew> There is one index on each table and that is the primary key (subject,
Matthew> search_type, field_index).
Matthew> Each table contains approximately 20 000 000 rows. Hence my reason for
Matthew> using merge :)
Matthew> The merge table created is called asic_cache. It has exactly the same
Matthew> structure as above except there are no keys what so ever
Matthew>. My
Matthew> understanding is that there is no need for keys on the merge
Matthew> table as the table handler uses the keys on each individual table when
Matthew> doing the lookups. The keys on each table are of type ref.

Sorry, but the above is the wrong assumption.  You MUST define the
exact keys for the MERGE table as you have for each individual tables.

Matthew> Here's the interesting part:
Matthew> I performed the following query and got a result in 0.2 secs approx.
Matthew> Qry = select * from asic_cache where subject = '10920411' and
Matthew> search_type = 'CE C' limit 10.

Matthew> When I run the following query without the limit clause mysql appears to
Matthew> go into a tail spin and the process runs on my linux machine for over 5

Matthew> minutes!
Matthew> Qry = select * from asic_cache where subject = '10920411' and
Matthew> search_type = 'CE C'.
Matthew> I am guessing that it is doing a full table scan on each of the four
Matthew> table but I'm not sure and if so why?

See above.

Matthew> Thirdly, the following query has the same effect as above and blows the
Matthew> time of the query over 5 mintues.
Matthew> Qry = select * from asic_cache where subject = '10920411' and
Matthew> search_type = 'CE C'
Matthew> order by subject, search_type, field_index.

Note that you can always check the used indexes with the EXPLAIN command.

Matthew> Looking at the mysql doco on merge tables on one hand it says there is
Matthew> no need for indexes on the merge table itself but in the example in the
Matthew> doco it uses a key? This has got me really confused and I'm not sure if
Matthew> this is contributing to the above issues. Has anyone got any ideas on
Matthew> the
Matthew> matter??

I will check the documentation. It should say that MySQL will not
create a separate index tree for the MERGE tables but will use the
table indexes.  One must however always define the keys when creating the
MERGE table.

Regards,
Monty