Help with optimize SELECT
hi,
I need two select query to take users parameters for radius server from mysql database.
DB is billing DB and I can't change it structure :(
there is a query
auth-check
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';
+-------------+-----------+----------+--------+----+
| ip_group_id | uname | Password | upass | := |
+-------------+-----------+----------+--------+----+
| 4410 | user_test | Password | TEST | := |
+-------------+-----------+----------+--------+----+
1 row in set (0.28 sec)
and user radius attr query
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';
+-------------+------+------------+------------+
| ip | attr | is_deleted | is_blocked |
+-------------+------+------------+------------+
| 192.168.1.1 | NULL | 0 | 0 |
+-------------+------+------------+------------+
1 row in set (0.33 sec)
as u see there are too slow
is any method to optimize them and make them more faster?
there are tables desc
desc ip_groups;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| ip_group_id | int(11) | NO | MUL | NULL | |
| ip | int(11) | NO | | 0 | |
| mask | int(11) | NO | | -1 | |
| uname | varchar(64) | NO | MUL | NULL | |
| upass | varchar(64) | NO | | NULL | |
| mac | varchar(32) | NO | | | |
| allowed_cid | varchar(32) | NO | | | |
| ip_type | int(11) | NO | | 0 | |
| router_id | int(11) | NO | | 0 | |
| create_date | int(11) | NO | | 0 | |
| delete_date | int(11) | NO | | 0 | |
| is_deleted | int(11) | NO | | 0 | |
+-------------+-------------+------+-----+---------+----------------+
desc iptraffic_service_links;
+---------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| ip_group_id | int(11) | NO | | NULL | |
| downloaded_id | int(11) | NO | | NULL | |
| is_deleted | int(11) | NO | | 0 | |
+---------------+---------+------+-----+---------+----------------+
desc service_links;
+----------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| user_id | int(11) | NO | | NULL | |
| account_id | int(11) | NO | MUL | NULL | |
| service_id | int(11) | NO | | NULL | |
| tariff_link_id | int(11) | NO | | NULL | |
| is_deleted | int(11) | NO | | 0 | |
+----------------+---------+------+-----+---------+----------------+
desc accounts;
+----------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| balance | double | NO | | 0 | |
| account_name | varchar(255) | NO | | | |
| credit | double | NO | | 0 | |
| flags | int(11) | NO | | 0 | |
| is_blocked | int(11) | NO | | 0 | |
| vat_rate | double | NO | | 0 | |
| sale_tax_rate | double | NO | | 0 | |
| int_status | int(11) | NO | | 0 | |
| block_recalc_abon | int(11) | NO | | 0 | |
| block_recalc_prepaid | int(11) | NO | | 0 | |
| unlimited | int(11) | NO | | 0 | |
| is_deleted | int(11) | NO | | 0 | |
+----------------------+--------------+------+-----+---------+----------------+
desc radius_data;
+------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| owner_id | int(11) | NO | | NULL | |
| owner_type | int(11) | NO | | NULL | |
| vendor | int(11) | NO | | 0 | |
| attr | int(11) | NO | | NULL | |
| value | text | YES | | NULL | |
+------------+---------+------+-----+---------+----------------+
Subject
Written By
Posted
Help with optimize SELECT
April 10, 2009 03:44AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.