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]
