From 60cf55bceb45c9ca06e9b65f55fbfae2699d8cc5 Mon Sep 17 00:00:00 2001 From: Dobrica Pavlinusic Date: Thu, 5 Apr 2012 01:03:30 +0200 Subject: [PATCH] Bug 7886 - C4/ShelfBrowser slow SQL performance ShelfBrowser uses two SQL queries with syntax WHERE ((cn_sort = ? AND itemnumber < ?) OR cn_sort < ?) AND homebranch = ? AND location = ? AND ccode = ? homebranch already has index, and adding indexes on ccode and location improves performance by 30% for each query since MySQL is able to use index_merge intersect(items_ccode,homebranch,items_location) Signed-off-by: Paul Poulain (cherry picked from commit d8e702dd25dfb09f99620ea9f5d3251033d16864) Signed-off-by: Jared Camins-Esakov --- installer/data/mysql/updatedatabase.pl | 9 +++++++++ 1 file changed, 9 insertions(+) diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 0c0f19f5b9..f089b7e119 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -4697,6 +4697,7 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { SetVersion($DBversion); } + $DBversion = "3.06.07.000"; if (C4::Context->preference("Version") < TransformToNum($DBversion)) { print "Upgrade to $DBversion done (Incrementing version for 3.6.7 release. See release notes for details.) \n"; @@ -4704,6 +4705,14 @@ if (C4::Context->preference("Version") < TransformToNum($DBversion)) { } +$DBversion = "3.06.00.XXX"; +if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { + $dbh->do("CREATE INDEX items_location ON items(location)"); + $dbh->do("CREATE INDEX items_ccode ON items(ccode)"); + print "Upgrade to $DBversion done (items_location and items_ccode indexes added for ShelfBrowser)"; + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 DropAllForeignKeys($table) -- 2.39.5