List:General Discussion« Previous MessageNext Message »
From:Elim PDT Date:September 21 2010 5:47pm
Subject:Re: prime number table
View as plain text  
Thank you so much Chris.
But is this mean that myisam table stores integer very differntly and
inefficiently than InnoDB table?

I see that oid can be ommitted. But in the case that I like to query
on ordinal number of the prime. oid can make things faster, am i right?

seperate the storage for descript is a great idea. thanks again!



----- Original Message ----- 
From: "Chris W" <4rfvgy7@stripped>
To: "Elim PDT" <elim@stripped>
Cc: <mysql@stripped>
Sent: Saturday, September 18, 2010 6:57 AM
Subject: Re: prime number table


>  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
>>
>
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>


--------------------------------------------------------------------------------



No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.851 / Virus Database: 271.1.1/3143 - Release Date: 09/18/10 
00:34:00

Thread
Encryption with MYSQLTompkins Neil17 Sep
  • Re: Encryption with MYSQLJohan De Meersman17 Sep
  • prime number tableElim PDT17 Sep
    • RE: prime number tableGavin Towey17 Sep
    • Re: prime number tableChris W18 Sep
  • Re: prime number tableElim PDT21 Sep
Re: Encryption with MYSQLTompkins Neil20 Sep
  • Re: Encryption with MYSQLBaron Schwartz20 Sep