Mysql Index COLLATE utf8_unicode_ci

Question

I updated database from freeradius and now there is a sql sentence that take more tan 30 seconds to execute while before takes only 0,5s to execute.

This is the OLD definition tables:

    CREATE TABLE `radacct` (
   `radacctid` bigint(21) NOT NULL AUTO_INCREMENT,
   `acctsessionid` varchar(64) NOT NULL DEFAULT '',
   `acctuniqueid` varchar(32) NOT NULL DEFAULT '',
   `username` varchar(64) NOT NULL DEFAULT '',
   `groupname` varchar(64) NOT NULL DEFAULT '',
   `realm` varchar(64) DEFAULT '',
   `nasipaddress` varchar(15) NOT NULL DEFAULT '',
   `nasportid` varchar(15) DEFAULT NULL,
   `nasporttype` varchar(32) DEFAULT NULL,
   `acctstarttime` datetime DEFAULT NULL,
   `acctstoptime` datetime DEFAULT NULL,
   `acctsessiontime` int(12) DEFAULT NULL,
   `acctauthentic` varchar(32) DEFAULT NULL,
   `connectinfo_start` varchar(50) DEFAULT NULL,
   `connectinfo_stop` varchar(50) DEFAULT NULL,
   `acctinputoctets` bigint(20) DEFAULT NULL,
   `acctoutputoctets` bigint(20) DEFAULT NULL,
   `calledstationid` varchar(50) NOT NULL DEFAULT '',
   `callingstationid` varchar(50) NOT NULL DEFAULT '',
   `acctterminatecause` varchar(32) NOT NULL DEFAULT '',
   `servicetype` varchar(32) DEFAULT NULL,
   `framedprotocol` varchar(32) DEFAULT NULL,
   `framedipaddress` varchar(15) NOT NULL DEFAULT '',
   `acctstartdelay` int(12) DEFAULT NULL,
   `acctstopdelay` int(12) DEFAULT NULL,
   `xascendsessionsvrkey` varchar(10) DEFAULT NULL,
   PRIMARY KEY (`radacctid`),
   KEY `username` (`username`),
   KEY `framedipaddress` (`framedipaddress`),
   KEY `acctsessionid` (`acctsessionid`),
   KEY `acctsessiontime` (`acctsessiontime`),
   KEY `acctuniqueid` (`acctuniqueid`),
   KEY `acctstarttime` (`acctstarttime`),
   KEY `acctstoptime` (`acctstoptime`),
   KEY `nasipaddress` (`nasipaddress`)
 ) ENGINE=InnoDB AUTO_INCREMENT=3514770 DEFAULT CHARSET=latin1;

 CREATE TABLE `userinfo` (
   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
   `username` varchar(128) DEFAULT NULL,
   `firstname` varchar(200) DEFAULT NULL,
   `lastname` varchar(200) DEFAULT NULL,
   `email` varchar(200) DEFAULT NULL,
   `department` varchar(200) DEFAULT NULL,
   `company` varchar(200) DEFAULT NULL,
   `workphone` varchar(200) DEFAULT NULL,
   `homephone` varchar(200) DEFAULT NULL,
   `mobilephone` varchar(200) DEFAULT NULL,
   `address` varchar(200) DEFAULT NULL,
   `city` varchar(200) DEFAULT NULL,
   `state` varchar(200) DEFAULT NULL,
   `country` varchar(100) DEFAULT NULL,
   `zip` varchar(200) DEFAULT NULL,
   `notes` varchar(200) DEFAULT NULL,
   `changeuserinfo` varchar(128) DEFAULT NULL,
   `portalloginpassword` varchar(128) DEFAULT '',
   `enableportallogin` int(32) DEFAULT '0',
   `creationdate` datetime DEFAULT '0000-00-00 00:00:00',
   `creationby` varchar(128) DEFAULT NULL,
   `updatedate` datetime DEFAULT '0000-00-00 00:00:00',
   `updateby` varchar(128) DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `username` (`username`),
   KEY `company` (`company`)
 ) ENGINE=MyISAM AUTO_INCREMENT=188493 DEFAULT CHARSET=latin1;

