0

Query is taking 32-42 seconds , because "radacct" table has more than 5 million records.

Is there a way to speed up or boost my database? How to optimize this very slow query.


Server Operating System is CentOS 7 (x64) with 12GB RAM
my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
innodb_buffer_pool_size=6000M
innodb_log_file_size=1572864000
innodb_file_per_table
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT

# Optimise MySQL for low Memory
wait_timeout            = 120
max_allowed_packet      = 8M
#thread_buffer_size      = 64
#sort_buffer_size        = 2M
#bulk_insert_buffer_size = 4M
thread_cache_size        = 16
tmp_table_size          = 512M
max_heap_table_size     = 512M
key_buffer_size         = 300M
table_open_cache        = 1000
long_query_time         = 5
#myisam_sort_buffer_size = 2M
#read_buffer_size        = 128K
#read_rnd_buffer_size    = 256K
#query_cache_limit       = 512K
#query_cache_size        = 128M
#query_cache_type        = 2

#general-log = 1
#general-log-file=queries.log
log-slow-queries=/var/log/mariadb/log-slow-queries.log
log-output=file

# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

"radacct" table has records from year 2014 till now .

SHOW CREATE TABLE rm_managers

CREATE TABLE `rm_managers` (
 `managername` varchar(64) NOT NULL,
 `parentmanagername` varchar(64) DEFAULT NULL,
 `mangroupid` int(11) NOT NULL,
 `acctstart` int(6) DEFAULT NULL,
 `acctend` int(6) DEFAULT NULL,
 `password` varchar(32) NOT NULL,
 `firstname` varchar(50) NOT NULL,
 `lastname` varchar(50) NOT NULL,
 `phone` varchar(15) NOT NULL,
 `mobile` varchar(15) NOT NULL,
 `address` varchar(50) NOT NULL,
 `city` varchar(50) NOT NULL,
 `zip` varchar(8) NOT NULL,
 `country` varchar(50) NOT NULL,
 `state` varchar(50) NOT NULL,
 `comment` varchar(200) NOT NULL,
 `company` varchar(50) NOT NULL,
 `vatid` varchar(40) NOT NULL,
 `email` varchar(50) NOT NULL,
 `balance` decimal(20,2) NOT NULL,
 `perm_listusers` tinyint(1) NOT NULL,
 `perm_createusers` tinyint(1) NOT NULL,
 `perm_editusers` tinyint(1) NOT NULL,
 `perm_edituserspriv` tinyint(1) NOT NULL,
 `perm_deleteusers` tinyint(1) NOT NULL,
 `perm_listmanagers` tinyint(1) NOT NULL,
 `perm_createmanagers` tinyint(1) NOT NULL,
 `perm_editmanagers` tinyint(1) NOT NULL,
 `perm_deletemanagers` tinyint(1) NOT NULL,
 `perm_listservices` tinyint(1) NOT NULL,
 `perm_createservices` tinyint(1) NOT NULL,
 `perm_editservices` tinyint(1) NOT NULL,
 `perm_deleteservices` tinyint(1) NOT NULL,
 `perm_listonlineusers` tinyint(1) NOT NULL,
 `perm_listinvoices` tinyint(1) NOT NULL,
 `perm_trafficreport` tinyint(1) NOT NULL,
 `perm_traffic` tinyint(1) NOT NULL,
 `perm_addcredits` tinyint(1) NOT NULL,
 `perm_negbalance` tinyint(1) NOT NULL,
 `perm_listallinvoices` tinyint(1) NOT NULL,
 `perm_showinvtotals` tinyint(1) NOT NULL,
 `perm_logout` tinyint(1) NOT NULL,
 `perm_cardsys` tinyint(1) NOT NULL,
 `perm_editinvoice` tinyint(1) NOT NULL,
 `perm_allusers` tinyint(1) NOT NULL,
 `perm_allowdiscount` tinyint(1) NOT NULL,
 `perm_enwriteoff` tinyint(1) NOT NULL,
 `perm_accessap` tinyint(1) NOT NULL,
 `perm_cts` tinyint(1) NOT NULL,
 `enablemanager` tinyint(1) NOT NULL,
 `lang` varchar(30) NOT NULL,
 `createddate` date DEFAULT NULL,
 PRIMARY KEY (`managername`),
 UNIQUE KEY `managername` (`managername`),
 KEY `managername_2` (`managername`),
 KEY `parentmanagername` (`parentmanagername`),
 KEY `mangroupid` (`mangroupid`),
 KEY `balance` (`balance`),
 KEY `enablemanager` (`enablemanager`),
 KEY `rm_managers_index_1` (`mangroupid`,`managername`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8

SHOW CREATE TABLE rm_services

CREATE TABLE `rm_services` (
 `srvid` int(11) NOT NULL,
 `srvname` varchar(50) NOT NULL,
 `descr` varchar(255) NOT NULL,
 `downrate` int(11) NOT NULL,
 `uprate` int(11) NOT NULL,
 `limitdl` tinyint(1) NOT NULL,
 `limitul` tinyint(1) NOT NULL,
 `limitcomb` tinyint(1) NOT NULL,
 `limitexpiration` tinyint(1) NOT NULL,
 `limituptime` tinyint(1) NOT NULL,
 `poolname` varchar(50) NOT NULL,
 `unitprice` decimal(25,6) NOT NULL,
 `unitpriceadd` decimal(25,6) NOT NULL,
 `timebaseexp` tinyint(1) NOT NULL,
 `timebaseonline` tinyint(1) NOT NULL,
 `timeunitexp` int(11) NOT NULL,
 `timeunitonline` int(11) NOT NULL,
 `trafficunitdl` int(11) NOT NULL,
 `trafficunitul` int(11) NOT NULL,
 `trafficunitcomb` int(11) NOT NULL,
 `inittimeexp` int(11) NOT NULL,
 `inittimeonline` int(11) NOT NULL,
 `initdl` int(11) NOT NULL,
 `initul` int(11) NOT NULL,
 `inittotal` int(11) NOT NULL,
 `srvtype` tinyint(1) NOT NULL,
 `timeaddmodeexp` tinyint(1) NOT NULL,
 `timeaddmodeonline` tinyint(1) NOT NULL,
 `trafficaddmode` tinyint(1) NOT NULL,
 `monthly` tinyint(1) NOT NULL,
 `enaddcredits` tinyint(1) NOT NULL,
 `minamount` int(20) NOT NULL,
 `minamountadd` int(20) NOT NULL,
 `resetcounters` tinyint(1) NOT NULL,
 `pricecalcdownload` tinyint(1) NOT NULL,
 `pricecalcupload` tinyint(1) NOT NULL,
 `pricecalcuptime` tinyint(1) NOT NULL,
 `unitpricetax` decimal(25,6) NOT NULL,
 `unitpriceaddtax` decimal(25,6) NOT NULL,
 `enableburst` tinyint(1) NOT NULL,
 `dlburstlimit` int(11) NOT NULL,
 `ulburstlimit` int(11) NOT NULL,
 `dlburstthreshold` int(11) NOT NULL,
 `ulburstthreshold` int(11) NOT NULL,
 `dlbursttime` int(11) NOT NULL,
 `ulbursttime` int(11) NOT NULL,
 `enableservice` int(11) NOT NULL,
 `dlquota` bigint(20) NOT NULL,
 `ulquota` bigint(20) NOT NULL,
 `combquota` bigint(20) NOT NULL,
 `timequota` bigint(20) NOT NULL,
 `priority` smallint(6) NOT NULL,
 `nextsrvid` int(11) NOT NULL,
 `dailynextsrvid` int(11) NOT NULL,
 `disnextsrvid` int(11) NOT NULL,
 `availucp` tinyint(1) NOT NULL,
 `renew` tinyint(1) NOT NULL,
 `carryover` tinyint(1) NOT NULL,
 `policymapdl` varchar(50) NOT NULL,
 `policymapul` varchar(50) NOT NULL,
 `custattr` varchar(255) NOT NULL,
 `gentftp` tinyint(1) NOT NULL,
 `cmcfg` varchar(10240) NOT NULL,
 `advcmcfg` tinyint(1) NOT NULL,
 `addamount` int(11) NOT NULL,
 `ignstatip` tinyint(1) NOT NULL,
 PRIMARY KEY (`srvid`),
 KEY `srvname` (`srvname`),
 KEY `srvid` (`srvid`),
 KEY `rm_services_index_1` (`srvid`,`unitprice`,`unitpricetax`),
 KEY `dailynextsrvid` (`dailynextsrvid`),
 KEY `nextsrvid` (`nextsrvid`),
 KEY `disnextsrvid` (`disnextsrvid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

SHOW CREATE TABLE rm_users

CREATE TABLE `rm_users` (
 `username` varchar(64) NOT NULL,
 `password` varchar(32) NOT NULL,
 `groupid` int(11) NOT NULL,
 `enableuser` tinyint(1) NOT NULL,
 `uplimit` bigint(20) NOT NULL,
 `downlimit` bigint(20) NOT NULL,
 `comblimit` bigint(20) NOT NULL,
 `firstname` varchar(50) NOT NULL,
 `lastname` varchar(50) NOT NULL,
 `company` varchar(50) NOT NULL,
 `phone` varchar(15) NOT NULL,
 `mobile` varchar(15) NOT NULL,
 `address` varchar(100) NOT NULL,
 `city` varchar(50) NOT NULL,
 `zip` varchar(8) NOT NULL,
 `country` varchar(50) NOT NULL,
 `state` varchar(50) NOT NULL,
 `comment` varchar(500) NOT NULL,
 `gpslat` decimal(17,14) NOT NULL,
 `gpslong` decimal(17,14) NOT NULL,
 `mac` varchar(17) NOT NULL,
 `macwlan` varchar(17) DEFAULT NULL,
 `usemacauth` tinyint(1) NOT NULL,
 `expiration` datetime NOT NULL,
 `uptimelimit` bigint(20) NOT NULL,
 `srvid` int(11) NOT NULL,
 `staticipcm` varchar(15) NOT NULL,
 `staticipcpe` varchar(15) NOT NULL,
 `ipmodecm` tinyint(1) NOT NULL,
 `ipmodecpe` tinyint(1) NOT NULL,
 `poolidcm` int(11) NOT NULL,
 `poolidcpe` int(11) NOT NULL,
 `createdon` date NOT NULL,
 `acctype` tinyint(1) NOT NULL,
 `credits` decimal(20,2) NOT NULL,
 `cardfails` tinyint(4) NOT NULL,
 `createdby` varchar(64) NOT NULL,
 `owner` varchar(64) NOT NULL,
 `taxid` varchar(40) NOT NULL,
 `email` varchar(100) NOT NULL,
 `maccm` varchar(17) NOT NULL,
 `custattr` varchar(255) NOT NULL,
 `warningsent` tinyint(1) NOT NULL,
 `verifycode` varchar(10) NOT NULL,
 `verified` tinyint(1) NOT NULL,
 `selfreg` tinyint(1) NOT NULL,
 `verifyfails` tinyint(4) NOT NULL,
 `verifysentnum` tinyint(4) NOT NULL,
 `verifymobile` varchar(15) NOT NULL,
 `contractid` varchar(50) NOT NULL,
 `contractvalid` date NOT NULL,
 `actcode` varchar(60) NOT NULL,
 `pswactsmsnum` tinyint(4) NOT NULL,
 `alertemail` tinyint(1) NOT NULL,
 `alertsms` tinyint(1) NOT NULL,
 `lang` varchar(30) NOT NULL,
 `lastlogoff` datetime DEFAULT NULL,
 PRIMARY KEY (`username`),
 KEY `srvid` (`srvid`),
 KEY `groupid` (`groupid`),
 KEY `enableuser` (`enableuser`),
 KEY `firstname` (`firstname`),
 KEY `lastname` (`lastname`),
 KEY `company` (`company`),
 KEY `phone` (`phone`),
 KEY `mobile` (`mobile`),
 KEY `address` (`address`),
 KEY `city` (`city`),
 KEY `zip` (`zip`),
 KEY `country` (`country`),
 KEY `state` (`state`),
 KEY `comment` (`comment`(255)),
 KEY `mac` (`mac`),
 KEY `acctype` (`acctype`),
 KEY `email` (`email`),
 KEY `maccm` (`maccm`),
 KEY `owner` (`owner`),
 KEY `staticipcpe` (`staticipcpe`),
 KEY `staticipcm` (`staticipcm`),
 KEY `expiration` (`expiration`),
 KEY `createdon` (`createdon`),
 KEY `contractid` (`contractid`),
 KEY `contractvalid` (`contractvalid`),
 KEY `lastlogoff` (`lastlogoff`),
 KEY `username` (`username`),
 KEY `rm_users_index_1` (`acctype`,`srvid`,`owner`,`expiration`),
 KEY `rm_users_srvid_acctype_username` (`srvid`,`acctype`,`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

SHOW CREATE TABLE radacct

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,
 `_accttime` datetime DEFAULT NULL,
 `_srvid` int(11) DEFAULT NULL,
 `_dailynextsrvactive` tinyint(1) DEFAULT NULL,
 `_apid` int(11) 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`),
 KEY `_AcctTime` (`_accttime`),
 KEY `callingstationid` (`callingstationid`),
 KEY `_srvid` (`_srvid`),
 KEY `radacctid` (`radacctid`),
 KEY `acctoutputoctets` (`acctoutputoctets`),
 KEY `acctinputoctets` (`acctinputoctets`),
 KEY `radacct_username_acctstoptime` (`username`,`acctstoptime`),
 KEY `radacct_username_acctstarttime` (`username`,`acctstarttime`),
 KEY `radacct_srvid_acctstoptime` (`_srvid`,`acctstoptime`),
 KEY `radacct_acctstarttime_username` (`acctstarttime`,`username`),
 KEY `radacct_acctstarttime_acctuniqueid` (`acctstarttime`,`acctuniqueid`),
 KEY `radacct_overal_traffic` (`acctstarttime`,`username`,`acctoutputoctets`,`acctinputoctets`,`acctsessiontime`),
 KEY `radacct_traffic_summary` (`acctstarttime`,`username`,`acctsessiontime`,`acctoutputoctets`,`acctinputoctets`),
 KEY `radacct_acctstoptime_radacctid_username` (`acctstoptime`,`radacctid`,`username`)
) ENGINE=InnoDB AUTO_INCREMENT=5291146 DEFAULT CHARSET=utf8

SHOW CREATE TABLE rm_phpsess

CREATE TABLE `rm_phpsess` (
 `managername` varchar(64) NOT NULL,
 `ip` varchar(15) NOT NULL,
 `sessid` varchar(64) NOT NULL,
 `lastact` datetime NOT NULL,
 `closed` tinyint(1) NOT NULL,
 KEY `managername` (`managername`),
 KEY `lastact` (`lastact`),
 KEY `ip` (`ip`),
 KEY `closed` (`closed`),
 KEY `sessid` (`sessid`),
 KEY `rm_phpsess_index_1` (`managername`,`lastact`,`ip`),
 KEY `rm_phpsess_index_2` (`lastact`),
 KEY `rm_phpsess_index_3` (`managername`,`lastact`,`closed`,`ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Query output:

| ManagerName | Balance  | Group       | ActiveUsers | ExpiredUsers | DisabledUsers | SUM     | LastSeen                 |
|-------------|----------|-------------|-------------|--------------|---------------|---------|--------------------------|
| {admin}     | {-8240$} | {Admins}    | {19}        | {6}          | {2}           | {45$}   | {2017-12-23 10.20.20.40} |
| {manager1}  | {2100$}  | {Resellers} | {221}       | {13}         | {15}          | {1900$} | {2017-12-21 10.20.20.41} |

Server Version : 5.5.52-MariaDB MariadB Server


$query = "SELECT
                                ( SELECT COUNT(rm_users.username) FROM `rm_users` 
               LEFT JOIN `rm_services` ON rm_users.srvid = rm_services.srvid 
               WHERE 
               rm_users.owner = rm_managers.managername AND rm_users.acctype = 0 AND rm_users.enableuser = 1 
               AND
               (IF (rm_services.limitdl = 1, rm_users.downlimit - (SELECT COALESCE(SUM(acctoutputoctets), 0)
               FROM radacct WHERE radacct.username = rm_users.username) - (SELECT COALESCE(SUM(dlbytes), 0) 
               FROM rm_radacct WHERE rm_radacct.username = rm_users.username) , 1) <= 0
              OR 
               IF (rm_services.limitul = 1, rm_users.uplimit - (SELECT COALESCE(SUM(acctinputoctets), 0)
               FROM radacct WHERE radacct.username = rm_users.username) - (SELECT COALESCE(SUM(ulbytes), 0) 
               FROM rm_radacct WHERE rm_radacct.username = rm_users.username) , 1) <= 0
              OR
               IF (rm_services.limitcomb = 1, rm_users.comblimit -
               (SELECT COALESCE(SUM(acctinputoctets + acctoutputoctets), 0)
               FROM radacct WHERE radacct.username = rm_users.username) +
               (SELECT COALESCE(SUM(ulbytes + dlbytes), 0)
               FROM rm_radacct WHERE rm_radacct.username = rm_users.username) , 1) <= 0
              OR
               IF (rm_services.limituptime = 1, rm_users.uptimelimit - (SELECT COALESCE(SUM(acctsessiontime), 0)
               FROM radacct WHERE radacct.username = rm_users.username) + (SELECT COALESCE(SUM(acctsessiontime), 0) 
               FROM rm_radacct WHERE rm_radacct.username = rm_users.username) , 1) <= 0
              OR          
               IF (rm_services.limitexpiration=1, UNIX_TIMESTAMP(rm_users.expiration) - UNIX_TIMESTAMP(NOW()), 1) <= 0) ) AS expired_users ,

              ( SELECT COUNT(rm_users.username) FROM `rm_users` 
               LEFT JOIN `rm_services` ON rm_users.srvid = rm_services.srvid 

               WHERE 
                 owner = rm_managers.managername AND enableuser=1 AND acctype = 0 
                          AND 
                          (rm_services.limitdl = 0 OR IF (rm_services.limitdl =1, downlimit -
                  (SELECT COALESCE(SUM(acctoutputoctets), 0) FROM radacct WHERE radacct.username = rm_users.username) -
                      (SELECT COALESCE(SUM(dlbytes), 0) FROM rm_radacct WHERE rm_radacct.username = rm_users.username) , 1) > 0)
                      AND
                      (rm_services.limitul = 0 OR IF (rm_services.limitul =1, uplimit -
                      (SELECT COALESCE(SUM(acctinputoctets), 0) FROM radacct WHERE radacct.username = rm_users.username) -
                          (SELECT COALESCE(SUM(ulbytes ), 0) FROM rm_radacct WHERE rm_radacct.username = rm_users.username) , 1) > 0)
                          AND
                          (rm_services.limitcomb = 0 OR IF (rm_services.limitcomb =1, comblimit -
                          (SELECT COALESCE(SUM(acctinputoctets + acctoutputoctets), 0) FROM radacct WHERE radacct.username = rm_users.username) +
                          (SELECT COALESCE(SUM(ulbytes + dlbytes), 0) FROM rm_radacct WHERE rm_radacct.username = rm_users.username) , 1) > 0)
                          AND
                          (rm_services.limituptime = 0 OR IF (rm_services.limituptime=1, uptimelimit -
                          (SELECT COALESCE(SUM(acctsessiontime), 0) FROM radacct WHERE radacct.username = rm_users.username) - (SELECT COALESCE(SUM(acctsessiontime), 0) FROM rm_radacct WHERE rm_radacct.username = rm_users.username) , 1) > 0)
                          AND
                          (rm_services.limitexpiration = 0 OR IF (limitexpiration=1, UNIX_TIMESTAMP(expiration) - UNIX_TIMESTAMP(NOW()), 1) > 0)
             ) AS active_users ,
             ( SELECT ip FROM rm_phpsess WHERE rm_managers.managername = rm_phpsess.managername ORDER BY lastact DESC LIMIT 1) AS ipip,


             ( SELECT COUNT(username) FROM `rm_users` WHERE owner = rm_managers.managername AND acctype = 0 AND enableuser = 0 ) AS disabled_users,

             ( SELECT ( SUM(rm_services.unitprice) + SUM(rm_services.unitpricetax) ) FROM `rm_users` 
             LEFT JOIN `rm_services` ON rm_users.srvid = rm_services.srvid 
             WHERE 
                rm_users.owner = rm_managers.managername AND rm_users.acctype = 0 AND rm_users.expiration > NOW()) AS sum_users,




             rm_managers.firstname,
             rm_managers.lastname,
             rm_managers.managername,
             rm_managers.balance,
             rm_managers.comment,
             rm_managers.enablemanager,
             rm_managers.country,
             rm_managers.city,
             rm_managers.state,
             rm_managers.email,
             rm_managers.zip,
             rm_managers.address,
             rm_managers.company,
             rm_managers.phone,
             rm_managers.mobile,
             rm_managergroups.mangroupid,
             rm_managergroups.mangroupname,
             t2.lastseen,
             t2.onlinecount,
             t2.lastip 

             FROM `rm_managers`  
             LEFT JOIN `rm_managergroups` ON rm_managergroups.mangroupid = rm_managers.mangroupid 
             LEFT OUTER JOIN (SELECT rm_phpsess.managername, MAX(rm_phpsess.lastact) AS lastseen, ( COUNT(IF(closed=0,1,NULL) ) ) AS onlinecount, (ip) AS lastip  FROM `rm_phpsess` GROUP BY rm_phpsess.managername ORDER BY lastact desc) t2 ON t2.managername = rm_managers.managername

I added EXPLAIN EXTENDED to the query as shown below:

| id | select_type | table            | type     | possible_keys | key                            | key_len | ref                            | rows  | filtered | Extra                           |
|----|-------------|------------------|----------|---------------|--------------------------------|---------|--------------------------------|-------|----------|---------------------------------|
| 1  | PRIMARY     | rm_managers      | ALL      | NULL          | NULL                           | NULL    | NULL                           | 12    | 100      |                                 |
| 1  | PRIMARY     | rm_managergroups | eq_ref   | PRIMARY       | PRIMARY                        | 4       | radius.rm_managers.mangroupid  | 1     | 100      |                                 |
| 1  | PRIMARY     | ref              | key0     | key0          |                                | 195     | radius.rm_managers.managername | 333   | 100      |                                 |
| 23 | DERIVED     | rm_phpsess       | index    | NULL          | managername                    | 194     | NULL                           | 33371 | 100      | Using temporary; Using filesort |
| 22 | DEPENDENT   | SUBQUERY         | rm_users | ref           | acctype,owner,expiration owner | 194     | radius.rm_managers.managername | 28    | 100      | Using where;                    |

etc ...

THE SLOWEST QUERY :

How to optimize this code or if its possible to replace OR clause with UNION , maybe performance would get better!.

( SELECT COUNT(rm_users.username) FROM `rm_users` 
           LEFT JOIN `rm_services` ON rm_users.srvid = rm_services.srvid 
           WHERE 
           rm_users.owner = rm_managers.managername AND rm_users.acctype = '0' AND rm_users.enableuser = '1' 
           AND
           (IF (rm_services.limitdl = 1, rm_users.downlimit - (SELECT COALESCE(SUM(acctoutputoctets), 0)
           FROM radacct WHERE radacct.username = rm_users.username) - (SELECT COALESCE(SUM(dlbytes), 0) 
           FROM rm_radacct WHERE rm_radacct.username = rm_users.username) , 1) <= 0
          OR 
           IF (rm_services.limitul = 1, rm_users.uplimit - 
           (SELECT COALESCE(SUM(acctinputoctets), 0) FROM radacct WHERE radacct.username = rm_users.username) - 
           (SELECT COALESCE(SUM(ulbytes), 0) FROM rm_radacct WHERE rm_radacct.username = rm_users.username) , 1) <= 0
          OR
           IF (rm_services.limitcomb = 1, rm_users.comblimit - 
           (SELECT COALESCE(SUM(acctinputoctets + acctoutputoctets), 0) FROM radacct WHERE radacct.username = rm_users.username) +
           (SELECT COALESCE(SUM(ulbytes + dlbytes), 0) FROM rm_radacct WHERE rm_radacct.username = rm_users.username) , 1) <= 0
          OR
           IF (rm_services.limituptime = 1, rm_users.uptimelimit - (SELECT COALESCE(SUM(acctsessiontime), 0)
           FROM radacct WHERE radacct.username = rm_users.username) + (SELECT COALESCE(SUM(acctsessiontime), 0) 
           FROM rm_radacct WHERE rm_radacct.username = rm_users.username) , 1) <= 0
          OR          
           IF (rm_services.limitexpiration=1, UNIX_TIMESTAMP(rm_users.expiration) - UNIX_TIMESTAMP(NOW()), 1) <= 0) ) AS expired_users ,

          ( SELECT COUNT(rm_users.username) FROM `rm_users` 
           LEFT JOIN `rm_services` ON rm_users.srvid = rm_services.srvid 

           WHERE 
             owner = rm_managers.managername AND enableuser='1' AND acctype = '0' 
                      AND 
                      (rm_services.limitdl = 0 OR IF (rm_services.limitdl =1, downlimit -
              (SELECT COALESCE(SUM(acctoutputoctets), 0) FROM radacct WHERE radacct.username = rm_users.username) -
                  (SELECT COALESCE(SUM(dlbytes), 0) FROM rm_radacct WHERE rm_radacct.username = rm_users.username) , 1) > 0)
                  AND
                  (rm_services.limitul = 0 OR IF (rm_services.limitul =1, uplimit -
                  (SELECT COALESCE(SUM(acctinputoctets), 0) FROM radacct WHERE radacct.username = rm_users.username) -
                      (SELECT COALESCE(SUM(ulbytes ), 0) FROM rm_radacct WHERE rm_radacct.username = rm_users.username) , 1) > 0)
                      AND
                      (rm_services.limitcomb = 0 OR IF (rm_services.limitcomb =1, comblimit -
                      (SELECT COALESCE(SUM(acctinputoctets + acctoutputoctets), 0) FROM radacct WHERE radacct.username = rm_users.username) +
                      (SELECT COALESCE(SUM(ulbytes + dlbytes), 0) FROM rm_radacct WHERE rm_radacct.username = rm_users.username) , 1) > 0)
                      AND
                      (rm_services.limituptime = 0 OR IF (rm_services.limituptime=1, uptimelimit -
                      (SELECT COALESCE(SUM(acctsessiontime), 0) FROM radacct WHERE radacct.username = rm_users.username) - (SELECT COALESCE(SUM(acctsessiontime), 0) FROM rm_radacct WHERE rm_radacct.username = rm_users.username) , 1) > 0)
                      AND
                      (rm_services.limitexpiration = 0 OR IF (limitexpiration=1, UNIX_TIMESTAMP(expiration) - UNIX_TIMESTAMP(NOW()), 1) > 0)
         ) AS active_users ,
         ( SELECT ip FROM rm_phpsess WHERE rm_managers.managername = rm_phpsess.managername ORDER BY lastact DESC LIMIT 1) AS ipip,


         ( SELECT COUNT(username) FROM `rm_users` WHERE owner = rm_managers.managername AND acctype = '0' AND enableuser = '0' ) AS disabled_users,

         ( SELECT ( SUM(rm_services.unitprice) + SUM(rm_services.unitpricetax) ) FROM `rm_users` 
         LEFT JOIN `rm_services` ON rm_users.srvid = rm_services.srvid 
         WHERE 
            rm_users.owner = rm_managers.managername AND rm_users.acctype = '0' AND rm_users.expiration > '2017-12-28 06:47:20') AS sum_users,

closed as off-topic by ypercubeᵀᴹ, Erik Darling, mustaccio, Mr.Brownstone, McNets Dec 24 '17 at 21:05

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Tip of the iceberg - the question or comments reveal an underlying issue that would need extensive investigation by a consultant or database vendor support team: issues like this do not fit the SE Q&A model well. For more information see this meta post." – ypercubeᵀᴹ, Erik Darling, Mr.Brownstone, McNets
If this question can be reworded to fit the rules in the help center, please edit the question.

  • I suggest you rewrite all those (doubly nested) correlated subqueries into LEFT JOINs. – ypercubeᵀᴹ Dec 23 '17 at 13:18
  • I am not sure if it can be optimized , because this query is showing me each reseller with the amount of active users , amount of disabled users and the sum of active users. – Ghassan Dec 23 '17 at 14:41
  • so if we start with "admin" then the engine should list all managers from TABLE 'rm_managers' and then for each manager , then engine should go to TABLE 'rm_users' then start to count all active users that belongs to 'admin' (by counting the total consumed traffic 'rm_radacct' and 'radacct' tables and compare dates ).We call it ACTIVE USERS (NOT DISABLED AND NOT EXPIRED USERS) etc... TABLE 'rm_phpsess' handles all sessions of each manager and it is responsible to track managers logins (Last Seen with the last ip). – Ghassan Dec 23 '17 at 14:50
  • I just suggested you rewrite the query without correlated subqueries. It's surely possible but I don't think any user will bother to attempt rewriting it. – ypercubeᵀᴹ Dec 23 '17 at 15:28
  • Do you mean sub queries that act as field (column) ?. Okay i can instead replace them with LEFT JOIN ( SUB QUERY EXAMPLE). Unfortunately, I will be adding LEFT JOIN to the same table multiple times because i will be adding MULTIPLE IF CONDITIONS and multi cases anyway i hope someone direct me to do something and point me to the right direction . – Ghassan Dec 23 '17 at 19:08
2

Several recommendations that can help here:

  1. Replace the subqueries in the SELECT clause with temporary tables that you JOIN to in the original query (scroll down for the actual temporary table creation and query transformations). The advantage of these temporary tables instead of subqueries is that each of these small temp table queries can be indexed so the search will be lightning fast, instead of the subqueries which can't be indexed properly in many cases.
  2. Add explicitly ORDER BY NULL in places where you have a GROUP BY clause but you do not care about ordering.
  3. Find all places you're comparing an integer column with a "string value" and avoid that, as it causes an unnecessary cast. For example, replace AND acctype = '0' with AND acctype = 0
  4. Try to avoid OR conditions if you can, as they are not indexable.
  5. Create the proper indexes for the query (see below).

The temporary tables creation and the transformed query:

CREATE TEMPORARY TABLE IF NOT EXISTS temp1 AS SELECT
        COUNT(rm_users.username) AS expired_users,
        owner 
    FROM
        rm_users 
    LEFT JOIN
        `rm_services` 
            ON rm_users.srvid = rm_services.srvid 
    WHERE
        1 = 1 
        AND rm_users.acctype = '0' 
        AND rm_users.enableuser = '1' 
        AND (
            if(rm_services.limitdl = 1, rm_users.downlimit - (SELECT
                COALESCE(SUM(radacct.acctoutputoctets),
                0) 
            FROM
                radacct 
            WHERE
                radacct.username = rm_users.username) - (SELECT
                COALESCE(SUM(dlbytes),
                0) 
            FROM
                rm_radacct 
            WHERE
                rm_radacct.username = rm_users.username), 1) <= 0 
            OR if(rm_services.limitul = 1, rm_users.uplimit - (SELECT
                COALESCE(SUM(radacct.acctinputoctets),
                0) 
            FROM
                radacct 
            WHERE
                radacct.username = rm_users.username) - (SELECT
                COALESCE(SUM(ulbytes),
                0) 
            FROM
                rm_radacct 
            WHERE
                rm_radacct.username = rm_users.username), 1) <= 0 
            OR if(rm_services.limitcomb = 1, rm_users.comblimit - (SELECT
                COALESCE(SUM(radacct.acctinputoctets + radacct.acctoutputoctets),
                0) 
            FROM
                radacct 
            WHERE
                radacct.username = rm_users.username) + (SELECT
                COALESCE(SUM(ulbytes + dlbytes),
                0) 
            FROM
                rm_radacct 
            WHERE
                rm_radacct.username = rm_users.username), 1) <= 0 
            OR if(rm_services.limituptime = 1, rm_users.uptimelimit - (SELECT
                COALESCE(SUM(radacct.acctsessiontime),
                0) 
            FROM
                radacct 
            WHERE
                radacct.username = rm_users.username) + (SELECT
                COALESCE(SUM(radacct.acctsessiontime),
                0) 
            FROM
                rm_radacct 
            WHERE
                rm_radacct.username = rm_users.username), 1) <= 0 
            OR if(rm_services.limitexpiration = 1, UNIX_TIMESTAMP(rm_users.expiration) - UNIX_TIMESTAMP(NOW()), 1) <= 0
        ) 
    GROUP BY
        owner 
    ORDER BY
        NULL

CREATE TEMPORARY TABLE IF NOT EXISTS temp2 AS SELECT
        COUNT(rm_users.username) AS active_users,
        owner 
    FROM
        `rm_users` 
    LEFT JOIN
        `rm_services` 
            ON rm_users.srvid = rm_services.srvid 
    WHERE
        1 = 1 
        AND rm_users.enableuser = '1' 
        AND rm_users.acctype = '0' 
        AND (
            rm_services.limitdl = 0 
            OR if(rm_services.limitdl = 1, rm_users.downlimit - (SELECT
                COALESCE(SUM(radacct.acctoutputoctets),
                0) 
            FROM
                radacct 
            WHERE
                radacct.username = rm_users.username) - (SELECT
                COALESCE(SUM(rm_radacct.dlbytes),
                0) 
            FROM
                rm_radacct 
            WHERE
                rm_radacct.username = rm_users.username), 1) > 0
        ) 
        AND (
            rm_services.limitul = 0 
            OR if(rm_services.limitul = 1, rm_radacct.uplimit - (SELECT
                COALESCE(SUM(radacct.acctinputoctets),
                0) 
            FROM
                radacct 
            WHERE
                radacct.username = rm_users.username) - (SELECT
                COALESCE(SUM(ulbytes),
                0) 
            FROM
                rm_radacct 
            WHERE
                rm_radacct.username = rm_users.username), 1) > 0
        ) 
        AND (
            rm_services.limitcomb = 0 
            OR if(rm_services.limitcomb = 1, rm_radacct.comblimit - (SELECT
                COALESCE(SUM(radacct.acctinputoctets + radacct.acctoutputoctets),
                0) 
            FROM
                radacct 
            WHERE
                radacct.username = rm_users.username) + (SELECT
                COALESCE(SUM(rm_radacct.ulbytes + rm_radacct.dlbytes),
                0) 
            FROM
                rm_radacct 
            WHERE
                rm_radacct.username = rm_users.username), 1) > 0
        ) 
        AND (
            rm_services.limituptime = 0 
            OR if(rm_services.limituptime = 1, rm_radacct.uptimelimit - (SELECT
                COALESCE(SUM(radacct.acctsessiontime),
                0) 
            FROM
                radacct 
            WHERE
                radacct.username = rm_users.username) - (SELECT
                COALESCE(SUM(radacct.acctsessiontime),
                0) 
            FROM
                rm_radacct 
            WHERE
                rm_radacct.username = rm_users.username), 1) > 0
        ) 
        AND (
            rm_services.limitexpiration = 0 
            OR if(rm_radacct.limitexpiration = 1, UNIX_TIMESTAMP(rm_radacct.expiration) - UNIX_TIMESTAMP(NOW()), 1) > 0
        ) 
    GROUP BY
        owner 
    ORDER BY
        NULL

CREATE TEMPORARY TABLE IF NOT EXISTS temp3 AS SELECT
        COUNT(rm_users.username) AS disabled_users,
        owner 
    FROM
        `rm_users` 
    WHERE
        1 = 1 
        AND rm_users.acctype = '0' 
        AND rm_users.enableuser = '0' 
    GROUP BY
        owner 
    ORDER BY
        NULL

SELECT
        temp1.expired_users,
        temp2.active_users,
        (SELECT
            rm_phpsess.ip 
        FROM
            rm_phpsess 
        WHERE
            rm_managers.managername = rm_phpsess.managername 
        ORDER BY
            rm_phpsess.lastact DESC LIMIT 1) AS ipip,
        temp3.disabled_users,
        (SELECT
            (SUM(rm_services.unitprice) + SUM(rm_services.unitpricetax)) 
        FROM
            `rm_users` 
        LEFT JOIN
            `rm_services` 
                ON rm_users.srvid = rm_services.srvid 
        WHERE
            rm_users.owner = rm_managers.managername 
            AND rm_users.acctype = '0' 
            AND rm_users.expiration > '$date') AS sum_users,
        rm_managers.firstname,
        rm_managers.lastname,
        rm_managers.managername,
        rm_managers.balance,
        rm_managers.comment,
        rm_managers.enablemanager,
        rm_managers.country,
        rm_managers.city,
        rm_managers.state,
        rm_managers.email,
        rm_managers.zip,
        rm_managers.address,
        rm_managers.company,
        rm_managers.phone,
        rm_managers.mobile,
        rm_managergroups.mangroupid,
        rm_managergroups.mangroupname,
        t2.lastseen,
        t2.onlinecount,
        t2.lastip 
    FROM
        `rm_managers` 
    LEFT JOIN
        `rm_managergroups` 
            ON rm_managergroups.mangroupid = rm_managers.mangroupid 
    LEFT OUTER JOIN
        (
            SELECT
                rm_phpsess.managername,
                MAX(rm_phpsess.lastact) AS lastseen,
                (COUNT(if(rm_phpsess.closed = '0',
                1,
                NULL))) AS onlinecount,
                (rm_phpsess.ip) AS lastip 
            FROM
                `rm_phpsess` 
            GROUP BY
                rm_phpsess.managername 
            ORDER BY
                rm_phpsess.lastact DESC
        ) t2 
            ON t2.managername = rm_managers.managername 
    LEFT JOIN
        temp1 
            ON temp1.owner = rm_managers.managername 
    LEFT JOIN
        temp2 
            ON temp2.owner = rm_managers.managername 
    LEFT JOIN
        temp3 
            ON temp3.owner = rm_managers.managername

The indexes:

ALTER TABLE
  `rm_managergroups`
ADD
  INDEX `rm_managergroups_index_1` (`mangroupid`, `mangroupname`);

ALTER TABLE
  `rm_managers`
ADD
  INDEX `rm_managers_index_1` (`mangroupid`, `managername`);

ALTER TABLE
  `rm_phpsess`
ADD
  INDEX `rm_phpsess_index_1` (`managername`, `lastact`, `ip`);

ALTER TABLE
  `rm_phpsess`
ADD
  INDEX `rm_phpsess_index_2` (`lastact`);

ALTER TABLE
  `rm_phpsess`
ADD
  INDEX `rm_phpsess_index_3` (`managername`, `lastact`, `closed`, `ip`);

ALTER TABLE
  `rm_services`
ADD
  INDEX `rm_services_index_1` (`srvid`, `unitprice`, `unitpricetax`);

ALTER TABLE
  `rm_users`
ADD
  INDEX `rm_users_index_1` (`acctype`, `srvid`, `owner`, `expiration`);

ALTER TABLE
  `temp1`
ADD
  INDEX `temp1_index_1` (`owner`, `expired_users`);

ALTER TABLE
  `temp2`
ADD
  INDEX `temp2_index_1` (`owner`, `active_users`);

ALTER TABLE
  `temp3`
ADD
  INDEX `temp3_index_1` (`owner`, `disabled_users`);

P.S, most recommendations were from this online sql query optimizer, and some were added manually.

  • I have noticed that query has 3 temporary tables which does not work inside php one query . – Ghassan Dec 26 '17 at 5:11
  • You have to separate the queries in PHP as well. It complicates the code a bit, but should optimize the execution duration. Did you this solution before implementing in the code? How well did it improve the execution duration? – Tom Shir Dec 26 '17 at 10:29
  • Honestly i seperated them into 3 queries , but the same execution time about ~32 seconds reaching 40 seconds . TABLE "radacct" is reaching 5 million records which force me to think twice about optimizing all mysql queries so i'm thinking to convert all Correlated Sub Queries into JOINS or LEFT JOINS , but its complicated . I will update my question and put my new mysql query to show you the comparison of getting the idea . – Ghassan Dec 26 '17 at 18:02
  • Sure, happy to try to help (though I still didn't haha). Did you make sure to add the indexes after adding the temporary table, and before running the query itself? If so, which part runs slowly? Is it the creation of the tables or the query itself? – Tom Shir Dec 26 '17 at 20:54
  • OR clause is the main reason for slowing my query :( so i was reading that OR clause can be replaced with UNION ALL – Ghassan Dec 27 '17 at 1:10

Not the answer you're looking for? Browse other questions tagged or ask your own question.