Re: Almost whole table in single index: is there a better way?

[Rick James]

> s it possible to specify this on a per-table basis by specifying MAX_ROWS, rather than changing the global my.cnf?
I believe the MAX_ROWS clause indirectly controls that setting.

> Is there a similar optimisation for InnoDB?
No. InnoDB does its own thing; not much is tunable. Unlike MyISAM (which has a byte or record offset into the .MYD), InnoDB uses the PRIMARY KEY as the 'pointer'. That is for secondary keys.

> This is certainly worth considering, and would not take too many application level changes. It would save 4 bytes on each row, but not save anything on the index.
If you are doing arithmetic with the numeric `Value`s, then it might simplify the code to know whether the value is numeric?

If you stick with CHAR or FLOAT (not VARCHAR), then myisam_data_pointer_size=4 might work. When the records are "Fixed" width, the pointer refers to a _record_ number, not a _byte offset_. 4 would restrict you to 4 billion _rows_ in _each_ partition.