At 10:16 AM 11/17/2001 -0700, Tom Bradford wrote: >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.
Is that true even if the vast majority of rows do not contain anywhere near 64 K of data? In my example, most of the rows taking up space are of type VARCHAR(255). Will all the rows occupy 16 pages of data even though most of them don't need it? I don't want to have rows with this much data crammed into them, but due to the impossibility of doing some things in MySQL and Perl (which I can't port the application away from any time soon), I don't think I have much choice. Example: each user in our application has up to five URLs associated with their account. The ideal solution would be to have a separate table for URLs, associated with an ID number as a unique index, and then the user table would reference those indexes. But I also want to be able to view table contents and table rows quickly through a Web-based interface. MySQL apparently does not provide a way of finding out which fields in a table are foreign keys after the table has been created, so instead of the URLs, all I'd see would be a bunch of numbers. And in the one-to-many relationship between users and monitored URLs, MySQL probably don't let me enforce the constraint of only five URLs for any one user. (I could enforce it in about ten different places in the code, but that's notoriously error-prone.) And then every time I wanted to get the data in a user-friendly fashion, I'd have to join two tables -- which I'm not afraid of doing :) but those are even more error-prone because Perl can't enforce the correctness of the query syntax before you compile the program, so you end up with the worse kind of coding errors -- where your program runs part of the way through and then chokes on incorrect syntax. (To the maximum extent possible, a language should ensure that programs with typos will not run, instead of letting them run and do something unexpected.) Then, if I'm running a test case, I have to clean up all the changes that were made to the database and start all over again, etc. Moral is: look at total cost of ownership for software, not the price :) >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" [EMAIL PROTECTED] http://www.peacefire.org (425) 649 9024 --------------------------------------------------------------------- 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
