Hi Mark, I'll tell you what I know. :-)
First, AVG_ROW_LENGTH is only needed for dynamic row-length tables (it's ignored with fixed-length rows) -- more specifically, those with TEXT/BLOB columns. Otherwise, if MAX_ROWS is used, MySQL will assume that each TEXT/BLOB column will be filled completely, [probably] making it think the data file will be > 4GB. I discovered this when I was ONLY specifying MAX_ROWS. It made the index file larger because 5 byte data pointers were used instead of 4. Second, the 4GB data file limit also only applies to dynamic row-length tables. That's because their data pointer is in bytes. The data pointer for fixed row-length tables is just a *row number*. Therefore, fixed row-length tables are limited by the number of rows -- 4 billion (4,294,967,295 actually) with 4 byte pointers. No, as far as I know, MySQL doesn't use MAX_ROWS or AVG_ROW_LENGTH to do any optimizations -- only to decide the pointer size and therefore the max size of the table. BTW, I don't know what the use of MIN_ROWS is, do you?? Yes, I use MAX_ROWS and AVG_ROW_LENGTH for tables that I know won't be over a certain size. This makes the index file smaller if the data pointer is < 4 bytes. To determine space savings, the number of data pointers used is: number_of_rows * number_of_indexes. The *index* pointer may also be smaller, but I'm not sure what its purpose is -- I think it points to other blocks in the index or something... Its size seems to depend on how many indexes there are, etc. If you want to check the size of the pointers, use: myisamchk -dv /path/to/database/table So for fixed row-length tables: MAX_ROWS <= 255 -- 1 byte pointer MAX_ROWS <= 65,535 -- 2 byte pointer MAX_ROWS <= 16,777,215 -- 3 byte pointer MAX_ROWS > 16,777,215 or no MAX_ROWS -- 4 byte pointer For dynamic row-length tables, the product of MAX_ROWS and AVG_ROW_LENGTH determines the max data file size. In other words, their individual value doesn't matter, only the product (e.g. don't waste time trying to figure their exact values). If AVG_ROW_LENGTH isn't given, I think it's assumed to be the maximum length of a row. product = MAX_ROWS * AVG_ROW_LENGTH product <= 255 -- 1 byte pointer; max data size: 255 (REALLY small table :-)) product <= 65,535 -- 2 byte pointer; max data size: 64KB product <= 16,777,215 -- 3 byte pointer; max data size: 16MB product <= 4,294,967,295 or no MAX_ROWS -- 4 byte pointer; max data size: 4GB product > 4,294,967,295 -- 5 byte pointer; max data size: 1TB (or 4,294,967,295 rows is MySQL's internal limit I *think*) Hope that helps! Matt P.S. You should upgrade MySQL (at least latest 3.23; preferably to 4.0). :-) 3.23.41 is over 2 years old! ----- Original Message ----- From: "Mark Hawkes" Sent: Friday, December 19, 2003 10:01 AM Subject: Benefits of MAX_ROWS and AVG_ROW_LENGTH > Hi all, > > At table creation time I can use MAX_ROWS and AVG_ROW_LENGTH to > > (a) limit the size of a HEAP table > (b) overcome MyISAM's default 4GB limit > > But are they used in any other ways? Does MySQL use them to improve > performance by sizing buffers appropriately or to prevent fragmentation in > dynamic tables? > > Is there any point in me using MAX_ROWS, MIN_ROWS or > AVG_ROW_LENGTH if my > tables will be small (20KB to 700KB with slow growth rates)? I'm currently > running 3.23.41. > > Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
