As most folks know, by default, MySQL limits the size of a MyISAM table at 4GB. Where does this limit come from? It's the maximum of a 32-bit address:
232 = 4,294,967,296 bytes = 4GB
How is this 4GB allocated? Well here's the math:
row count X row length = 4GB max
Basically, if your rows don't contain much information, you can cram a lot of rows into a table. On the flip side, if you don't plan on having too many rows, you can cram a lot of information in each row.
Here's where things get ugly. If you have a MyISAM table and you exceed the maximum data length for the table, it may or may not tell you that you've exceeded the limit (depending on the version). If it doesn't tell you, your data will actually become corrupt.
So, how can you find out what a table's limit is? Run show table status like 'tablename' and check the value for Max_data_length. The default, of course, is 4294967295.
How can the Max_data_length be increased? Just run something like alter table tablename max_rows = 200000000000 avg_row_length = 50. This example would increase your Max_data_length to 1,099,511,627,775.
Why not switch the storage engine to InnoDB then you can have the same table with many rows?
I believe all this applies only to MySQL 4 and prior versions.
In MySQL 5, the default pointer size was increased to 6 bytes (48 bits), allowing for tables up to 256TB (assuming your filesystem allows files this large). The pointer can even be set to 7 bytes, allowing for a table size of up to 65,536TB. (please be patient, your backup completion time over gigabit ethernet is estimated at 16.6 years)
You can check the default row pointer size with "SHOW GLOBAL VARIABLES LIKE 'myisam_data_pointer_size'; ". This will show the default pointer size in bytes. This parameter, can of course, be changed in your my.cnf if you would like a different default.
Conversely, if you need to squeeze every bit of performance out of your system, you can set the row pointer size down as low as 2 bytes for tables which you are confident will never grow very large. This will save 4 bytes per row in the datafile and each index.
The row pointer size of a table can be adjusted using the "ALTER TABLE" technique described in the main post.
Good points, Scott! I had forgotten about the pointer size changes in MySQL 5.
What you people have written in very informative but it contradicts the statements on mysql's site where it states that row length is 64,535. I believe what you guys state is true but why havent those guys mentioned this in documentation. the bottom line is 'i am confused'
Hi All,
I find myself asking this very question now and the article has been very informative. However, id love to know if there is an answer to the response before mine by Daniel.
Many thanks, Jayp.