Bennett Haselton wrote: > Row sizes in MySQL tables can't exceed 65535 bytes (BLOB and, presumably, > TEXT fields are not counted towards this total). That means you can't > defined a table where the *possible* size of a row might be larger than > that -- e.g. a column of type VARCHAR(255) gets counted as taking up 256 > bytes towards that total, even though most of the time it will be much > smaller. > > I had tried creating a table with a lot of fields, then ran into problems > because of the size limit, so I changed some of the field types from text > to integer (which they should have been to begin with, but I was just using > a script to create the table quickly so I hadn't bothered with that > tweak). Now I'm under the 65535-character limit, but I don't know how > close I am. Is there a way to determine the size (or rather, the maximum > possible size) of a row in a given table?
Not sure of the answer in the case of MySQL, but I will say that if you have a table with so many columns as to be close to a 64k row size limit, then you're approaching this whole relational database thing from the wrong angle. Even if it's a logging application, that takes quite a lot of information, no table needs to be that wide, especially if you need the database to perform relatively well. >From a performance standpoint, the best way to look at it is something like this: Most file systems store data as pages of octets these pages are generally small, let's say 4k for the sake of an example. If you have a database row that's 64k, it will extend across 16 pages. That's a lot of IO overhead, especially if you're not going to be using anywhere close to all of those columns for any single database operation. If you break the table out into multiple tables, segmented into groups of entities that logically relate to each other, you can actually achieve much better performance. Then, when you need some of the data that's been isolated from the main table, you can just perform a 1-to-1 joined query on the data that you need. Just my 2 cents. -- Tom Bradford - http://www.tbradford.org Developer - dbXML - http://www.dbxml.org Maintainer - jEdit-Syntax - http://syntax.jedit.org Co-Author - O'Reilly & Associates' "Learning dbXML" --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
