On Fri, Jan 16, 2004 at 10:14:08AM -0800, Andrew Kwiczola wrote: > I was wondering how many records A mysql table can comfortably handle.. and > which table type supports the greatest amount of record capacity. Can I > have a few million records in 1 table? Over 10 million? Thanks ! >
Hi, Andrew. I posted yesterday explaining that my table seems fairly slow for queries. The list archive should have the message at http://lists.mysql.com My table has over 498,000,000 records of fixed-length: CREATE TABLE `inv0web02` ( `docid` int(10) unsigned NOT NULL default '0', `offset` smallint(5) unsigned NOT NULL default '0', `termid` int(10) unsigned NOT NULL default '0', `taglistid` smallint(5) unsigned NOT NULL default '0', `whichpara` tinyint(3) unsigned NOT NULL default '0', `weight_in_subdoc` float unsigned NOT NULL default '0', PRIMARY KEY (`docid`,`offset`), KEY `termid_index` (`termid`), KEY `whichpara_index` (`whichpara`), KEY `taglistid_index` (`taglistid`), KEY `weight_index` (`weight_in_subdoc`), KEY `docid_index` (`docid`), KEY `offset_index` (`offset`), KEY `termid_docid_whichpara_offset` (`termid`,`docid`,`whichpara`,`offset`) ) TYPE=MyISAM; mysql> show table status; | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +-----------+--------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+----------------+---------+ | inv0web02 | MyISAM | Fixed | 498093481 | 18 | 8965682658 | 77309411327 | 33526264832 | 0 | NULL | 2004-01-15 13:54:28 | 2004-01-15 14:42:01 | 2004-01-15 23:16:29 | | | This takes about 40GB on disk, from 20GB of raw input. (I used INNODB previously, it took about 120GB on disk with comparable performance otherwise). To allow so many records with MyISAM, you need to "ALTER TABLE tablename MAX_ROWS=[very large value];" to allow for pointers to be big enough for all the rows. So, I'd say that yes, you can create quite large tables in MySQL. Certainly for most purposes a few million records should not be any problem. -- Greg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
