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" <4rfv...@cox.net>
To: "Elim PDT" <e...@pdtnetworks.net>
Cc: <mysql@lists.mysql.com>
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=e...@pdtnetworks.net
--------------------------------------------------------------------------------
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
