query optimization - MySQL slow group by date_format -


i have table daloradius:

{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=1797353 default charset=latin1; 

}

with 1797353 rows.

this query delays 0.7s:

select count(distinct(right(radacct.username,17))) navegant,0,0             radacct,userinfo             userinfo.company='98' , userinfo.username=radacct.username    

explain:

id  select_type table   type    possible_keys   key key_len ref rows    1   simple  userinfo    ref username,company    company 203 const   4811    using 1   simple  radacct ref username    username    66  radius.userinfo.username    7   using where; using index    

but when add date_format(acctstarttime,'%h') takes more tan 4s run query:

select   date_format(acctstarttime,'%h') aa,count(distinct(right(radacct.username,17))) navegant,0,0             radacct,userinfo             userinfo.company='98' , userinfo.username=radacct.username             group aa 

explain:

id  select_type table   type    possible_keys   key key_len ref rows    1   simple  userinfo    ref username,company    company 203 const   4811    using where; using temporary; using filesort 1   simple  radacct ref username    username    66  radius.userinfo.username    7   using 

how can make query faster?

i have had lot of issues performance group by. have ended using sub selects instead in cases. article super helpful me:

http://kccoder.com/mysql/join-group-by-performance/

essentially boils down taking this: date_format(acctstarttime,'%h') aa

out of select statement , putting sub select this:

select count(distinct(right(radacct.username,17))) navegant,0,0 radacct,userinfo, select date_format(acctstarttime,'%h') aa some_table
userinfo.company='98' , userinfo.username=radacct.username

i know syntax isn't correct, should give general idea.

hope helps, brian


Comments

Popular posts from this blog

css - SVG using textPath a symbol not rendering in Firefox -

Java 8 + Maven Javadoc plugin: Error fetching URL -

datatable - Matlab struct computations -