From 3317a32f572a2e3605071410e7ddd8082e0b03ee Mon Sep 17 00:00:00 2001 From: "Jason (Jayce^) Hall" Date: Tue, 2 Sep 2014 11:00:08 -0600 Subject: [PATCH] Add in history_usernum and history_username indices. This allows for searches on *who* made changes to run faster. The best example of this is on the homepage when it looks for the last modified customers by the current user. On our system that query could take 5 minutes across so many history records. Now takes a manageable amount of time with this index --- FS/FS/Schema.pm | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) diff --git a/FS/FS/Schema.pm b/FS/FS/Schema.pm index 2aba2eab5..c635ea0a0 100644 --- a/FS/FS/Schema.pm +++ b/FS/FS/Schema.pm @@ -243,6 +243,23 @@ sub dbdef_dist { ], }); + #necessary for queries that want to look at *who* made changes + $h_indices{"h_${table}_usernum"} = + DBIx::DBSchema::Index->new({ + 'name' => "h_${table}_usernum", + 'unique' => 0, + 'columns' => [ 'history_usernum'], + }); + + # necessary because of the evil OR username for older data, be really nice if everything was just migrated to usernum and we could drop username + # This will not be helpful to mysql, but postgres smartly does a bitmap across both indexes, mysql will just use one + + $h_indices{"h_${table}_username"} = + DBIx::DBSchema::Index->new({ + 'name' => "h_${table}_username", + 'unique' => 0, + 'columns' => [ 'history_username'], + }); } my $primary_key_col = $tableobj->column($tableobj->primary_key) -- 2.11.0