• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 925
  • Last Modified:

max number of rows in a table?

Hi,

On a 32 and 64 bit machines, what's the max number of rows a table can have in mysql? Is it 2^32 and 2^64 accordingly? Or can a 32 bit installation of mysql handle 2^64?

Even if you can have 2^64 rows, what's the realistic performance on that going to be?

Thanks
0
DJ_AM_Juicebox
Asked:
DJ_AM_Juicebox
  • 2
3 Solutions
 
UmeshMySQL Principle Technical Support EngineerCommented:
The effective maximum table size for MySQL databases is usually determined by operating system constraints on file sizes, not by MySQL internal limits. The following table lists some examples of operating system file-size limits. This is only a rough guide and is not intended to be definitive. For the most up-to-date information, be sure to check the documentation specific to your operating system.

Operating System       File-size Limit
Win32 w/ FAT/FAT32       2GB/4GB
Win32 w/ NTFS       2TB (possibly larger)
Linux 2.2-Intel 32-bit       2GB (LFS: 4GB)
Linux 2.4+       (using ext3 filesystem) 4TB
Solaris 9/10       16TB
MacOS X w/ HFS+       2TB
NetWare w/NSS filesystem       8TB

Windows users, please note that FAT and VFAT (FAT32) are not considered suitable for production use with MySQL. Use NTFS instead.

Pls check this for more details..

http://dev.mysql.com/doc/refman/5.0/en/full-table.html
0
 
k_murli_krishnaCommented:
ushastry is correct but it is not just number of rows that determines data file size required but row size as well. Table size is in general determined by following formula:
Table size = (Average row size + 8) * number of rows * 1.5
You can arrive at average row size using rough calculation based on data type and size, using queries and also directly using inbuilt or third party tools.

Here are some links on MySQL max rows & rowsize etc. of a table limitations for innoDB, MySAM etc. tables:
http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html
http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html
http://www-css.fnal.gov/dsg/external/freeware/mysql-vs-pgsql.html
http://www.mysqlperformanceblog.com/?s=max+rows+mysql+table

0
 
UmeshMySQL Principle Technical Support EngineerCommented:
>>Even if you can have 2^64 rows, what's the realistic performance on that going to be?

It depends upon the usage of READ/WRITE to that table(s). Your SELECT queries must be well written and shouldn't be killer to the table.

BTW, which engine you are going to use? MyISAM or InnoDB??
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now