Overcome MySQL limit of 2 ^ 32 rows in MyISAM table

Question

I want to create a MyISAM table in MySQL (I tried various versions 5.0.x and 5.1.x), which allows using more than 2 ^ 32 rows. The way to this is to force MySQL to use 8-byte instead of 4-byte string pointers.

Here is a quote from the MySQL manual:

If you create MySQL with the --with-big-tables option, the row limit is increased to 1,844E + 19 rows. See Section 2.3.2 “Typical Configuration Settings”. Binary distributions for Unix and Linux are built with this option.

Sounds easy, right? But I tried various binary distributions for Linux x86_64, and also created MySQL from the source code with the option --with-big-tables. In each case, I still cannot go beyond 2 ^ 32. I create a table, for example:

CREATE TABLE big (col int) MAX_ROWS=1099511627776

And when I check the state of the table, it says:

Create_options: max_rows=4294967295

How to avoid 32-bit purgatory? Using InnoDB may solve the problem, but it performs much slower for the type of queries that I run.

FYI, here's an important link that didn't solve the problem:

http://jeremy.zawodny.com/blog/archives/000796.html

Answer 1

I worked it out for myself, and the answer is simple and absurd.

MAX_ROWS MyISAM, 2 ^ 32 . , myisam_data_pointer_size.

myisam_data_pointer_size 6 MySQL, Linux/Unix, 2 ^ 48 ( 2 ^ 48 ). , - :

SET GLOBAL myisam_data_pointer_size=5;

, :

SHOW VARIABLES LIKE 'myisam_data_pointer_size';

MyISAM (2 ^ (8 * myisam_data_pointer_size)) - 1, myisam_data_pointer_size .

Answer 2

mysql, --with-big-tables, (2 ^ 32) ^ 2. , :)