Below the query that takes almost 7's and Im confused if its normal behaviour in mysqlserver(ndb storage engine), in explain out put it also shows it is using table indexes
SELECT radgroupreply.groupname,
count(distinct(radusergroup.username)) AS users
FROM radgroupreply
JOIN radusergroup ON radgroupreply.groupname=radusergroup.groupname
WHERE
(radgroupreply.groupname NOT LIKE 'FB-%'
AND radgroupreply.groupname <> 'Dropped Corporate Users'
AND radgroupreply.groupname <> 'Dropped Broadband Users')
GROUP BY radgroupreply.groupname
UNION
SELECT distinct(radgroupcheck.groupname),
count(distinct(radusergroup.username))
FROM radgroupcheck
JOIN radusergroup ON radgroupcheck.groupname=radusergroup.groupname
WHERE
(radgroupcheck.groupname NOT LIKE 'FB-%'
AND radgroupcheck.groupname <> 'Dropped Corporate Users'
)
GROUP BY radgroupcheck.groupname ORDER BY groupname asc;
The explain output for the query is :-
+----+--------------+---------------+-------+---------------+-----------+---------+----------------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+---------------+-------+---------------+-----------+---------+----------------------------------+------+---------------------------------+
| 1 | PRIMARY | radgroupreply | range | groupname | groupname | 66 | NULL | 47 | Using where; Using MRR |
| 1 | PRIMARY | radusergroup | ref | groupname | groupname | 66 | ctradius.radgroupreply.groupname | 64 | NULL |
| 2 | UNION | radgroupcheck | range | groupname | groupname | 66 | NULL | 20 | Using where; Using MRR |
| 2 | UNION | radusergroup | ref | groupname | groupname | 66 | ctradius.radgroupcheck.groupname | 120 | NULL |
|NULL| UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary; Using filesort |
+----+--------------+---------------+-------+---------------+-----------+---------+----------------------------------+------+---------------------------------+
Below is the table structure and indexed column information for tables involved in join
Table: radgroupreply; # total 192 rows
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| groupname | varchar(64) | NO | MUL | | |
| attribute | varchar(32) | NO | | | |
| op | char(2) | NO | | = | |
| value | varchar(253) | NO | | | |
+-----------+------------------+------+-----+---------+----------------+
Table: radusergroup #total: ~13000 rows
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(64) | NO | MUL | | |
| groupname | varchar(64) | NO | MUL | | |
| priority | int(11) | NO | | 1 | |
+-----------+-------------+------+-----+---------+----------------+
Table: radgroupcheck #totalrows: ~ 100
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| groupname | varchar(64) | NO | MUL | | |
| attribute | varchar(32) | NO | | | |
| op | char(2) | NO | | == | |
| value | varchar(253) | NO | | | |
+-----------+------------------+------+-----+---------+----------------+
#radusergroup# CREATE TABLE `radusergroup` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(64) NOT NULL DEFAULT '',
`groupname` varchar(64) NOT NULL DEFAULT '',
`priority` int(11) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
KEY `groupname` (`groupname`),
KEY `username` (`username`)
) ENGINE=ndbcluster AUTO_INCREMENT=12380 DEFAULT CHARSET=latin1
#show index from radusergroup
+---------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| radgroupreply | 0 | PRIMARY | 1 | id | A | 192 | NULL | NULL | | BTREE | | |
| radgroupreply | 1 | groupname | 1 | groupname | A | NULL | NULL | NULL | | BTREE | | |
+---------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
#radgroupreply# CREATE TABLE `radgroupreply` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`groupname` varchar(64) NOT NULL DEFAULT '',
`attribute` varchar(32) NOT NULL DEFAULT '',
`op` char(2) NOT NULL DEFAULT '=',
`value` varchar(253) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `groupname` (`groupname`)
) ENGINE=ndbcluster AUTO_INCREMENT=2410 DEFAULT CHARSET=latin1
mysql> show index from radgroupreply;
+---------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| radgroupreply | 0 | PRIMARY | 1 | id | A | 192 | NULL | NULL | | BTREE | | |
| radgroupreply | 1 | groupname | 1 | groupname | A | NULL | NULL | NULL | | BTREE | | |
+---------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
#radgroupcheck# CREATE TABLE `radgroupcheck` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`groupname` varchar(64) NOT NULL DEFAULT '',
`attribute` varchar(32) NOT NULL DEFAULT '',
`op` char(2) NOT NULL DEFAULT '==',
`value` varchar(253) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `groupname` (`groupname`)
) ENGINE=ndbcluster AUTO_INCREMENT=588 DEFAULT CHARSET=latin1
mysql> show index from radgroupcheck;
+---------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| radgroupcheck | 0 | PRIMARY | 1 | id | A | 103 | NULL | NULL | | BTREE | | |
| radgroupcheck | 1 | groupname | 1 | groupname | A | NULL | NULL | NULL | | BTREE | | |
+---------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql> SELECT @@GLOBAL.tx_isolation, @@tx_isolation, @@session.tx_isolation;
+-----------------------+----------------+------------------------+
| @@GLOBAL.tx_isolation | @@tx_isolation | @@session.tx_isolation |
+-----------------------+----------------+------------------------+
| READ-COMMITTED | READ-COMMITTED | READ-COMMITTED |
+-----------------------+----------------+------------------------+
(SELECT radgroupreply.groupname,
count(distinct(radusergroup.username)) AS users
FROM radgroupreply
JOIN radusergroup ON radgroupreply.groupname=radusergroup.groupname
WHERE
(radgroupreply.groupname NOT LIKE 'FB-%' AND radgroupreply.groupname <> 'Dropped Corporate Users' AND radgroupreply.groupname <> 'Dropped Broadband Users')
GROUP BY radgroupreply.groupname )
UNION
(SELECT radgroupcheck.groupname,
count(distinct(radusergroup.username))
FROM radgroupcheck
JOIN radusergroup ON radgroupcheck.groupname=radusergroup.groupname
WHERE
(radgroupcheck.groupname NOT LIKE 'FB-%' AND radgroupcheck.groupname <> 'Dropped Corporate Users')
GROUP BY radgroupcheck.groupname ORDER BY groupname asc);
Explain:-
+----+--------------+---------------+-------+---------------+-----------+---------+----------------------------------+------+------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+---------------+-------+---------------+-----------+---------+----------------------------------+------+------------------------+
| 1 | PRIMARY | radgroupreply | range | groupname | groupname | 66 | NULL | 47 | Using where; Using MRR |
| 1 | PRIMARY | radusergroup | ref | groupname | groupname | 66 | ctradius.radgroupreply.groupname | 64 | NULL |
| 2 | UNION | radgroupcheck | range | groupname | groupname | 66 | NULL | 20 | Using where; Using MRR |
| 2 | UNION | radusergroup | ref | groupname | groupname | 66 | ctradius.radgroupcheck.groupname | 121 | NULL |
|NULL| UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+---------------+-------+---------------+-----------+---------+----------------------------------+------+------------------------+
Since you are already GROUPing BY that column change SELECT distinct(radgroupcheck.groupname) to SELECT radgroupcheck.groupname
Add parentheses to clarify that the UNION is being sorted, not merely the last SELECT:
( SELECT ... ) UNION ( SELECT ... ) ORDER BY ...
Change the many:many table according to the hints here (except for the Engine).
group_reply and group_check seem to have identical schemas; some reason for them to be separate?
Is there a unique column (or pair of columns) in group_reply and group_check that could be used for the PRIMARY KEY?
Revamp
SELECT groupname,
( SELECT count(distinct username)
FROM radusergroup WHERE groupname = u.groupname
) AS users
FROM (
(
SELECT r.groupname
FROM radgroupreply AS r
WHERE r.groupname NOT LIKE 'FB-%'
AND r.groupname <> 'Dropped Corporate Users'
AND r.groupname <> 'Dropped Broadband Users'
)
UNION DISTINCT -- or UNION ALL ?
(
SELECT c.groupname
FROM radgroupcheck AS c
WHERE c.groupname NOT LIKE 'FB-%'
AND c.groupname <> 'Dropped Corporate Users'
)
) AS u
ORDER BY groupname asc