Keep in mind that to store a number as a 32 bit unsigned integer takes
4 bytes of data no matter how small or large that number is as long as
it is less than 4,294,267,296. If you store numbers as a string like it
is in your file it takes 8 bits per digit so for 19,999,999 it would
take 8 bytes or 64 bits. I assume the file has a line feed between each
number, that is another 8 bits and may also have a carriage return
adding another 8 bits.
You could probably make your table even smaller if you were to move the
descript filed to a new table. I highly recommend this option based on
your statement that most of them are empty. A table with only integer
values will have a fixed row size and probably be a little faster to
access. If most of the rows will have descript text them leave it in
this table, other wise it would be more efficient to keep in a different
table.
Also I'm not sure you really need an independent field for a primary key
since the prime numbers could be used as the primary key. That would
make your table even smaller. I'm not sure why you would what a table
of prime numbers unless you had an index on that field and making it the
primary key does that for you.
Chris W
On 9/17/2010 1:28 PM, Elim PDT wrote:
> I got a file of the list of the 1st 1270607 prime numbers (the
> 1270607th prime is 19999999,
> beat the $227 book at
> http://www.amazon.com/prime-numbers-Carnegie-institution-Washington/dp/B0006AH1S8).
> the file is an output of a python script. the file size is about 12Mb.
>
> Then I created a simeple mysql table prime as
>
> mysql> desc prime;
> +----------+------------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +----------+------------------+------+-----+---------+----------------+
> | oid | int(10) unsigned | NO | PRI | NULL | auto_increment |
> | pv | int(10) unsigned | YES | | NULL | |
> | descript | text | YES | | NULL | |
> +----------+------------------+------+-----+---------+----------------+
> mysql> show create table prime;
> --------------------------------------------------------------------------+
>
> | Table | Create Table
> --------------------------------------------------------------------------+
>
> | prime | CREATE TABLE `prime` (
> `oid` int(10) unsigned NOT NULL AUTO_INCREMENT,
> `pv` int(10) unsigned DEFAULT NULL,
> `descript` text,
> PRIMARY KEY (`oid`)
> ) ENGINE=MyISAM AUTO_INCREMENT=1270608 DEFAULT CHARSET=latin1
>
> The table file size is (prime.frm,prime.MYD,prime.MYI) = (9k;
> 24,817KB; 12,754KB)
>
> Then I do
> mysql> create table prm select * from prime order by prime.oid;
> mysql> alter table prm modify oid int unsigned primary key
> auto_increment;
>
> mysql> desc prm;
> +----------+------------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +----------+------------------+------+-----+---------+----------------+
> | oid | int(10) unsigned | NO | PRI | NULL | auto_increment |
> | pv | int(10) unsigned | YES | | NULL | |
> | descript | text | YES | | NULL | |
> +----------+------------------+------+-----+---------+----------------+
>
> mysql> show create table prm;
> +-------+------------------------------------------------------------------
>
> | Table | Create Table
> +-------+------------------------------------------------------------------
>
> | prm | CREATE TABLE `prm` (
> `oid` int(10) unsigned NOT NULL AUTO_INCREMENT,
> `pv` int(10) unsigned DEFAULT NULL,
> `descript` text,
> PRIMARY KEY (`oid`)
> ) ENGINE=InnoDB AUTO_INCREMENT=1270608 DEFAULT CHARSET=latin1 |
> +-------+------------------------------------------------------------------
>
>
> The table file prm.frm is only 9KB
>
> My question is that how come it's SO SMALL? (currently the colum
> description
> in both tables prime and prm are empty except one identical row, with
> very
> short string value.
>
> Is is recommend to index the other twoo columns?
>
> Thanks
>
