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 <paul.poulain@biblibre.com>

diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 03911fe..21c657d 100644 (file)
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -1054,6 +1054,8 @@ CREATE TABLE `items` ( -- holdings/item information
   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 b3c5691..fae6351 100755 (executable)
--- a/installer/data/mysql/updatedatabase.pl
+++ b/installer/data/mysql/updatedatabase.pl
@@ -5159,6 +5159,14 @@ if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) {
     SetVersion($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)