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

[Dobrica Pavlinusic]

On Fri, Apr 06, 2012 at 10:04:26PM +0200, Marc Balmer wrote:
> 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?

I wrote updatedatabase.pl in non-database specific way, but
data/mysql/kohastructure.sql is MySQL specific anyway, so I decided to be
consistent with rest of SQL file since there is also
installer/data/Pg/kohastructure.sql and DDL syntax in
data/mysql/kohastructure.sql wouldn't work on PostgreSQL even if it didn't
have quotes.

Is this wrong assumption?

While we are at installer/data/Pg/kohastructure.sql it seems it wasn't
touched since 2009. Should I provide patch against it also?

> > ---
> >  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)
> 
> _______________________________________________
> Koha-patches mailing list
> Koha-patches at lists.koha-community.org
> http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-patches
> website : http://www.koha-community.org/
> git : http://git.koha-community.org/
> bugs : http://bugs.koha-community.org/

-- 
Dobrica Pavlinusic               2share!2flame            dpavlin at rot13.org
Unix addict. Internet consultant.             http://www.rot13.org/~dpavlin