Matt,
Thanks for the great reply. We have a database that has been migrated over to MySQL 4.0, and the column-definitions are a bit wacked - way more space is allocated than is needed for many columns. I think you are using MyISAM tables; we are using InnoDB. I think it woudl be worth dropping some indexes after seeing how much free tablespace there is, and then re-creating those indexes with a length specifier of the column size, and see if the amount of free tablespace changes. If it doesn't, then MySQL is indexing the whole column.
The question arose due to a unique index we have on a table; we can't seem to get the data in from our other database - the index fails, yet the value that it fails on appears only once in the table we are copying from. I wondered if maybe MySQL was defaulting the length of the index, and causing it to conflict with an other value. I don't believe it is, however, as there is no value that is even remotely similar.
Regardless, I think it might be a worthwhile exercise to take a close look at our data, and see if we can guesstimate appropriate lengths.
Thanks, David.
Matt W wrote:
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]
