From 4337cfd068e8fa85032e0166fd0f227164b4e60f Mon Sep 17 00:00:00 2001 From: Marcin Siodelski Date: Tue, 27 Feb 2018 21:06:28 +0100 Subject: [PATCH] [5437] Replaced index by iaid_subnet_id_duid with duid_iaid_subnet_id. --- src/share/database/scripts/mysql/dhcpdb_create.mysql | 7 +++++++ src/share/database/scripts/mysql/upgrade_5.1_to_6.0.sh.in | 7 +++++++ src/share/database/scripts/pgsql/dhcpdb_create.pgsql | 7 +++++++ src/share/database/scripts/pgsql/upgrade_3.2_to_4.0.sh.in | 7 +++++++ 4 files changed, 28 insertions(+) diff --git a/src/share/database/scripts/mysql/dhcpdb_create.mysql b/src/share/database/scripts/mysql/dhcpdb_create.mysql index b1cdd1d058..4732ae6524 100644 --- a/src/share/database/scripts/mysql/dhcpdb_create.mysql +++ b/src/share/database/scripts/mysql/dhcpdb_create.mysql @@ -516,6 +516,13 @@ CREATE INDEX lease4_by_subnet_id ON lease4 (subnet_id); # Create for searching leases by subnet identifier and lease type. CREATE INDEX lease6_by_subnet_id_lease_type ON lease6 (subnet_id, lease_type); +# The index by iaid_subnet_id_duid is not the best choice because there are +# cases when we don't specify subnet identifier while searching leases. The +# index will be universal if the subnet_id is the right most column in the +# index. +DROP INDEX lease6_by_iaid_subnet_id_duid on lease6; +CREATE INDEX lease6_by_duid_iaid_subnet_id ON lease6 (duid, iaid, subnet_id); + # Update the schema version number UPDATE schema_version SET version = '6', minor = '0'; diff --git a/src/share/database/scripts/mysql/upgrade_5.1_to_6.0.sh.in b/src/share/database/scripts/mysql/upgrade_5.1_to_6.0.sh.in index c4e7809128..5d48d5dc6a 100644 --- a/src/share/database/scripts/mysql/upgrade_5.1_to_6.0.sh.in +++ b/src/share/database/scripts/mysql/upgrade_5.1_to_6.0.sh.in @@ -30,6 +30,13 @@ CREATE INDEX lease4_by_subnet_id ON lease4 (subnet_id); # Create for searching leases by subnet identifier and lease type. CREATE INDEX lease6_by_subnet_id_lease_type ON lease6 (subnet_id, lease_type); +# The index by iaid_subnet_id_duid is not the best choice because there are +# cases when we don't specify subnet identifier while searching leases. The +# index will be universal if the subnet_id is the right most column in the +# index. +DROP INDEX lease6_by_iaid_subnet_id_duid on lease6; +CREATE INDEX lease6_by_duid_iaid_subnet_id ON lease6 (duid, iaid, subnet_id); + # Update the schema version number UPDATE schema_version SET version = '6', minor = '0'; diff --git a/src/share/database/scripts/pgsql/dhcpdb_create.pgsql b/src/share/database/scripts/pgsql/dhcpdb_create.pgsql index 90d1aed7da..d5041520fe 100644 --- a/src/share/database/scripts/pgsql/dhcpdb_create.pgsql +++ b/src/share/database/scripts/pgsql/dhcpdb_create.pgsql @@ -543,6 +543,13 @@ CREATE INDEX lease4_by_subnet_id ON lease4 (subnet_id); -- Create for searching leases by subnet identifier and lease type. CREATE INDEX lease6_by_subnet_id_lease_type ON lease6 (subnet_id, lease_type); +-- The index by iaid_subnet_id_duid is not the best choice because there are +-- cases when we don't specify subnet identifier while searching leases. The +-- index will be universal if the subnet_id is the right most column in the +-- index. +DROP INDEX lease6_by_iaid_subnet_id_duid; +CREATE INDEX lease6_by_duid_iaid_subnet_id ON lease6 (duid, iaid, subnet_id); + -- Set 4.0 schema version. UPDATE schema_version SET version = '4', minor = '0'; diff --git a/src/share/database/scripts/pgsql/upgrade_3.2_to_4.0.sh.in b/src/share/database/scripts/pgsql/upgrade_3.2_to_4.0.sh.in index c20e114f2a..c1a249c7c6 100644 --- a/src/share/database/scripts/pgsql/upgrade_3.2_to_4.0.sh.in +++ b/src/share/database/scripts/pgsql/upgrade_3.2_to_4.0.sh.in @@ -32,6 +32,13 @@ CREATE INDEX lease4_by_subnet_id ON lease4 (subnet_id); -- Create for searching leases by subnet identifier and lease type. CREATE INDEX lease6_by_subnet_id_lease_type ON lease6 (subnet_id, lease_type); +-- The index by iaid_subnet_id_duid is not the best choice because there are +-- cases when we don't specify subnet identifier while searching leases. The +-- index will be universal if the subnet_id is the right most column in the +-- index. +DROP INDEX lease6_by_iaid_subnet_id_duid; +CREATE INDEX lease6_by_duid_iaid_subnet_id ON lease6 (duid, iaid, subnet_id); + -- Set 4.0 schema version. UPDATE schema_version SET version = '4', minor = '0'; -- 2.47.2