From: Chris W
Date: September 18 2010 12:57pm
Subject: Re: prime number table
List-Archive: http://lists.mysql.com/mysql/223031
Message-Id: <4C94B754.2090602@cox.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

  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
>