Bug 9681

Summary: Add some indexes in database
Product: Koha Reporter: Fridolin Somers <fridolin.somers>
Component: DatabaseAssignee: Fridolin Somers <fridolin.somers>
Status: CLOSED FIXED QA Contact: Paul Poulain <paul.poulain>
Severity: normal    
Priority: P5 - low CC: bgkriegel, paul.poulain
Version: Main   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: Trivial patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Attachments: Proposed patch
Proposed patch (better udpate message)
[SIGNED-OFF] Bug 9681: Add some indexes in database
Bug 9681: Add some indexes in database

Description Fridolin Somers 2013-02-21 10:58:04 UTC
Some table columns should have an index to speed SQL queries :
- statistics table has only one index (used to make heavy queries for reports)
- itemtype should have an index in items and biblioitems (this is used in circulation to check the number of existing issues of a specific item type)
- issue.branchcode and issue.issuingbranch

Note that the fact that an index is automatically created for foreign keys in InnoBD seems to be in discussion, so better create it explicitly.
Comment 1 Fridolin Somers 2013-02-21 11:07:21 UTC Comment hidden (obsolete)
Comment 2 Fridolin Somers 2013-02-21 17:16:44 UTC Comment hidden (obsolete)
Comment 3 Bernardo Gonzalez Kriegel 2013-02-23 01:58:35 UTC Comment hidden (obsolete)
Comment 4 Paul Poulain 2013-05-06 13:05:16 UTC
QA comments :
 * this patch just adds some indexes, no possible side effect
 * it will speed up a lot some of the statistics SQL queries, so bumping severity from "ENH" to "NOR". Including this patch for 3.12 is RM choice, but I think it's worth it
 * tested updatedatabase and kohastructure.sql, everything OK

Passed QA
Comment 5 Paul Poulain 2013-05-06 13:07:27 UTC
Created attachment 17959 [details] [review]
Bug 9681: Add some indexes in database

Some table columns should have an index to speed SQL queries :
- statistics table has only one index (used to make heavy queries for reports)
- itemtype should have an index in items and biblioitems (this is used in circulation to check the number of existing issues of a specific item type)
- issue.branchcode and issue.issuingbranch

Test plan :
- Create a new database using kohastructure.pl
=> check there is no SQL error and that new indexes are present (mysql> show create table)
Apply update database on a master version
=> check there is no SQL error and that new indexes are present (mysql> show create table)

Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com>

Comment: Indexes created as described. No errors.
Fixing small merge conflict.
Test presence of new indexes on all affected tables.

Signed-off-by: Paul Poulain <paul.poulain@biblibre.com>
Comment 6 Jared Camins-Esakov 2013-05-08 14:04:10 UTC
This patch has been pushed to master and 3.12.x.
Comment 7 Fridolin Somers 2013-09-24 13:01:46 UTC
Can it been pushed to 3.10 ?