im stuck with a query which take more than 13 secs to return output i did create the indexes but the execution plan doesnt seem to use them. Below are some information regarding
Query
SELECT userinfo.username,userinfo.firstname,userinfo.lastname,userinfo.email,
radcheck.attribute,radcheck.value,radusergroup.groupname, userinfo.id,
userinfo.workphone,userinfo.homephone,
userinfo.mobilephone,userinfo.address, userinfo.zone,
userinfo.account_state,userinfo.device_owner,userinfo.link_type,userinfo.account_type
FROM userinfo
INNER JOIN radcheck ON userinfo.username = radcheck.username
INNER JOIN radusergroup ON userinfo.username = radusergroup.username
WHERE radcheck.attribute='Expiration' and
STR_TO_DATE(radcheck.value, '%d %M %Y') < CURDATE()
and radusergroup.groupname='Customer30M';
Explain output
+----+-------------+--------------+------+--------------------+-----------+---------+--------------------------------+------+-----------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------+------+--------------------+-----------+---------+--------------------------------+------+-----------------------------------+
| 1 | SIMPLE | radusergroup | ref | username,groupname | groupname | 66 | const | 5 | Using where with pushed condition |
| 1 | SIMPLE | radcheck | ref | username,attribute | attribute | 34 | const | 9 | Using where |
| 1 | SIMPLE | userinfo | ref | username | username | 131 | ctradius.radusergroup.username | 10 | Using where |
+----+-------------+--------------+------+--------------------+-----------+---------+--------------------------------+------+-----------------------------------+
3 rows in set (0.00 sec)
Table Info
Table: radusergroup
+-----------+-------------+------+-----+---------+----------------+
| 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 | |
+-----------+-------------+------+-----+---------+----------------+
radusergroup table Index
+--------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| radusergroup | 0 | PRIMARY | 1 | id | A | 11292 | NULL | NULL | | BTREE | | |
| radusergroup | 1 | username | 1 | username | A | NULL | NULL | NULL | | BTREE | | |
| radusergroup | 1 | groupname | 1 | groupname | A | NULL | NULL | NULL | | BTREE | | |
| radusergroup | 1 | indexradusergroup | 1 | username | A | NULL | NULL | NULL | | BTREE | | |
| radusergroup | 1 | indexradusergroup | 2 | groupname | A | NULL | NULL | NULL | | BTREE | | |
Table : userinfo
---------------------+------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+------------------+------+-----+---------------------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(128) | YES | MUL | NULL | |
| firstname | varchar(200) | YES | | NULL | |
| lastname | varchar(200) | YES | | NULL | |
| email | varchar(200) | YES | | NULL | |
| workphone | varchar(200) | YES | | NULL | |
| homephone | varchar(200) | YES | | NULL | |
| mobilephone | varchar(200) | YES | | NULL | |
| address | varchar(200) | YES | | NULL | |
| zone | varchar(20) | YES | | NULL | |
| account_state | varchar(10) | YES | | active | |
| change_commit | tinyint(1) | YES | | NULL | |
| link_type | varchar(8) | YES | | NULL | |
| account_type | varchar(255) | YES | | NULL | |
| device_owner | varchar(255) | YES | | NULL | |
| account_name | varchar(255) | YES | | NULL | |
| account_email | varchar(255) | YES | | NULL | |
| account_mobile | varchar(255) | YES | | NULL | |
| groupname | varchar(255) | YES | | NULL | |
+---------------------+------------------+------+-----+---------------------+----------------+
Indexes on userinfo
----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| userinfo | 0 | PRIMARY | 1 | id | A | 11282 | NULL | NULL | | BTREE | | |
| userinfo | 1 | username | 1 | username | A | NULL | NULL | NULL | YES | BTREE | | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
Table: Radcheck
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(64) | NO | MUL | | |
| attribute | varchar(32) | NO | MUL | | |
| op | char(2) | NO | | == | |
| value | varchar(253) | NO | MUL | | |
+-----------+------------------+------+-----+---------+----------------+
indexes on radcheck
+----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| radcheck | 0 | PRIMARY | 1 | id | A | 33425 | NULL | NULL | | BTREE | | |
| radcheck | 1 | username | 1 | username | A | NULL | NULL | NULL | | BTREE | | |
| radcheck | 1 | value | 1 | value | A | NULL | NULL | NULL | | BTREE | | |
| radcheck | 1 | attribute | 1 | attribute | A | NULL | NULL | NULL | | BTREE | | |
