Re: Alter MyISAM table to adjust max_rows and Avg_row_length

[Michael Stassen <suppressed>]

Jeff wrote:
> I've got a table that just hit the 4gig limit for MyISAM tables in a 4.x
> ver db.  I need to alter the table structure and set the max_rows and
> Avg_row_length to override the default of 4 gig.  Problem is I can't
> find any reference in the mysql docs that indicates how to decide a
> setting for Avg_row_length or even what the measurement is.  Bytes?
> Columns? 
>
> Any help is much appreciated.
>
> Jeff

MAX_ROWS and AVG_ROW_LENGTH are defined in the manual page for CREATE TABLE 
<http://dev.mysql.com/doc/refman/4.1/en/create-table.html>:

"MAX_ROWS: The maximum number of rows you plan to store in the table.  This is 
not a hard limit, but rather an indicator that the table must be able to store 
at least this many rows."

"AVG_ROW_LENGTH: An approximation of the average row length for your table.  You 
need to set this only for large tables with variable-size records."

The current value of AVG_ROW_LENGTH can be seen in the output of SHOW TABLE 
STATUS <http://dev.mysql.com/doc/refman/4.1/en/show-table-status.html>, along 
with the maximum_data_length.  They are in bytes.

As for MAX_ROWS, you really just need a value large enough to require a larger 
pointer.  For example, the manual suggests

  ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;

It goes on to say that you don't need to set AVG_ROW_LENGTH unless your table 
has BLOB or TEXT values <http://dev.mysql.com/doc/refman/4.1/en/full-table.html>.

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/suppressed