From: Elim PDT Date: September 21 2010 5:47pm Subject: Re: prime number table List-Archive: http://lists.mysql.com/mysql/223084 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="ISO-8859-1"; reply-type=original Content-Transfer-Encoding: 7bit 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" Cc: 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=elim@stripped > -------------------------------------------------------------------------------- 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