MySQL Forums :: Newbie :: Help with optimize SELECT


Advanced Search

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 Alexander Kosykh 04/10/2009 03:44AM
Re: Help with optimize SELECT Rick James 04/11/2009 03:16PM
Re: Help with optimize SELECT Alexander Kosykh 04/13/2009 02:09AM
Re: Help with optimize SELECT Rick James 04/13/2009 12:09PM
Re: Help with optimize SELECT Alexander Kosykh 04/14/2009 02:33AM
Re: Help with optimize SELECT Rick James 04/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.