Prevent full-table-scans on wt_site_access_rules

1 files changed, 23 insertions, 1 deletions

diff --git a/includes/db_schema/db_schema_27_28.php b/includes/db_schema/db_schema_27_28.php
index f60d8e707d..a875cc5693 100644
--- a/includes/db_schema/db_schema_27_28.php
+++ b/includes/db_schema/db_schema_27_28.php
@@ -32,9 +32,31 @@ if (!defined('WT_WEBTREES')) {
 }
 
 // Delete old/unused settings
-self::exec(
+WT_DB::exec(
 	"DELETE FROM `##gedcom_setting` WHERE setting_name IN ('USE_GEONAMES')"
 );
 
+try {
+	// Indexes created by setup.php or schema update 17-18
+	WT_DB::exec("ALTER TABLE `##site_access_rule` DROP INDEX ix1, DROP INDEX ix2, DROP INDEX ix3");
+	// Indexes created by schema update 17-18
+	WT_DB::exec("ALTER TABLE `##site_access_rule` DROP INDEX ix4, DROP INDEX ix5, DROP INDEX ix6");
+} catch (Exception $ex) {
+	// Already done?
+}
+
+// User data may contains duplicates - these will prevent us from creating the new indexes
+WT_DB::exec(
+	"DELETE t1 FROM `##site_access_rule` AS t1 JOIN (SELECT MIN(site_access_rule_id) AS site_access_rule_id, ip_address_end, ip_address_start, user_agent_pattern FROM `##site_access_rule`) AS t2 ON t1.ip_address_end = t2.ip_address_end AND t1.ip_address_start = t2.ip_address_start AND t1.user_agent_pattern = t2.user_agent_pattern AND t1.site_access_rule_id <> t2.site_access_rule_id"
+);
+
+// ix1 - covering index for visitor lookup
+// ix2 - for total counts in admin page
+//try {
+	WT_DB::exec("ALTER TABLE `##site_access_rule` ADD UNIQUE INDEX `##site_access_rule_ix1` (ip_address_end, ip_address_start, user_agent_pattern, rule), ADD INDEX `##site_access_rule_ix2` (rule)");
+//} catch (Exception $ex) {
+	// Already done?
+//}
+
 // Update the version to indicate success
 WT_Site::preference($schema_name, $next_version);