General Discussion

Print Page | Close Window

MySQL

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: General Discussion
Forum Description: General discussion and chat on any topic.
URL: https://forums.webwiz.net/forum_posts.asp?TID=10385
Printed Date: 31 March 2026 at 4:33pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: MySQL

Posted By: huwnet
Subject: MySQL
Date Posted: 11 May 2004 at 11:18am

Some info I need for a project!

1)What is the maximum number of records for a MySql Table?

2)Are there any free tools to setup the tables (add rows blah, blah). There is a MySQL administrator but it dosen't yet have these feautres.

Replies:

Posted By: Mart
Date Posted: 11 May 2004 at 11:22am

1) No idea but its a lot, your not likely to fill it up

2) You can do all of that via the command line, theres a good tutorial somewhere but it's on my other HD... If I boot up into it tonight I will send you a link

Posted By: huwnet
Date Posted: 11 May 2004 at 11:35am

1) I am writing a script that will calculate the 7 time table and enter it into database. So it may fill up quickly!

Posted By: huwnet
Date Posted: 11 May 2004 at 12:48pm

Also what database type should I use?

MyISAM
InnoDB
HEAP
BDB
ISAM

Posted By: Mart
Date Posted: 11 May 2004 at 1:58pm

Well how many records are you looking at? Just saying it's the 7 times table doesn't say how many records you need... you could just be going up to 70 for all we know

Posted By: michael
Date Posted: 11 May 2004 at 2:49pm

From the mySQL Manual that most people probably don't bother to read

MySQL 3.22 has a 4G limit on table size. With the new MyISAM in MySQL 3.23 the maximum table size is pushed up to 8 million terabytes (2 ^ 63 bytes).

Note however that operating systems have their own file size limits. On Linux, the current limit is 2G; on Solaris 2.5.1, the limit is 4G; on Solaris 2.6, the limit is 1000G. This means that the table size for MySQL is normally limited by the operating system.

By default, MySQL tables have a maximum size of about 4G. You can check the maximum table size for a table with the SHOW TABLE STATUS command or with the myisamchk -dv table_name. http://www.dwam.net/docs/mysqle3.23/manuel_SHOW.html#SHOW - 7.20 SHOW syntax (Get information about tables, columns,...) .

If you need bigger tables than 4G (and your operating system supports this), you should set the AVG_ROW_LENGTH and MAX_ROWS parameter when you create your table. http://www.dwam.net/docs/mysqle3.23/manuel_CREATE_TABLE.html#CREATE_TABLE - 7.6 CREATE TABLE syntax . You can also set these later with ALTER TABLE. http://www.dwam.net/docs/mysqle3.23/manuel_ALTER_TABLE.html#ALTER_TABLE - 7.7 ALTER TABLE syntax .

If your big table is going to be read-only, you could use pack_isam to merge and compress many tables to one. pack_isam usually compresses a table by at least 50%, so you can have, in effect, much bigger tables. http://www.dwam.net/docs/mysqle3.23/manuel_pack_isam.html#pack_isam - pack_isam .

Another solution can be the included MERGE library, which allows you to handle a collection of identical tables as one. (Identical in this case means that all tables are created with identical column information.) Currently MERGE can only be used to scan a collection of tables because it doesn't support indexes. We will add indexes to this in the near future.

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: michael
Date Posted: 11 May 2004 at 2:53pm

just did a quick whirl on a mySQL table and
alter table weather max_rows = 200000000000 avg_row_length = 50
did not return an error so as I doubt you hit 200 Billion rows that quick I'd say you are good to go

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: Gullanian
Date Posted: 11 May 2004 at 3:54pm

Why would you want to store the 7* table in a database anyway?

Posted By: JohnKn
Date Posted: 11 May 2004 at 6:12pm

DB Tools is a great piece of software for managing a mySQL database. It makes setting up tables, importing data from other sources etc very easy.

http://www.dbtools.com.br/EN/

Posted By: pmormr
Date Posted: 11 May 2004 at 8:25pm

michael wrote:

just did a quick whirl on a mySQL table and
alter table weather max_rows = 200000000000 avg_row_length = 50
did not return an error so as I doubt you hit 200 Billion rows that quick I'd say you are good to go

If you needed to store 2 billion rows you shouldn't be using MySQL

-------------
Paul A Morgan

http://www.pmorganphoto.com/" rel="nofollow - http://www.pmorganphoto.com/

Posted By: michael
Date Posted: 11 May 2004 at 8:27pm

probably not, but you never know....some companies save where they can.

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker