Hi Mark, Maybe you intentionally only replied to me (instead of the list too), but I'm sending this to the list also so others can follow the discussion. :-)
I never know how much I have to explain things for a person's knowledge level, but it sounds like you understand what's going on very well. :-) More below... ----- Original Message ----- From: "Mark Hawkes" Sent: Saturday, December 20, 2003 3:50 PM Subject: Re: Benefits of MAX_ROWS and AVG_ROW_LENGTH > Hi Matt, > > Thanks very much for your thoughts and advice. I was going to ignore using > MAX_ROWS, MIN_ROWS and AVG_ROW_LENGTH because the tables I'll be working > with are small. That doesn't stop me wanting to tune them though, so I've > included them anyway. I figure it's better to give MySQL a clue - better > than specifying no size params whatsoever. Maybe future versions will use > them intelligently (?). I doubt it. It's already doing all it can. You have to tell it the rest. 4 byte pointers are the default since most tables don't have data files > 4GB. It can't go smaller unless you give MySQL that information -- otherwise people would be getting "Table is full" errors. :-) > The reason I asked is that I'm accustomed to tuning options for filesystems > (inode density, cluster size etc..), and hash tables when programming (e.g. > load factor and ensuring the number of buckets isn't a power of 2, blah). I > just wanted to make sure that something as important as a database table > would also be sized or tuned correctly. Yeah, I'm always trying to optimize things as much as possible. :-D And I never see anyone use MAX_ROWS/AVG_ROW_LENGTH -- unless it's to get around the 4GB limit. He he. > >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. > > Using VARCHAR also makes a table dynamic doesn't it? (Unless it's below 4 > chars.) Yes, but see my reply to Jeremy Zawodny about that. MySQL should still be able to fairly accurately estimate the average row length if the only dynamic columns are VARCHAR. > > 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. > > I see, so if we had a table like this... > > CREATE TABLE Foo ( essay TEXT NOT NULL ) MAX_ROWS = 25; > > then, without specifying AVG_ROW_LENGTH at all, MySQL would assume the > table could get as large as 1.6MB (25 * 65536) and thus use a 3 byte > datafile pointer. Right. :-) I said it would "probably" use 5 byte pointers without AVG_ROW_LENGTH, unless MAX_ROWS is small enough. If there's just 1 regular TEXT/BLOB column: MAX_ROWS < ~65,532 -- 4 byte pointer MAX_ROWS < ~256 -- 3 byte pointer With 1 TEXT column like your example, the max row length would probably be more like 65,540: 65,535 for TEXT data + 2 bytes to record that length + ~3 bytes (I think) for the row header. And that's not taking into account possible split/fragmented rows which will take extra space for the pointer to where the row continues. ;-) > But if we did this... > > CREATE TABLE Foo ( essay TEXT NOT NULL ) MAX_ROWS = 25 > AVG_ROW_LENGTH = 2000; > > then 2 byte datafile pointers would be used because 50,000 bytes can be > addressed by a 16-bit pointer. Okay, I get it. Yep, just verified that. :-) > >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?? > > I agree - looks like MAX_ROWS and AVG_ROW_LENGTH determine the datafile > pointer size and that's all. The only mention of MIN_ROWS in the manual says > > "Minimum number of rows you plan to store in the table" > > Boy, that's really informative! I have no idea what it does but have used > it anyway. Exactly what I was thinking! I honestly can't think of any way that it would be useful, though. Don't know how the minimum number or rows would change anything... Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
