[Koha-patches] [PATCH] Bug 7886 - C4/ShelfBrowser slow SQL performance
Dobrica Pavlinusic
dpavlin at rot13.org
Fri Apr 6 19:10:21 CEST 2012
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)
---
installer/data/mysql/kohastructure.sql | 2 ++
installer/data/mysql/updatedatabase.pl | 8 ++++++++
2 files changed, 10 insertions(+), 0 deletions(-)
diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 03911fe..21c657d 100644
--- 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
--- 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)
--
1.7.2.5
More information about the Koha-patches
mailing list