SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
04-29-2008, 08:27 PM
| ||||
| ||||
 why is explain.key-len not size of key field ? 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      |
|
04-29-2008, 08:27 PM
| |||
| |||
| Re: why is explain.key-len not size of key field ? On Tue, Apr 29, 2008 at 5:12 AM, C.R.Vegelin <cr.vegelin@hetnet.nl> 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 wultsch@gmail.com wultsch (aim) |
|
04-29-2008, 08:27 PM
| |||
| |||
| Re: why is explain.key-len not size of key field ? ----- Original Message ----- From: "Rob Wultsch" <wultsch@gmail.com> To: "C.R.Vegelin" <cr.vegelin@hetnet.nl> 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 <cr.vegelin@hetnet.nl> 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 > wultsch@gmail.com > 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 |
|
04-29-2008, 08:27 PM
| |||
| |||
| Re: why is explain.key-len not size of key field ? On Tue, Apr 29, 2008 at 7:39 AM, C.R.Vegelin <cr.vegelin@hetnet.nl> wrote: > ----- Original Message ----- From: "Rob Wultsch" <wultsch@gmail.com> > To: "C.R.Vegelin" <cr.vegelin@hetnet.nl> > 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 <cr.vegelin@hetnet.nl> 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 > > wultsch@gmail.com > > > > > > 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...ngth-in-mysql/ -- Rob Wultsch wultsch@gmail.com wultsch (aim) |
|
04-29-2008, 08:27 PM
| ||||
| ||||
| Re: why is explain.key-len not size of key field ? ----- Original Message ----- From: "Rob Wultsch" <wultsch@gmail.com> To: "C.R.Vegelin" <cr.vegelin@hetnet.nl> 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 <cr.vegelin@hetnet.nl> wrote: >> ----- Original Message ----- From: "Rob Wultsch" <wultsch@gmail.com> >> To: "C.R.Vegelin" <cr.vegelin@hetnet.nl> >> 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 <cr.vegelin@hetnet.nl> >> > 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 >> > wultsch@gmail.com >> > >> > >> >> 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...ngth-in-mysql/ > > -- > Rob Wultsch > wultsch@gmail.com > 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. |
Linear Mode
