----- Original Message -----
From: "Rob Wultsch" <[EMAIL PROTECTED]>
To: "C.R.Vegelin" <[EMAIL PROTECTED]>
Cc: <mysql@lists.mysql.com>
Sent: Tuesday, April 29, 2008 2:44 PM
Subject: Re: why is explain.key-len not size of key field ?
On Tue, Apr 29, 2008 at 7:39 AM, C.R.Vegelin <[EMAIL PROTECTED]> wrote:
----- Original Message ----- From: "Rob Wultsch" <[EMAIL PROTECTED]>
To: "C.R.Vegelin" <[EMAIL PROTECTED]>
Cc: <mysql@lists.mysql.com>
Sent: Tuesday, April 29, 2008 2:24 PM
Subject: Re: why is explain.key-len not size of key field ?
> On Tue, Apr 29, 2008 at 5:12 AM, C.R.Vegelin <[EMAIL PROTECTED]>
> wrote:
>
> > Hi All,
> >
> > I have a dictionary table like:
> > CREATE TABLE IF NOT EXISTS `mydictionary`
> > (
> > `EN` varchar(36) default NULL,
> > `DE` varchar(36) default NULL,
> > `ES` varchar(36) default NULL,
> > `FR` varchar(36) default NULL,
> > `IT` varchar(36) default NULL,
> > `NL` varchar(36) default NULL,
> > `PT` varchar(36) default NULL,
> > KEY `EN` (`EN`),
> > KEY `DE` (`DE`),
> > KEY `ES` (`ES`),
> > KEY `FR` (`FR`),
> > KEY `IT` (`IT`),
> > KEY `NL` (`NL`),
> > KEY `PT` (`PT`)
> > ) ENGINE=MyISAM DEFAULT CHARSET = UTF8;
> >
> > When I run the query:
> > EXPLAIN SELECT `EN`, `FR` FROM `mydictionary`
> > WHERE `FR` Like "comp%" ORDER BY `FR`;
> >
> > EXPLAIN says:
> > select type = SIMPLE, type = range, key = FR,
> > key-len = 111, ref = NULL, extra = using where
> >
> > Any idea why key-len is 111 ?
> >
> > Thanks, Cor
> >
> >
>
> Your using a multi byte character set.
>
> Rob Wultsch
> [EMAIL PROTECTED]
>
>
Thanks Rob,
So key-len is expressed in bytes and not in characters.
But that means that each normal character (a..z) is 3 bytes in UTF8 ?
TIA, Cor
Max memory usage is 3 bytes in MySQL. I am not an expert on this
subject. Take a look at the following:
http://en.wikipedia.org/wiki/UTF-8
http://www.xaprb.com/blog/2006/04/17/max-key-length-in-mysql/
--
Rob Wultsch
[EMAIL PROTECTED]
wultsch (aim)
The 2nd link gave the answer stating:
"MySQL must be pessimistic and assume the worst-case scenario of every
character requiring 3 bytes."
Thanks Rob.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]