Hi David, Great questions:
----- Original Message ----- From: "David Griffiths" Sent: Friday, May 28, 2004 6:05 PM Subject: Specifying an index length and the default value > The length of indexes on varchar and char indexes can be specified at > index creation. > > What is the default length of an index if no length is provided? The default is to index the whole column length (of course that's not possible with TEXT/BLOB columns). > The High Performance MySQL book hints that the index-length used is > specific for each entry in the indexed column (ie an index on a column > where the average length of the data is 8 bytes would take up (8 x > number-of-rows) bytes). Well, maybe. It depends... see below. > If a column was a varchar(128), would the index use 128 bytes per entry, > or would it use the number of bytes in each row of the indexed column. > So if each row had exactly four characters, the index would use four > bytes per row, but if a row was added with 8 characters, that one row > would have an index entry that was 8 bytes in size and the rest would > remain at 4? I don't know that it's in the manual anywhere, but from experimenting and stuff, I've found that, generally, indexes with a TOTAL length (if there's multiple columns) of <= 8 are fixed-length. That is, they'll always use 8 bytes/row even if a string doesn't take up that much space. Actually, this "up to 8 bytes, fixed length" behavior might only occur with indexed character columns (e.g. 100% numeric indexes may stay fixed-length regardless of their size). I'm not sure... You can change the behavior, however, by setting PACK_KEYS in ALTER/CREATE TABLE. The default, in MySQL 4+, is DEFAULT, where MySQL decides whether to use fixed-length keys (faster) or packed variable-length keys (space saving) depending on the index. Setting PACK_KEYS to 0 forces all fixed-length keys (I *think* 0 in 3.23 is like DEFAULT in 4+). Setting PACK_KEYS to 1 packs all *non-unique* indexes, even if they're numeric or <= 8 bytes. It's useful to use myisamchk to find out the properties of columns in an index: myisamchk -dv /path/to/db/table In the second half of the output, you will see information about the table's indexes. Some things you may see in the Type column are: packed - I think this is for character indexes that have prefix compression (multiple index entries that start with the same characters are compressed). Any unused space at the end of the index (storing 10 chars in a 32 character index) is also not stored (like you were talking about above). prefix - I think this one is for numeric indexes that have prefix compression (in an INT index, values 0 - 255 use the same 3 bytes, so those can be compressed). stripped - This is for character indexes that have unused trailing space stripped (again, like you were talking about above). Now, having said that, there's still some things in the myisamchk output that I can't figure out: like sometimes there will be "packed" and "stripped" on the same column; sometimes not. And other things I can't remember now that don't seem consistent. I just kinda figured it out on my own since I don't know that there are official descriptions anywhere. ( But at least it gives you more of an idea of what's going on internally than you can get from a SQL query. :-) > Thanks for any input. > David. Hope that helps somewhat. Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
