From 1f6cc04bff3065d2c719a14e8f8af624f64c8ba9 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 Signed-off-by: Jared Camins-Esakov --- installer/data/mysql/kohastructure.sql | 3 +++ installer/data/mysql/updatedatabase.pl | 8 ++++++++ 2 files changed, 11 insertions(+) diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index ce793c1fe3..67d2d2a6b0 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -1043,6 +1043,9 @@ CREATE TABLE `items` ( -- holdings/item information KEY `itembibnoidx` (`biblionumber`), KEY `homebranch` (`homebranch`), KEY `holdingbranch` (`holdingbranch`), + KEY `itemcallnumber` (`itemcallnumber`), + KEY `items_location` (`location`), + KEY `items_ccode` (`ccode`), CONSTRAINT `items_ibfk_1` FOREIGN KEY (`biblioitemnumber`) REFERENCES `biblioitems` (`biblioitemnumber`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `items_ibfk_2` FOREIGN KEY (`homebranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE, CONSTRAINT `items_ibfk_3` FOREIGN KEY (`holdingbranch`) REFERENCES `branches` (`branchcode`) ON UPDATE CASCADE diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index ca02cd7183..160ea2f262 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -4663,6 +4663,14 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { } +$DBversion = "3.07.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