I've reworked the previously mentioned query, and improved a bit.
comment out the following 2 lines in include.php, they should be 181 and 182, make sure you take a copy of it first.
$sql = "SELECT system_uuid, system_timestamp, system_name, system.net_ip_address, net_domain FROM system, network_card WHERE system_uuid = '$pc' OR system_name = '$pc' OR (net_mac_address = '$pc' AND net_uuid = system_uuid) LIMIT 1"; $result = mysql_query($sql, $db);
and replace them with the following.
$sql = "SELECT system_uuid, system_timestamp, system_name, system.net_ip_address, net_domain FROM system, network_card WHERE system_uuid = '$pc' OR system_name = '$pc' LIMIT 1"; $result = mysql_query($sql, $db); if (mysql_num_rows($result) >0 ){ $myrow = mysql_fetch_array($result);} Else { $sql = "SELECT system_uuid, system_timestamp, system_name, system.net_ip_address, net_domain FROM system left join network_card on net_uuid = system_uuid WHERE net_mac_address = '$pc' LIMIT 1"; $result = mysql_query($sql, $db); $myrow = mysql_fetch_array($result);} This replaces the single query, which doesn't use indexes, with 2 seperate ones, both of which should use indexes, and only calls the second one if the first returns no results.
I've also looked at some of our commonly used queries, and added some indexes which make them faster. The most noticable of this is on the home page. The down side it is may make updates slower, as mysql has to add the keys at insert/update time.
you can cut and paste the following into mysql query browser.
use openaudit; alter table system add index (system_name); alter table system add index net_ip_address(net_ip_address); alter table system add index system_first_timestamp(system_first_timestamp);
alter table network_card add index (net_ip_address); alter table network_card add index net_uuid(net_uuid);
alter table system_audits add index system_audits_uuid(system_audits_uuid); alter table software add index software_name(software_name); alter table software add key software_first_timestamp(software_first_timestamp);
alter table software_register add index software_title(software_title)
Perhaps the code owner could look to including these changes in the source, if he is happy with them.
Server Info: Windows XP running XAMPP
Auditing: 4400 machines, mainly windows XP W2K3: Active Directory
|