[Koha-patches] [PATCH] Bug 7886 - C4/ShelfBrowser slow SQL performance

[Marc Balmer]

Am 06.04.12 19:10, schrieb Dobrica Pavlinusic:
> 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)

ah, it's these MySQL backquotes again... Can't we get rid of them in new
code?

> ---
>  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)