Hi,
I have already posted this message but I got no reply I am really stuck
on this topic - if anybody knows anything help would be greatly
appreciated.
I have only just joined this list today any help would be greatly
appreciated.
I have implemented the new MERGE table feature as part of my new install
on version 3.23.
I am finding a few small problems with usage of this functionality as
follows.
There are 4 identical tables namely asic_cache1, asic_cache2,
asic_cache3, asic_cache4. Each table is structured identically as
follows:
field_index int(11)PRI DEFAULT 0
subject varchar(16)PRI
search_type varchar(8) PRI
segmentchar(3)
grp_code int(11) DEFAULT 0
field_nr int(11) DEFAULT 0
value text DEFAULT NULL
segment_header int(1) DEFAULT 0
record_nr int(11) DEFAULT 0
There is one index on each table and that is the primary key (subject,
search_type, field_index).
Each table contains approximately 20 000 000 rows. Hence my reason for
using merge :)
The merge table created is called asic_cache. It has exactly the same
structure as above except there are no keys what so ever. My
understanding is that there is no need for keys on the merge
table as the table handler uses the keys on each individual table when
doing the lookups. The keys on each table are of type ref.
Here's the interesting part:
I performed the following query and got a result in 0.2 secs approx.
Qry = select * from asic_cache where subject = '10920411' and
search_type = 'CE C' limit 10.
When I run the following query without the limit clause mysql appears to
go into a tail spin and the process runs on my linux machine for over 5
minutes!
Qry = select * from asic_cache where subject = '10920411' and
search_type = 'CE C'.
I am guessing that it is doing a full table scan on each of the four
table but I'm not sure and if so why?
Thirdly, the following query has the same effect as above and blows the
time of the query over 5 mintues.
Qry = select * from asic_cache where subject = '10920411' and
search_type = 'CE C'
order by subject, search_type, field_index.
Looking at the mysql doco on merge tables on one hand it says there is
no need for indexes on the merge table itself but in the example in the
doco it uses a key? This has got me really confused and I'm not sure if
this is contributing to the above issues. Has anyone got any ideas on
the
matter??
Thanks in advance,
Matthew.
--
Matthew Shaw
IT Specialist / Project Leader
Australian Business Research (ABR)
Ph. (07) 3837 1391
Mob. 0402 894 797
Email. [EMAIL PROTECTED]
