MyISAM char(7) field has index with long key length

Question

i have a date dimension table with a char(7) field called year_month_number with an index on it as follows:

ALTER TABLE `dim_date` add INDEX `year_month_number` (`year_month_number` ASC);

it has data in it like '2013-06' or '2013-07'.

im having performance issues, so did an explain,

'1', 'SIMPLE', 'effective_date', 'ref', 'PRIMARY,year_month_number', 'year_month_number', '22', 'const', '29', 'Using where'

it seems like the key_len = 22, can anyone explain why it is not 7 (as suggested in this post)? i have tried dropping and recreating the index,

Is it a `VARCHAR(7)` or a `CHAR(7)`? – ypercubeᵀᴹ Oct 14 '13 at 14:39 — ypercubeᵀᴹ, Oct 14 '13 at 14:39

score 1 · Accepted Answer · answered Oct 14 '13 at 09:04

1

not sure why it shows 22, maybe some encoding issue - is it char(7) binary?

anyway - change field to usual int, where you will store YYYYMM as number, this will be only 4 bytes

answered Oct 14 '13 at 09:04

Iłya Bursov

nope, its a bog-standard char(7), not binary, not varchar. i've changed it to a medium int. this seems to do the job although the key size is now 4 rather than 3 as suggested here: [link](http://dev.mysql.com/doc/refman/5.0/en/integer-types.html). – marengaz Oct 15 '13 at 07:07
indexes uses only word/dword aligned structures, as they are faster, so usually they can take only even lengths (like 4 bytes, 8 bytes), 3 is odd number – Iłya Bursov Oct 15 '13 at 07:10
Answer found! to why `key_len = 22` and not `7`. Each `char` takes 3 bytes. a `varchar` datatype takes 2 bytes to store the length of the string. [reference](http://dev.mysql.com/doc/refman/5.0/en/column-count-limit.html) – marengaz Nov 04 '13 at 07:54
@user2847123 7*3+2 = 23, also it is char, not varchar – Iłya Bursov Nov 04 '13 at 15:26

1 Answers1