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
DJ_AM_JuiceboxAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

UmeshSenior Principal 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
UmeshSenior Principal 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.