From 24ff7fcb07172f3ce72e41d774f0946eb102de00 Mon Sep 17 00:00:00 2001
From: Steve Sutton <steve@gaslightmedia.com>
Date: Fri, 14 Apr 2017 16:25:16 -0400
Subject: [PATCH] Create indexes for the custom fields tables.

Indexing field name and the ref_dest and field_id.
---
 index.php                                      |  2 +-
 ...e_V0.0.1.sql => create_database_V0.0.2.sql} |  9 ++++++---
 setup/databaseScripts/dbVersions.php           |  1 +
 .../databaseScripts/update_database_V0.0.2.sql | 18 ++++++++++++++++++
 4 files changed, 26 insertions(+), 4 deletions(-)
 rename setup/databaseScripts/{create_database_V0.0.1.sql => create_database_V0.0.2.sql} (88%)
 create mode 100644 setup/databaseScripts/update_database_V0.0.2.sql

diff --git a/index.php b/index.php
index da5b91b..9873cee 100644
--- a/index.php
+++ b/index.php
@@ -38,7 +38,7 @@
  *  version from this plugin.
  */
 define('GLM_MEMBERS_FIELDS_PLUGIN_VERSION', '0.0.1');
-define('GLM_MEMBERS_FIELDS_PLUGIN_DB_VERSION', '0.0.1');
+define('GLM_MEMBERS_FIELDS_PLUGIN_DB_VERSION', '0.0.2');
 
 // This is the minimum version of the GLM Members DB plugin require for this plugin.
 define('GLM_MEMBERS_FIELDS_PLUGIN_MIN_MEMBERS_REQUIRED_VERSION', '2.8.0');
diff --git a/setup/databaseScripts/create_database_V0.0.1.sql b/setup/databaseScripts/create_database_V0.0.2.sql
similarity index 88%
rename from setup/databaseScripts/create_database_V0.0.1.sql
rename to setup/databaseScripts/create_database_V0.0.2.sql
index 1e14758..293f7c4 100644
--- a/setup/databaseScripts/create_database_V0.0.1.sql
+++ b/setup/databaseScripts/create_database_V0.0.2.sql
@@ -1,6 +1,6 @@
 -- Gaslight Media Members Database - Fields Add-On
 -- File Created: 2017-03-27
--- Database Version: 0.0.1
+-- Database Version: 0.0.2
 -- Database Creation Script
 --
 -- This file is called to create a new set of tables for this
@@ -18,7 +18,8 @@ CREATE TABLE {prefix}custom_fields (
   field_name TINYTEXT NOT NULL DEFAULT '',      -- Field Name
   field_type TINYTEXT NOT NULL DEFAULT '',      -- Field Type
   field_order SMALLINT NOT NULL DEFAULT 0,      -- Order for Field
-  PRIMARY KEY (id)
+  PRIMARY KEY (id),
+  INDEX(field_name(20))
 );
 
 ----
@@ -29,5 +30,7 @@ CREATE TABLE {prefix}custom_field_data (
   field_id INT NOT NULL DEFAULT 0,              -- Field Id
   ref_dest INT NOT NULL DEFAULT 0,              -- Member Info Id
   field_data TEXT NOT NULL DEFAULT '',          -- Data for the field
-  PRIMARY KEY (id)
+  PRIMARY KEY (id).
+  INDEX(field_id),
+  INDEX(ref_dest)
 );
diff --git a/setup/databaseScripts/dbVersions.php b/setup/databaseScripts/dbVersions.php
index 6c5508f..29c7bd4 100644
--- a/setup/databaseScripts/dbVersions.php
+++ b/setup/databaseScripts/dbVersions.php
@@ -15,5 +15,6 @@
 
 $glmMembersFieldsDbVersions = array(
     '0.0.1' => array('version' => '0.0.1', 'tables' => 2, 'date' => '03/27/2017'),
+    '0.0.2' => array('version' => '0.0.2', 'tables' => 2, 'date' => '04/14/2017'),
 );
 
diff --git a/setup/databaseScripts/update_database_V0.0.2.sql b/setup/databaseScripts/update_database_V0.0.2.sql
new file mode 100644
index 0000000..d1cef4c
--- /dev/null
+++ b/setup/databaseScripts/update_database_V0.0.2.sql
@@ -0,0 +1,18 @@
+-- Gaslight Media Members Database
+-- File Created: 2017-04-14
+-- Database Version: 0.0.2
+-- Database Update From Previous Version Script
+--
+-- To permit each query below to be executed separately,
+-- all queries must be separated by a line with four dashses
+
+CREATE INDEX field_name ON {prefix}custom_fields(field_name(20));
+
+----
+
+CREATE INDEX field_id ON {prefix}custom_field_data(field_id);
+
+----
+
+CREATE INDEX ref_dest ON {prefix}custom_field_data(ref_dest);
+
-- 
2.17.1