From 0d76b58c818306b35261dd21fd998b9f5fb0a903 Mon Sep 17 00:00:00 2001 From: Nik Okuntseff Date: Sun, 29 Jul 2018 16:53:01 +0000 Subject: [PATCH] Added indexes on group_id to speed up searches by group_id. --- WEB-INF/templates/footer.tpl | 2 +- dbinstall.php | 11 +++++++---- mysql.sql | 4 +++- 3 files changed, 11 insertions(+), 6 deletions(-) diff --git a/WEB-INF/templates/footer.tpl b/WEB-INF/templates/footer.tpl index d36605b6..88eb10ac 100644 --- a/WEB-INF/templates/footer.tpl +++ b/WEB-INF/templates/footer.tpl @@ -12,7 +12,7 @@
- - - + +
 Anuko Time Tracker 1.17.96.4300 | Copyright © Anuko | +  Anuko Time Tracker 1.17.97.4301 | Copyright © Anuko | {$i18n.footer.credits} | {$i18n.footer.license} | {$i18n.footer.improve} diff --git a/dbinstall.php b/dbinstall.php index 09c8d02e..5144d196 100644 --- a/dbinstall.php +++ b/dbinstall.php @@ -805,7 +805,7 @@ if ($_POST) { print "Updated $users_updated users...
\n"; } - if ($_POST["convert11744to11796"]) { + if ($_POST["convert11744to11797"]) { setChange("update `tt_roles` inner join `tt_site_config` sc on (sc.param_name = 'version_db' and sc.param_value = '1.17.44') set rights = replace(rights, 'override_punch_mode,override_date_lock', 'override_punch_mode,override_own_punch_mode,override_date_lock')"); setChange("UPDATE `tt_site_config` SET param_value = '1.17.48' where param_name = 'version_db' and param_value = '1.17.44'"); setChange("update `tt_users` inner join `tt_site_config` sc on (sc.param_name = 'version_db' and sc.param_value = '1.17.48') set role_id = (select id from tt_roles where team_id = 0 and rank = 512) where role = 324"); @@ -904,6 +904,9 @@ if ($_POST) { setChange("ALTER TABLE `tt_log` ADD `group_id` int(11) default NULL AFTER `user_id`"); setChange("ALTER TABLE `tt_expense_items` ADD `group_id` int(11) default NULL AFTER `user_id`"); setChange("UPDATE `tt_site_config` SET param_value = '1.17.96', modified = now() where param_name = 'version_db' and param_value = '1.17.92'"); + setChange("create index group_idx on tt_log(group_id)"); + setChange("create index group_idx on tt_expense_items(group_id)"); + setChange("UPDATE `tt_site_config` SET param_value = '1.17.97', modified = now() where param_name = 'version_db' and param_value = '1.17.96'"); } // The update_group_id function updates group_id field in tt_log and tt_expense_items tables. @@ -996,7 +999,7 @@ if ($_POST) {

DB Install

-
Create database structure (v1.17.96) + Create database structure (v1.17.97)
(applies only to new installations, do not execute when updating)
@@ -1036,8 +1039,8 @@ if ($_POST) {
Update database structure (v1.17.44 to v1.17.96)
Update database structure (v1.17.44 to v1.17.97)
diff --git a/mysql.sql b/mysql.sql index 79617668..62c1d7c4 100644 --- a/mysql.sql +++ b/mysql.sql @@ -202,6 +202,7 @@ CREATE TABLE `tt_log` ( # Create indexes on tt_log for performance. create index date_idx on tt_log(date); create index user_idx on tt_log(user_id); +create index group_idx on tt_log(group_id); create index client_idx on tt_log(client_id); create index invoice_idx on tt_log(invoice_id); create index project_idx on tt_log(project_id); @@ -408,6 +409,7 @@ CREATE TABLE `tt_expense_items` ( # Create indexes on tt_expense_items for performance. create index date_idx on tt_expense_items(date); create index user_idx on tt_expense_items(user_id); +create index group_idx on tt_expense_items(group_id); create index client_idx on tt_expense_items(client_id); create index project_idx on tt_expense_items(project_id); create index invoice_idx on tt_expense_items(invoice_id); @@ -452,4 +454,4 @@ CREATE TABLE `tt_site_config` ( PRIMARY KEY (`param_name`) ); -INSERT INTO `tt_site_config` (`param_name`, `param_value`, `created`) VALUES ('version_db', '1.17.96', now()); # TODO: change when structure changes. +INSERT INTO `tt_site_config` (`param_name`, `param_value`, `created`) VALUES ('version_db', '1.17.97', now()); # TODO: change when structure changes. -- 2.20.1