This is the NEW definition tables:

 CREATE TABLE `radacct` (
   `radacctid` bigint(21) NOT NULL AUTO_INCREMENT,
   `acctsessionid` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `acctuniqueid` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `username` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `groupname` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `realm` varchar(64) COLLATE utf8_unicode_ci DEFAULT '',
   `nasipaddress` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `nasportid` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
   `nasporttype` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
   `acctstarttime` datetime DEFAULT NULL,
   `acctstoptime` datetime DEFAULT NULL,
   `acctsessiontime` int(12) DEFAULT NULL,
   `acctauthentic` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
   `connectinfo_start` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
   `connectinfo_stop` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
   `acctinputoctets` bigint(20) DEFAULT NULL,
   `acctoutputoctets` bigint(20) DEFAULT NULL,
   `calledstationid` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `callingstationid` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `acctterminatecause` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `servicetype` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
   `framedprotocol` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
   `framedipaddress` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `acctstartdelay` int(12) DEFAULT NULL,
   `acctstopdelay` int(12) DEFAULT NULL,
   `xascendsessionsvrkey` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`radacctid`),
   KEY `username` (`username`),
   KEY `framedipaddress` (`framedipaddress`),
   KEY `acctsessionid` (`acctsessionid`),
   KEY `acctsessiontime` (`acctsessiontime`),
   KEY `acctuniqueid` (`acctuniqueid`),
   KEY `acctstarttime` (`acctstarttime`),
   KEY `acctstoptime` (`acctstoptime`),
   KEY `nasipaddress` (`nasipaddress`)
 ) ENGINE=MyISAM AUTO_INCREMENT=3519495 DEFAULT CHARSET=utf8    COLLATE=utf8_unicode_ci;

 CREATE TABLE `userinfo` (
   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
   `username` varchar(128) DEFAULT NULL,
   `firstname` varchar(200) DEFAULT NULL,
   `lastname` varchar(200) DEFAULT NULL,
   `email` varchar(200) DEFAULT NULL,
   `department` varchar(200) DEFAULT NULL,
   `company` varchar(200) DEFAULT NULL,
   `workphone` varchar(200) DEFAULT NULL,
   `homephone` varchar(200) DEFAULT NULL,
   `mobilephone` varchar(200) DEFAULT NULL,
   `address` varchar(200) DEFAULT NULL,
   `city` varchar(200) DEFAULT NULL,
   `state` varchar(200) DEFAULT NULL,
   `country` varchar(100) DEFAULT NULL,
   `zip` varchar(200) DEFAULT NULL,
   `notes` varchar(200) DEFAULT NULL,
   `changeuserinfo` varchar(128) DEFAULT NULL,
   `portalloginpassword` varchar(128) DEFAULT '',
   `enableportallogin` int(32) DEFAULT '0',
   `creationdate` datetime DEFAULT '0000-00-00 00:00:00',
   `creationby` varchar(128) DEFAULT NULL,
   `updatedate` datetime DEFAULT '0000-00-00 00:00:00',
   `updateby` varchar(128) DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `username` (`username`),
   KEY `company` (`company`)
 ) ENGINE=MyISAM AUTO_INCREMENT=188894 DEFAULT CHARSET=latin1;

Notice than the OLD table have both CHARSET=latin1, while the NEW tables by definition have different charset and collate: CHARSET=utf8 COLLATE=utf8_unicode_ci and CHARSET=latin1. That should be the difference.

Now this is the sentence I run:

 select TIMESTAMPDIFF(SECOND,max(acctstarttime),now()) as segons,(department) as idClient,(userinfo.username)
        from
        (select radacct.username as id
            from radacct,userinfo
            where userinfo.company=98
            and radacct.username = userinfo.username 
            group by userinfo.username
            order by max(radacct.radacctid) desc limit 0,6) as tbl,radacct,userinfo
        where radacct.username=tbl.id and radacct.username = userinfo.username 
        group by radacct.username
        order by max(radacct.radacctid) desc;

