Thank you James
It help to make my query more faster!
explain SELECT ip_groups.ip_group_id, ip_groups.uname, 'Password', ip_groups.upass, ':='
-> FROM ip_groups
-> LEFT JOIN iptraffic_service_links ON(ip_groups.ip_group_id=iptraffic_service_links.ip_group_id)
-> LEFT JOIN service_links ON (iptraffic_service_links.id=service_links.id)
-> LEFT JOIN accounts ON (accounts.id=service_links.account_id)
-> WHERE ip_groups.uname='user_test'
-> AND ip_groups.is_deleted='0'
-> AND iptraffic_service_links.is_deleted='0'
-> AND service_links.is_deleted='0'
-> AND accounts.is_deleted='0';
+----+-------------+-------------------------+--------+------------------------------------------------+----------------------------------------+---------+---------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------------+--------+------------------------------------------------+----------------------------------------+---------+---------------------------------+------+-------------+
| 1 | SIMPLE | ip_groups | ref | index_264d0ebccce540730e5da3d221dfa3d9,uname | uname | 194 | const | 1 | Using where |
| 1 | SIMPLE | iptraffic_service_links | ref | PRIMARY,index_55232d39c6b30c255118489783ed36e2 | index_55232d39c6b30c255118489783ed36e2 | 4 | UTM5.ip_groups.ip_group_id | 1 | Using where |
| 1 | SIMPLE | service_links | eq_ref | PRIMARY,index_4ac9ffe9ebe0322b6fb7d34f3e346a6b | PRIMARY | 4 | UTM5.iptraffic_service_links.id | 1 | Using where |
| 1 | SIMPLE | accounts | eq_ref | PRIMARY,index_33a254f7035d715e438372133be258b5 | PRIMARY | 4 | UTM5.service_links.account_id | 1 | Using where |
+----+-------------+-------------------------+--------+------------------------------------------------+----------------------------------------+---------+---------------------------------+------+-------------+
4 rows in set (0.00 sec)
explain SELECT inet_ntoa(ip_groups.ip & 0xFFFFFFFF) AS 'ip', unhex(radius_data.value) AS 'attr', accounts.is_deleted, accounts.is_blocked
-> FROM ip_groups
-> LEFT JOIN iptraffic_service_links ON (ip_groups.ip_group_id=iptraffic_service_links.ip_group_id)
-> LEFT JOIN service_links ON (iptraffic_service_links.id=service_links.id)
-> LEFT JOIN radius_data ON (service_links.service_id=radius_data.owner_id)
-> LEFT JOIN accounts ON (service_links.account_id=accounts.id)
-> WHERE ip_groups.uname='user_test'
-> AND ip_groups.is_deleted='0'
-> AND iptraffic_service_links.is_deleted='0'
-> AND service_links.is_deleted='0'
-> AND accounts.is_deleted='0';
+----+-------------+-------------------------+--------+------------------------------------------------+----------------------------------------+---------+---------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------------+--------+------------------------------------------------+----------------------------------------+---------+---------------------------------+------+-------------+
| 1 | SIMPLE | ip_groups | ref | index_264d0ebccce540730e5da3d221dfa3d9,uname | uname | 194 | const | 1 | Using where |
| 1 | SIMPLE | iptraffic_service_links | ref | PRIMARY,index_55232d39c6b30c255118489783ed36e2 | index_55232d39c6b30c255118489783ed36e2 | 4 | UTM5.ip_groups.ip_group_id | 1 | Using where |
| 1 | SIMPLE | service_links | eq_ref | PRIMARY,index_4ac9ffe9ebe0322b6fb7d34f3e346a6b | PRIMARY | 4 | UTM5.iptraffic_service_links.id | 1 | Using where |
| 1 | SIMPLE | radius_data | ALL | NULL | NULL | NULL | NULL | 27 | |
| 1 | SIMPLE | accounts | eq_ref | PRIMARY,index_33a254f7035d715e438372133be258b5 | PRIMARY | 4 | UTM5.service_links.account_id | 1 | Using where |
+----+-------------+-------------------------+--------+------------------------------------------------+----------------------------------------+---------+---------------------------------+------+-------------+
5 rows in set (0.01 sec)
and one more
I've added index to radius_data
KEY `owner_id` (`owner_id`,`id`)
and now second query look like this
explain SELECT inet_ntoa(ip_groups.ip & 0xFFFFFFFF) AS 'ip', unhex(radius_data.value) AS 'attr', accounts.is_deleted, accounts.is_blocked
-> FROM ip_groups
-> LEFT JOIN iptraffic_service_links ON (ip_groups.ip_group_id=iptraffic_service_links.ip_group_id)
-> LEFT JOIN service_links ON (iptraffic_service_links.id=service_links.id)
-> LEFT JOIN radius_data ON (service_links.service_id=radius_data.owner_id)
-> LEFT JOIN accounts ON (service_links.account_id=accounts.id)
-> WHERE ip_groups.uname='user_test'
-> AND ip_groups.is_deleted='0'
-> AND iptraffic_service_links.is_deleted='0'
-> AND service_links.is_deleted='0'
-> AND accounts.is_deleted='0';
+----+-------------+-------------------------+--------+------------------------------------------------+----------------------------------------+---------+---------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------------+--------+------------------------------------------------+----------------------------------------+---------+---------------------------------+------+-------------+
| 1 | SIMPLE | ip_groups | ref | index_264d0ebccce540730e5da3d221dfa3d9,uname | uname | 194 | const | 1 | Using where |
| 1 | SIMPLE | iptraffic_service_links | ref | PRIMARY,index_55232d39c6b30c255118489783ed36e2 | index_55232d39c6b30c255118489783ed36e2 | 4 | UTM5.ip_groups.ip_group_id | 1 | Using where |
| 1 | SIMPLE | service_links | eq_ref | PRIMARY,index_4ac9ffe9ebe0322b6fb7d34f3e346a6b | PRIMARY | 4 | UTM5.iptraffic_service_links.id | 1 | Using where |
| 1 | SIMPLE | radius_data | ref | owner_id | owner_id | 4 | UTM5.service_links.service_id | 1 | |
| 1 | SIMPLE | accounts | eq_ref | PRIMARY,index_33a254f7035d715e438372133be258b5 | PRIMARY | 4 | UTM5.service_links.account_id | 1 | Using where |
+----+-------------+-------------------------+--------+------------------------------------------------+----------------------------------------+---------+---------------------------------+------+-------------+
5 rows in set (0.00 sec)
I think it's more optimal. Am I right?