From a8fa25d18dca010e44ca71a4d7c7324249a0cba4 Mon Sep 17 00:00:00 2001
From: moodler <moodler>
Date: Mon, 28 Jul 2003 02:51:56 +0000
Subject: [PATCH] Improved indexing for improved performance on the course
 page, when viewing logs and when viewing lists of users ... thanks to Eloy
 for the one on the log file.

---
 lib/db/mysql.php     | 7 +++++++
 lib/db/mysql.sql     | 7 +++++--
 lib/db/postgres7.php | 7 +++++++
 lib/db/postgres7.sql | 8 ++++++--
 version.php          | 2 +-
 5 files changed, 26 insertions(+), 5 deletions(-)

diff --git a/lib/db/mysql.php b/lib/db/mysql.php
index 8375a66b9d..7ad20d9af2 100644
--- a/lib/db/mysql.php
+++ b/lib/db/mysql.php
@@ -405,6 +405,13 @@ function main_upgrade($oldversion=0) {
         table_column("course_sections", "sequence", "sequence", "text", "", "", "", "", "");
     }
 
+    if ($oldversion < 2003072800) {
+        print_simple_box("The following database index improves performance, but can be quite large - if you are upgrading and you have problems with a limited quota you may want to delete this index later from the '{$CFG->prefix}log' table in your database", "center", "50%", "$THEME->cellheading", "20", "noticebox");
+        execute_sql(" ALTER TABLE `{$CFG->prefix}log` ADD INDEX timecoursemoduleaction (time,course,module,action) ");
+        execute_sql(" ALTER TABLE `{$CFG->prefix}user_students` ADD INDEX courseuserid (course,userid) ");
+        execute_sql(" ALTER TABLE `{$CFG->prefix}user_teachers` ADD INDEX courseuserid (course,userid) ");
+    }
+
     return $result;
 
 }
diff --git a/lib/db/mysql.sql b/lib/db/mysql.sql
index f3b0c9b91b..ece942f6f2 100644
--- a/lib/db/mysql.sql
+++ b/lib/db/mysql.sql
@@ -131,6 +131,7 @@ CREATE TABLE `prefix_log` (
   `url` varchar(100) NOT NULL default '',
   `info` varchar(255) NOT NULL default '',
   PRIMARY KEY  (`id`),
+  KEY `timecoursemoduleaction` (time,course,module,action),
   KEY `coursemoduleaction` (course,module,action),
   KEY `courseuserid` (course,userid)
 ) TYPE=MyISAM COMMENT='Every action is logged as far as possible.';
@@ -233,7 +234,8 @@ CREATE TABLE `prefix_user_students` (
   `timeend` int(10) unsigned NOT NULL default '0',
   `time` int(10) unsigned NOT NULL default '0',
   PRIMARY KEY  (`id`),
-  UNIQUE KEY `id` (`id`)
+  UNIQUE KEY `id` (`id`),
+  KEY `courseuserid` (course,userid)
 ) TYPE=MyISAM;
 # --------------------------------------------------------
 
@@ -248,7 +250,8 @@ CREATE TABLE `prefix_user_teachers` (
   `authority` int(10) NOT NULL default '3',
   `role` varchar(40) NOT NULL default '',
   PRIMARY KEY  (`id`),
-  UNIQUE KEY `id` (`id`)
+  UNIQUE KEY `id` (`id`),
+  KEY `courseuserid` (course,userid)
 ) TYPE=MyISAM COMMENT='One record per teacher per course';
 
 #
diff --git a/lib/db/postgres7.php b/lib/db/postgres7.php
index 573f26a6c1..9d7afae016 100644
--- a/lib/db/postgres7.php
+++ b/lib/db/postgres7.php
@@ -176,6 +176,13 @@ function main_upgrade($oldversion=0) {
         table_column("course_sections", "sequence", "sequence", "text", "", "", "", "", "");
     }
 
+    if ($oldversion < 2003072800) {
+        print_simple_box("The following database index improves performance, but can be quite large - if you are upgrading and you have problems with a limited quota you may want to delete this index later from the '{$CFG->prefix}log' table in your database", "center", "50%", "$THEME->cellheading", "20", "noticebox");
+        execute_sql(" CREATE INDEX {$CFG->prefix}log_timecoursemoduleaction_idx ON {$CFG->prefix}log (time,course,module,action) ");
+        execute_sql(" CREATE INDEX {$CFG->prefix}user_students_courseuserid_idx ON {$CFG->prefix}user_students (course,userid) ");
+        execute_sql(" CREATE INDEX {$CFG->prefix}user_teachers_courseuserid_idx ON {$CFG->prefix}user_teachers (course,userid) ");
+    }
+
     return $result;
 }
 ?>    
diff --git a/lib/db/postgres7.sql b/lib/db/postgres7.sql
index 2cdc2c914b..4f689cd567 100644
--- a/lib/db/postgres7.sql
+++ b/lib/db/postgres7.sql
@@ -76,7 +76,7 @@ CREATE TABLE prefix_log (
 );
 
 CREATE INDEX prefix_log_coursemoduleaction_idx ON prefix_log (course,module,action);
-
+CREATE INDEX prefix_log_timecoursemoduleaction_idx ON prefix_log (time,course,module,action);
 CREATE INDEX prefix_log_courseuserid_idx ON prefix_log (course,userid);
 
 CREATE TABLE prefix_log_display (
@@ -147,6 +147,8 @@ CREATE TABLE prefix_user_students (
    time integer NOT NULL default '0'
 );
 
+CREATE INDEX prefix_user_students_courseuserid_idx ON prefix_user_students (course,userid);
+
 CREATE TABLE prefix_user_teachers (
    id SERIAL PRIMARY KEY,
    userid integer NOT NULL default '0',
@@ -155,7 +157,9 @@ CREATE TABLE prefix_user_teachers (
    role varchar(40) NOT NULL default ''
 );
 
-CREATE TABLE mdl_user_coursecreators (
+CREATE INDEX prefix_user_teachers_courseuserid_idx ON prefix_user_teachers (course,userid);
+
+CREATE TABLE prefix_user_coursecreators (
    id SERIAL8 PRIMARY KEY,
    userid int8  NOT NULL default '0'
 );
diff --git a/version.php b/version.php
index 30a3099e8e..abe5209d52 100644
--- a/version.php
+++ b/version.php
@@ -5,7 +5,7 @@
 // database to determine whether upgrades should
 // be performed (see lib/db/*.php)
 
-$version = 2003072101;   // The current version is a date (YYYYMMDDXX)
+$version = 2003072800;   // The current version is a date (YYYYMMDDXX)
 
 $release = "1.1 development";  // User-friendly version number
 
-- 
2.39.5