Bug 9681 - Add some indexes in database
Summary: Add some indexes in database
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: Main
Hardware: All All
: P5 - low normal (vote)
Assignee: Fridolin Somers
QA Contact: Paul Poulain
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2013-02-21 10:58 UTC by Fridolin Somers
Modified: 2014-12-07 20:02 UTC (History)
2 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Trivial patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
Proposed patch (6.86 KB, patch)
2013-02-21 11:07 UTC, Fridolin Somers
Details | Diff | Splinter Review
Proposed patch (better udpate message) (6.86 KB, patch)
2013-02-21 17:16 UTC, Fridolin Somers
Details | Diff | Splinter Review
[SIGNED-OFF] Bug 9681: Add some indexes in database (7.04 KB, patch)
2013-02-23 01:58 UTC, Bernardo Gonzalez Kriegel
Details | Diff | Splinter Review
Bug 9681: Add some indexes in database (7.08 KB, patch)
2013-05-06 13:07 UTC, Paul Poulain
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
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 ?