Droga Listo !
Tworzę skrypt konfigurujący jednocześnie kilkanaście parametrów w każdej pętli. W związku z tym zbudowałem bardzo duze zapytanie SQL wyciągające jednocześnie wszystkie parametry. Działa skutecznie lecz wolno, czemu nie można się dziwić widząc liczbę dodatkowych pętli. Nie znam sie na SQL wystarczająco żeby zoptymalizować je. Czy może mi ktos podpowiedzieć?
LMS : 11.8
MySQL : 5.0.77
Zapytanie :
SELECT
nodes.ownerid as owner_id,
customers.lastname as owner_name ,
nodes.id as node_id,
nodes.name as node_name ,
inet_ntoa(nodes.ipaddr) as lan_ip,
(SELECT bit_count(inet_aton(networks.mask)) from networks,nodes where ((inet_aton(networks.mask) & nodes.ipaddr) = networks.address and nodes.id = node_id)) as lan_mask,
(SELECT networks.interface from networks,nodes where ((inet_aton(networks.mask) & nodes.ipaddr) = networks.address) and nodes.id = node_id) as lan_interface, inet_ntoa(nodes.ipaddr_pub) as wan_ip,
(SELECT bit_count(inet_aton(networks.mask)) from networks,nodes where ((inet_aton(networks.mask) & nodes.ipaddr_pub) = networks.address and nodes.id = node_id)) as wan_mask,
(SELECT networks.interface from networks,nodes where ((inet_aton(networks.mask) & nodes.ipaddr_pub) = networks.address) and nodes.id = node_id) as wan_interface,
nodes.mac as mac,
nodes.chkmac as check_mac,
(SELECT SUM( uprate ) from tariffs,assignments,nodeassignments where assignments.tariffid = tariffs.id and assignments.customerid = owner_id AND assignments.id != nodeassignments.assignmentid ) as owner_up_limit,
(SELECT SUM( upceil ) from tariffs,assignments,nodeassignments where assignments.tariffid = tariffs.id and assignments.customerid = owner_id AND assignments.id != nodeassignments.assignmentid ) as owner_max_up_limit,
(SELECT SUM( downrate ) from tariffs,assignments,nodeassignments where assignments.tariffid = tariffs.id and assignments.customerid = owner_id AND assignments.id != nodeassignments.assignmentid ) as owner_down_limit,
(SELECT SUM( downceil ) from tariffs,assignments,nodeassignments where assignments.tariffid = tariffs.id and assignments.customerid = owner_id AND assignments.id != nodeassignments.assignmentid ) as owner_max_down_limit,
(SELECT SUM( quota_www_limit ) from tariffs,assignments,nodeassignments where assignments.tariffid = tariffs.id and assignments.customerid = owner_id AND assignments.id != nodeassignments.assignmentid ) as owner_burst_threshold,
(SELECT SUM( quota_sql_limit ) from tariffs,assignments,nodeassignments where assignments.tariffid = tariffs.id and assignments.customerid = owner_id AND assignments.id != nodeassignments.assignmentid ) as owner_burst_limit,
(SELECT SUM( quota_ftp_limit ) from tariffs,assignments,nodeassignments where assignments.tariffid = tariffs.id and assignments.customerid = owner_id AND assignments.id != nodeassignments.assignmentid ) as owner_burst_time,
(SELECT SUM( uprate ) from tariffs,assignments,nodeassignments where assignments.tariffid = tariffs.id and nodeassignments.nodeid = node_id AND assignments.id = nodeassignments.assignmentid ) as node_up_limit,
(SELECT SUM( upceil ) from tariffs,assignments,nodeassignments where assignments.tariffid = tariffs.id and nodeassignments.nodeid = node_id AND assignments.id = nodeassignments.assignmentid ) as node_max_up_limit,
(SELECT SUM( downrate ) from tariffs,assignments,nodeassignments where assignments.tariffid = tariffs.id and nodeassignments.nodeid = node_id AND assignments.id = nodeassignments.assignmentid ) as node_down_limit,
(SELECT SUM( downceil ) from tariffs,assignments,nodeassignments where assignments.tariffid = tariffs.id and nodeassignments.nodeid = node_id AND assignments.id = nodeassignments.assignmentid ) as node_max_down_limit,
(SELECT SUM( quota_www_limit ) from tariffs,assignments,nodeassignments where assignments.tariffid = tariffs.id and nodeassignments.nodeid = node_id AND assignments.id = nodeassignments.assignmentid ) as node_burst_threshold,
(SELECT SUM( quota_sql_limit ) from tariffs,assignments,nodeassignments where assignments.tariffid = tariffs.id and nodeassignments.nodeid = node_id AND assignments.id = nodeassignments.assignmentid ) as node_burst_limit,
(SELECT SUM( quota_ftp_limit ) from tariffs,assignments,nodeassignments where assignments.tariffid = tariffs.id and nodeassignments.nodeid = node_id AND assignments.id = nodeassignments.assignmentid ) as node_burst_time
FROM nodes, customers where nodes.ownerid = customers.id and nodes.access = 1 order by owner_id
--
Szymon Kajewski
lysysoft(a)o2.pl