And this is the DESCRIBE for the OLD TABLES - FAST query (0.4s):

   id * select_type *   table   type    possible_keys   key key_len ref rows    Extra *
   1    PRIMARY <derived2>  ALL {null}  {null}  {null}  {null}  6   Using temporary; Using filesort
   1    PRIMARY radacct ref username    username    66  tbl.id  5   
   1    PRIMARY userinfo    ref username    username    131 radius.radacct.username 1   Using where
   2    DERIVED radacct index   username    username    66  {null}  28768   Using index; Using temporary; Using filesort
   2    DERIVED userinfo    ref username,company    username    131 radius.radacct.username 1   Using where

And this is the DESCRIBE for the NEW TABLES - SLOW query (30s):

   id * select_type *   table   type    possible_keys   key key_len ref rows    Extra *
   1    PRIMARY <derived2>  ALL {null}  {null}  {null}  {null}  6   Using temporary; Using filesort
   1    PRIMARY radacct ref username    username    194 tbl.id  11  
   1    PRIMARY userinfo    ALL {null}  {null}  {null}  {null}  188911  Using where; Using join buffer
   2    DERIVED userinfo    ALL company {null}  {null}  {null}  188911  Using where; Using temporary; Using filesort
   2    DERIVED radacct ref username    username    194 func    11  Using where

Thanks.

EDIT:

I change charset on userinfo:

 alter table radius.userinfo convert to character set utf8 collate utf8_unicode_ci;

Now the NEW - Slow query runs in 3 seconds but still not as fast as the OLD database.

Also I change VARCHAR lenght to be the same on both querys and still not going by index like is does with the OLD database.

  ALTER TABLE radius.userinfo CHANGE username username VARCHAR(64);

I'm just running the subquery that goes slow:

 select radacct.username as id
        from radacct,userinfo
        where userinfo.company=98
        and radacct.username = userinfo.username 
        group by userinfo.username
        order by max(radacct.radacctid) desc limit 0,6

This is the DESCRIBE OLD - Fast:

 id select_type table   type    possible_keys   key key_len ref rows    Extra
 1  SIMPLE  radacct index   username    username    66      37545   Using index; Using temporary; Using filesort
 1  SIMPLE  userinfo    ref username,company    username    131 radius.radacct.username 1   Using where

This is the DESCRIBE NEW - Slow:

 id select_type table   type    possible_keys   key key_len ref rows    Extra
 1  SIMPLE  radacct ALL username                56879   Using temporary; Using filesort
 1  SIMPLE  userinfo    ref company,username    username    195 radius.radacct.username 1   Using where

Why is not getting the radacct.username index ?

EDIT 2: Add new definition with new COLLATION-CHARSET.

 CREATE TABLE `radacct` (
   `radacctid` bigint(21) NOT NULL AUTO_INCREMENT,
   `acctsessionid` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `acctuniqueid` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `username` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `groupname` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `realm` varchar(64) COLLATE utf8_unicode_ci DEFAULT '',
   `nasipaddress` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `nasportid` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
   `nasporttype` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
   `acctstarttime` datetime DEFAULT NULL,
   `acctstoptime` datetime DEFAULT NULL,
   `acctsessiontime` int(12) DEFAULT NULL,
   `acctauthentic` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
   `connectinfo_start` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
   `connectinfo_stop` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
   `acctinputoctets` bigint(20) DEFAULT NULL,
   `acctoutputoctets` bigint(20) DEFAULT NULL,
   `calledstationid` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `callingstationid` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `acctterminatecause` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `servicetype` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
   `framedprotocol` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
   `framedipaddress` varchar(15) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
   `acctstartdelay` int(12) DEFAULT NULL,
   `acctstopdelay` int(12) DEFAULT NULL,
   `xascendsessionsvrkey` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`radacctid`),
   KEY `framedipaddress` (`framedipaddress`),
   KEY `acctsessionid` (`acctsessionid`),
   KEY `acctsessiontime` (`acctsessiontime`),
   KEY `acctuniqueid` (`acctuniqueid`),
   KEY `acctstarttime` (`acctstarttime`),
   KEY `acctstoptime` (`acctstoptime`),
   KEY `nasipaddress` (`nasipaddress`),
   KEY `username` (`username`)
 ) ENGINE=MyISAM AUTO_INCREMENT=3607301 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

 CREATE TABLE `userinfo` (
   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
   `username` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
   `firstname` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `lastname` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `email` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `department` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `company` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `workphone` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `homephone` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `mobilephone` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `address` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `city` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `state` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `country` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
   `zip` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `notes` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
   `changeuserinfo` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
   `portalloginpassword` varchar(128) COLLATE utf8_unicode_ci DEFAULT '',
   `enableportallogin` int(32) DEFAULT '0',
   `creationdate` datetime DEFAULT '0000-00-00 00:00:00',
   `creationby` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
   `updatedate` datetime DEFAULT '0000-00-00 00:00:00',
   `updateby` varchar(128) COLLATE utf8_unicode_ci DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `company` (`company`),
   KEY `username` (`username`)
 ) ENGINE=MyISAM AUTO_INCREMENT=191546 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

