MySQL Forums
Forum List  »  Newbie

Help with optimize SELECT
Posted by: Alexander Kosykh
Date: April 10, 2009 03:44AM

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 | |
+------------+---------+------+-----+---------+----------------+

Options: ReplyQuote


Subject
Written By
Posted
Help with optimize SELECT
April 10, 2009 03:44AM
April 11, 2009 03:16PM
April 13, 2009 12:09PM
April 14, 2009 06:43PM


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.