List: General Discussion « Previous MessageNext Message » From: Chris W Date: September 18 2010 12:57pm Subject: Re: prime number table View as plain text
```  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

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
>
```