----- Original Message ----- From: "Keith C. Ivey" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: "Jared Richardson" <[EMAIL PROTECTED]> Sent: Tuesday, June 04, 2002 5:24 PM Subject: Re: Bug related to large tables and it's indexes on Win2k
| On 4 Jun 2002, at 15:43, Jared Richardson wrote: | | > | > AVG_ROW_LENGTH=4096 MAX_ROWS=4294967295; | > | | > | Why do you use AVG_ROW_LENGTH=4096? It seems to me the max record | | > length is 528...? | | > | > According to the MySql docs, the max table size is AVG_ROW_LENGTH * | > MAX_ROWS | > | > We were trying to ensure the maximum amount of growth... this table | > can grow very very large.... | | That's fine, but if you make the max size bigger than what you really | need, you bloat your index and slow things down for no benefit. As I | understand it, with the default max table size of 4 GB (for dynamic- | record MyISAM tables), the index will have 4-byte pointers to the | records. The next step would be 5-byte pointers, which would allow a | max table size of 1024 GB (or 1 TB). But you're asking for an even | higher max table size, forcing MySL to use 6-byte pointers in the | index, which allows a max table size of 256 TB. | | I don't think there's any difference between setting AVG_ROW_LENGTH | to 4096 and setting it to 528, since either will put you in the same | size range when combined with MAX_ROWS=4294967295. | That makes sense. | Think about how many rows you're actually going to have and what the | real average row length is. 528 may be your maximum row length, but | I think it's unlikely that your VARCHARs average anywhere near 255. | Most likely the numbers are low enough that the 1 TB table size is | sufficient, so your indexes will only need 5-byte pointers and thus | be smaller. | This table is part of a product that contains publicly available (and always expanding) publicly avilable biological data in addition to large companies internal data. A one terrabyte cap very well could come back to haunt us one day! (sadly enough!) We'll give it a shot with more reasonable numbers and see if we can work around this problem. Thank you very much for this input! | [Filter fodder: SQL] | | -- | Keith C. Ivey <[EMAIL PROTECTED]> | Tobacco Documents Online | http://tobaccodocuments.org --------------------------------------------------------------------- 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