score 0 · Answer 1 · answered Jul 01 '15 at 23:50

0

I see 3 issues:

company = 98
`company` varchar(200) DEFAULT NULL,

If it is a number then use a numeric datatype.

Replace

KEY `company` (`company`)

with

INDEX(company, username)

But perhaps the real problem is this:

radacct.username = userinfo.username

with one being latin1 and the other being utf8.

answered Jul 01 '15 at 23:50

Rick James

135,179
13
127
222

Hi Rick, and thanks, I edit my post with new information. Changing charset makes run faster the query. INDEX(company,username) doesn't makes any differrence. And company = 98 or company ='98' the same. Now the varchar lenght is the same, charset is the same but is not using the index. – Oscar Jofre Jul 02 '15 at 07:49
Run _both_ `SHOW CREATE TABLEs` again -- to verify that both `username` have the same `CHARACTER SET` _and_ `COLLATION`. The exact sequence of actions could have screwed things up still. – Rick James Jul 02 '15 at 18:50
(Assuming you have a new enough version...) Please provide `EXTEND FORMAT=JSON SELECT ...` for at least the "new, slow" one. – Rick James Jul 02 '15 at 18:51
Rick: both tables same collate: `username` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', same ENGINE=MySAM, same CHARSET. There is a difference in bettwen mysql versions that can make the difference: NEW-SLOW-MYSQL: version 5.1.73-0ubuntu0.10.04.1, OLD-FAST-MYSQL: version 5.5.32-0ubuntu0.12.04.1-log. Can this make the difference ? – Oscar Jofre Jul 03 '15 at 20:50
Please update the Question to reflect the consistency in the `CHARACTER SET`. And note that a _column_ can have a different `CHARACTER SET` than the _table_. – Rick James Jul 04 '15 at 00:49
I believe there is no difference in `CHARACTER SET` / `COLLATION` handling between 5.1.73 and 5.5.32. MySQL was burned badly when they changed the collation of ß in 5.1.24. – Rick James Jul 04 '15 at 00:51
Rick, updated Question with new definition tables. Still not using index properly, – Oscar Jofre Jul 04 '15 at 08:48
The Engine is important. In MyISAM, `INDEX(username)` is just `username`. In InnoDB, the PK is tacked on, so it is really `(username, redacctid)`. Notice that the fast `EXPLAIN` said "Using index"; that is because it only needed the index. Switching to MyISAM made it work harder. – Rick James Jul 05 '15 at 06:26
So with MyISAM what i can do to make it faster query ? – Oscar Jofre Jul 06 '15 at 19:58
Start with `INDEX(username, redacctid)`. More index discussion in [my blog](http://mysql.rjweb.org/doc.php/index_cookbook_mysql). – Rick James Jul 06 '15 at 20:47

Mysql Index COLLATE utf8_unicode_ci

1 Answers1