max number of rows in a table?

Posted on 2008-11-16
Last Modified: 2012-08-13

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?

Question by:DJ_AM_Juicebox
    LVL 26

    Accepted Solution

    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..
    LVL 17

    Assisted Solution

    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:

    LVL 26

    Assisted Solution

    >>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??

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (, A SQLite Tidbit: Quick Numbers Table Generation (…
    As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
    This video discusses moving either the default database or any database to a new volume.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now