Hi,
If my calculations are correct, your table will grow quite a lot
if you change from the MyISAM format to HEAP. The space to hold
the data will be roughly 1.3GB (a double of what it's with MyISAM!)
and the index will be another 1.3GB. Pretty huge, isn't it?
I think the dynamic row format is appropriate for your table.
BTW, running OPTIMIZE on your table could speed things up,
the figures you post indicate that the table is heavily fragmented.
Also, an ANALYZE never hurts.
Perhaps you could try to move it to a RAM disk (in MyISAM format),
that shouldn't eat up that much space as the HEAP format.
If you can live with a read-only access to the table, at least for some time,
you should consider using the myisampack utility to compress the datafile.
Then it's actually very fast to have the table on disk, too,
because with enough RAM, the keyfile is buffered in MySQL key cache
and the data is mmap()'ed to memory.
This should be as fast as a RAM disk.
If you only need to update a small portion of the table,
you could consider using the MERGE virtual driver,
with the basic part myisampack'ed and the changeable part
in plain MyISAM. But that might slow things down a bit.
You could also reconsider the table design.
Is it really so that the varchar(60) field is the primary key?
Are there any other tables that refer to this one with a foreign key?
(The foreign key should then be also a varchar(60),
which would probably be a killer.)
Jan
mike thomas wrote:
>
> Richard
>
> Thanks for the info! I have just one more question. To get the structure of
> the table I did a myisamchk and did a desc on the table. The output is shown
> below. From this is it possible to determine the amount of RAM I would need
> for a HEAP table?
>
> Thanks!
>
> MichaelT
>
> MyISAM file: domain0115
> Record format: Packed
> Character set: latin1 (8)
> File-version: 1
> Creation time: 2001-01-18 21:38:07
> Status: open,changed
> Data records: 21400798 Deleted blocks: 0
> Datafile parts: 34023680 Deleted data: 0
> Datafile pointer (bytes): 4 Keyfile pointer (bytes): 4
> Datafile length: 712991472 Keyfile length: 224704512
> Max datafile length: 4294967294 Max keyfile length: 4398046510079
> Recordlength: 65
>
> table description:
> Key Start Len Index Type Rec/key Root
> Blocksize
> 1 2 60 unique char packed stripped 1 35837952
> 1024
>
> mysql> desc domain0115;
> +---------+-------------+------+-----+---------+-------+----------------
> | Field | Type | Null | Key | Default | Extra | Privileges
> +---------+-------------+------+-----+---------+-------+----------------
> | dname | varchar(60) | | PRI | | |
> | matched | int(11) | YES | | NULL | |
> +---------+-------------+------+-----+---------+-------+----------------
> 2 rows in set (0.04 sec)
>
> **********************************************************
> >Hi all
> >
> >I have a large table (20 million records) that I want to load into RAM to
> >improve selects. I understand (from previous posts) that I must create a
> >HEAP table. Please correct me if I'm wrong on this. My question is - how
> >much RAM do I need if the MYI and MYD files are as follows :-
> >
> >214M Jan 20 19:11 domain0115.MYI
> >680M Jan 20 19:11 domain0115.MYD
> >
> >Those sizes are in Mega bytes. Does the RAM size have to provide for both
> >the MYI and MYD files?
>
> Yes. You will need around 1gig available mem to load that table! You have
> not included the structure of the table, so the table could have fixed row
> lengths. This can make a table very large indeed. Are you using varchar or
> char columns?
>
> >Also, is it correct that the MYD is the datafile and MYI the index file?
>
> Yes.
>
> --
> Richard Ellerbrock
> [EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
