Bug 14132 - suggestions table is missing indexes
Summary: suggestions table is missing indexes
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: master
Hardware: All All
: P5 - low normal (vote)
Assignee: Fridolin SOMERS
QA Contact: Testopia
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: 7923
  Show dependency treegraph
 
Reported: 2015-05-04 11:33 UTC by Fridolin SOMERS
Modified: 2015-12-03 22:11 UTC (History)
3 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Trivial patch
Bot Control: ---
When did the bot last check this:
Who signed the patch off:
Text to go in the release notes:


Attachments
Bug 14132 - suggestions table is missing indexes (1.45 KB, patch)
2015-05-04 12:03 UTC, Fridolin SOMERS
Details | Diff | Splinter Review
Bug 14132 - suggestions table is missing indexes - atomicupdate (910 bytes, patch)
2015-05-04 12:03 UTC, Fridolin SOMERS
Details | Diff | Splinter Review
Bug 14132 - suggestions table is missing indexes (1.45 KB, patch)
2015-05-05 07:37 UTC, Fridolin SOMERS
Details | Diff | Splinter Review
Bug 14132 - suggestions table is missing indexes - atomicupdate (969 bytes, patch)
2015-05-05 07:38 UTC, Fridolin SOMERS
Details | Diff | Splinter Review
[SIGNED-OFF] Bug 14132: suggestions table is missing indexes (1.53 KB, patch)
2015-05-06 23:09 UTC, Bernardo Gonzalez Kriegel
Details | Diff | Splinter Review
[SIGNED-OFF] Bug 14132: suggestions table is missing indexes - atomicupdate (1.03 KB, patch)
2015-05-06 23:09 UTC, Bernardo Gonzalez Kriegel
Details | Diff | Splinter Review
[PASSED QA] Bug 14132: suggestions table is missing indexes (1.60 KB, patch)
2015-05-08 12:47 UTC, Kyle M Hall
Details | Diff | Splinter Review
[PASSED QA] Bug 14132: suggestions table is missing indexes - atomicupdate (1.10 KB, patch)
2015-05-08 12:47 UTC, Kyle M Hall
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Fridolin SOMERS 2015-05-04 11:33:57 UTC
Suggestions table is missing some indexes.
Comment 1 Fridolin SOMERS 2015-05-04 12:03:37 UTC Comment hidden (obsolete)
Comment 2 Fridolin SOMERS 2015-05-04 12:03:48 UTC Comment hidden (obsolete)
Comment 3 Marc Véron 2015-05-04 12:18:24 UTC
Should this have the status "Needs sign-off"?

For the second patch: 
Instead of
ALTER TABLE `suggestions`...
maybe better:
ALTER IGNORE TABLE `suggestions`...

http://wiki.koha-community.org/wiki/Database_updates
Comment 4 Fridolin SOMERS 2015-05-05 07:28:13 UTC
(In reply to Marc Véron from comment #3)
> Should this have the status "Needs sign-off"?
Indeed.
I've used git bz without -e.

> 
> For the second patch: 
> Instead of
> ALTER TABLE `suggestions`...
> maybe better:
> ALTER IGNORE TABLE `suggestions`...
> 
> http://wiki.koha-community.org/wiki/Database_updates

Ok thanks. I did not knew that INGORE can be used with ALTER TABLE.
Comment 5 Fridolin SOMERS 2015-05-05 07:37:57 UTC Comment hidden (obsolete)
Comment 6 Fridolin SOMERS 2015-05-05 07:38:08 UTC Comment hidden (obsolete)
Comment 7 Fridolin SOMERS 2015-05-05 07:54:28 UTC
Ready for signoff.

I've spitted each alter table in a unique line because if one key already exists the all line fails.
If the key alreeady exists, it will still return an error even with ALTER IGNORE. Looks like the is only useful for unique keys.
Comment 8 Bernardo Gonzalez Kriegel 2015-05-06 23:09:17 UTC Comment hidden (obsolete)
Comment 9 Bernardo Gonzalez Kriegel 2015-05-06 23:09:34 UTC Comment hidden (obsolete)
Comment 10 Kyle M Hall 2015-05-08 12:47:26 UTC
Created attachment 38975 [details] [review]
[PASSED QA] Bug 14132: suggestions table is missing indexes

Suggestions table is missing some indexes.

Index on biblionumber will speed up C4::Suggestions::GetSuggestionInfoFromBiblionumber(), often called in a loop.
Index on status and branchcode will speed up many queries.
Note that an index on budgetid is automatically added by the creation of the foreign key constraint.

Test plan :
- Check performance with an invoice containing a lot of orders.
- Check suggestions pages are faster or at least not slower.

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

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 11 Kyle M Hall 2015-05-08 12:47:34 UTC
Created attachment 38976 [details] [review]
[PASSED QA] Bug 14132: suggestions table is missing indexes - atomicupdate

Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com>
Sorry but removed backticks
No errors

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 12 Tomás Cohen Arazi 2015-05-14 15:15:45 UTC
Patches pushed to master.

Thanks Frido!