From f3ea64c7fba51017e56f7879b690f147f66cc5ae Mon Sep 17 00:00:00 2001 From: Steve Sutton Date: Thu, 15 Mar 2018 12:08:45 -0400 Subject: [PATCH] Create indexes on tables. Creating indexes on the tables. --- index.php | 2 +- ...0.0.11.sql => create_database_V0.0.12.sql} | 24 ++++-- setup/databaseScripts/dbVersions.php | 1 + .../update_database_V0.0.12.sql | 74 +++++++++++++++++++ 4 files changed, 95 insertions(+), 6 deletions(-) rename setup/databaseScripts/{create_database_V0.0.11.sql => create_database_V0.0.12.sql} (96%) create mode 100644 setup/databaseScripts/update_database_V0.0.12.sql diff --git a/index.php b/index.php index 6d7b784..74c88ed 100644 --- a/index.php +++ b/index.php @@ -38,7 +38,7 @@ * version from this plugin. */ define('GLM_MEMBERS_BILLING_PLUGIN_VERSION', '0.0.1'); -define('GLM_MEMBERS_BILLING_PLUGIN_DB_VERSION', '0.0.11'); +define('GLM_MEMBERS_BILLING_PLUGIN_DB_VERSION', '0.0.12'); // This is the minimum version of the GLM Members DB plugin require for this plugin. define('GLM_MEMBERS_BILLING_PLUGIN_MIN_MEMBERS_REQUIRED_VERSION', '2.8.0'); diff --git a/setup/databaseScripts/create_database_V0.0.11.sql b/setup/databaseScripts/create_database_V0.0.12.sql similarity index 96% rename from setup/databaseScripts/create_database_V0.0.11.sql rename to setup/databaseScripts/create_database_V0.0.12.sql index e4ca9cf..ed69e1f 100644 --- a/setup/databaseScripts/create_database_V0.0.11.sql +++ b/setup/databaseScripts/create_database_V0.0.12.sql @@ -26,7 +26,10 @@ CREATE TABLE {prefix}accounts ( renewal_date DATE NULL, -- renewal date of account payment_data TEXT NULL, -- stored payment data email TINYTEXT NULL, -- billing email - PRIMARY KEY (id) + PRIMARY KEY (id), + INDEX(ref_dest) + INDEX(ref_name(20)), + INDEX(email(20)) ); ---- @@ -40,7 +43,10 @@ CREATE TABLE {prefix}transactions ( transaction_time DATETIME NOT NULL, -- datetime for the transaction current_invoice_total DECIMAL(8, 2) NOT NULL, -- invoice total current_payment_total DECIMAL(8, 2) NOT NULL, -- payment total - PRIMARY KEY (id) + PRIMARY KEY (id), + INDEX(account), + INDEX(type_id), + INDEX(transaction_time) ); ---- @@ -57,7 +63,10 @@ CREATE TABLE {prefix}invoices ( notes TINYTEXT, -- notes for this invoice recurring BOOLEAN DEFAULT '0', -- true/false if recurring recurrence INT NULL DEFAULT 0, -- recurrence type - PRIMARY KEY (id) + PRIMARY KEY (id), + INDEX(account), + INDEX(transaction_time), + INDEX(due_date) ); ---- @@ -89,7 +98,11 @@ CREATE TABLE {prefix}line_items ( created DATE NULL, -- Date this line item was first created first_due_date DATE NULL, -- The first due date for this item next_due_date DATE NULL, -- Next Due Date for this item - PRIMARY KEY (id) + PRIMARY KEY (id), + INDEX(account), + INDEX(created), + INDEX(first_due_date), + INDEX(next_due_date) ); ---- @@ -179,7 +192,8 @@ CREATE TABLE {prefix}invoice_payments ( invoice INT NOT NULL, -- reference to invoice id payment INT NOT NULL, -- reference to payment id amount DECIMAL(8, 2) NOT NULL, -- payment amount - PRIMARY KEY (id) + PRIMARY KEY (id), + INDEX(invoice) ); ---- diff --git a/setup/databaseScripts/dbVersions.php b/setup/databaseScripts/dbVersions.php index 9db6ea9..41b4d67 100644 --- a/setup/databaseScripts/dbVersions.php +++ b/setup/databaseScripts/dbVersions.php @@ -25,5 +25,6 @@ $glmMembersBillingDbVersions = array( '0.0.9' => array('version' => '0.0.9', 'tables' => 14), '0.0.10' => array('version' => '0.0.10', 'tables' => 14), '0.0.11' => array('version' => '0.0.11', 'tables' => 14), + '0.0.12' => array('version' => '0.0.12', 'tables' => 14), ); diff --git a/setup/databaseScripts/update_database_V0.0.12.sql b/setup/databaseScripts/update_database_V0.0.12.sql new file mode 100644 index 0000000..b5ffa54 --- /dev/null +++ b/setup/databaseScripts/update_database_V0.0.12.sql @@ -0,0 +1,74 @@ +-- Gaslight Media Billing Database +-- File Created: 03/15/2018 +-- Database Version: 0.0.12 +-- +-- To permit each query below to be executed separately, +-- all queries must be separated by a line with four dashes + +-- Add Index for ref_dest +CREATE INDEX account_ref_dest ON {prefix}accounts (ref_dest); + +---- + +-- Add Index for ref_name +CREATE INDEX account_ref_name ON {prefix}accounts (ref_name(20)); + +---- + +-- Add Index for ref_email +CREATE INDEX account_email ON {prefix}accounts (email(20)); + +---- + +-- Add Index for account +CREATE INDEX transaction_type ON {prefix}transactions (account); + +---- + +-- Add Index for type_id +CREATE INDEX transaction_type_id ON {prefix}transactions (type_id); + +---- + +-- Add Index for transaction_time +CREATE INDEX transaction_time ON {prefix}transactions (transaction_time); + +---- + +-- Add Index for account +CREATE INDEX invoices_account ON {prefix}invoices (account); + +---- + +-- Add Index for transaction_time +CREATE INDEX invoices_time ON {prefix}invoices (transaction_time); + +---- + +-- Add Index for due_date +CREATE INDEX invoices_due_date ON {prefix}invoices (due_date); + +---- + +-- Add Index for account +CREATE INDEX line_items_account ON {prefix}line_items (account); + +---- + +-- Add Index for created +CREATE INDEX line_items_created ON {prefix}line_items (created); + +---- + +-- Add Index for first_due_date +CREATE INDEX line_items_first_due_date ON {prefix}line_items (first_due_date); + +---- + +-- Add Index for next_due_date +CREATE INDEX line_items_next_due_date ON {prefix}line_items (next_due_date); + +---- + +-- Add Index for invoice +CREATE INDEX invoice_payments_invoice ON {prefix}invoice_payments (invoice); -- 2.17.1