From 8eb8be98377f365103bdb18cb930dab4b698ebb0 Mon Sep 17 00:00:00 2001 From: Roland Haeder Date: Mon, 12 Aug 2013 18:20:27 +0000 Subject: [PATCH] Added index + optimized query --- libs/lib_connect.php | 9 ++++++--- libs/lib_updates.php | 7 ++++++- 2 files changed, 12 insertions(+), 4 deletions(-) diff --git a/libs/lib_connect.php b/libs/lib_connect.php index c0476d4..0af17e8 100644 --- a/libs/lib_connect.php +++ b/libs/lib_connect.php @@ -312,10 +312,13 @@ function isCrackerTrackerIpSuspicious () { } // END - if // We only need the very last attempt to get! - $result = runCrackerTrackerSql("SELECT * FROM `ctracker_data` WHERE `remote_addr`='" . determineCrackerTrackerRealRemoteAddress() . "' OR `proxy_addr`='" . getenv('REMOTE_ADDR') . "' ORDER BY `last_attempt` DESC LIMIT 1", __FUNCTION__, __LINE__); + $result = runCrackerTrackerSql("SELECT SQL_SMALL_RESULT COUNT(`id`) AS `cnt` FROM `ctracker_data` USE INDEX (`remote_proxy_last`) WHERE `remote_addr`='" . determineCrackerTrackerRealRemoteAddress() . "' OR `proxy_addr`='" . getenv('REMOTE_ADDR') . "' ORDER BY `last_attempt` DESC LIMIT 1", __FUNCTION__, __LINE__); - // Do we have entries? - $found = (mysql_num_rows($result) == 1); + // Get row count + list($rows) = mysql_fetch_row($result); + + // Is there one entry? + $found = ($rows > 0); // And again? if ($found === TRUE) { diff --git a/libs/lib_updates.php b/libs/lib_updates.php index e1cb80b..436d676 100644 --- a/libs/lib_updates.php +++ b/libs/lib_updates.php @@ -43,7 +43,7 @@ UNIQUE KEY (`ctracker_ticket_remote_addr`) `ctracker_ticket_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, `ctracker_data_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY ( `ctracker_ticket_data_id` ), -UNIQUE KEY `ctracker_ticket_data` ( `ctracker_ticket_id`,`ctracker_data_id` ), +UNIQUE KEY `ctracker_ticket_data` ( `ctracker_ticket_id`, `ctracker_data_id` ), INDEX ( `ctracker_data_id` ), FOREIGN KEY ( `ctracker_ticket_id` ) REFERENCES `' . $GLOBALS['ctracker_dbname'] . '`.`ctracker_ticket` ( `ctracker_ticket` @@ -67,6 +67,11 @@ FOREIGN KEY ( `ctracker_data_id` ) REFERENCES `' . $GLOBALS['ctracker_dbname'] . 'ALTER TABLE `ctracker_data` ADD `proxy_addr` VARCHAR(15) NULL DEFAULT NULL', 'ALTER TABLE `ctracker_ticket` ADD `ctracker_ticket_proxy_addr` VARCHAR(15) NULL DEFAULT NULL', ), + + // Better key for always repeating query + 3 => array( + 'ALTER TABLE `ctracker`.`ctracker_data` ADD INDEX `remote_proxy_last` ( `remote_addr`, `proxy_addr`, `last_attempt` DESC )', + ), ); } -- 2.39.2