Bug 14132

Summary: suggestions table is missing indexes
Product: Koha Reporter: Fridolin Somers <fridolin.somers>
Component: DatabaseAssignee: Fridolin Somers <fridolin.somers>
Status: CLOSED FIXED QA Contact: Testopia <testopia>
Severity: normal    
Priority: P5 - low CC: bgkriegel, tomascohen, veron
Version: Main   
Hardware: All   
OS: All   
See Also: http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=14053
Change sponsored?: --- Patch complexity: Trivial patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Bug Depends on:    
Bug Blocks: 7923    
Attachments: Bug 14132 - suggestions table is missing indexes
Bug 14132 - suggestions table is missing indexes - atomicupdate
Bug 14132 - suggestions table is missing indexes
Bug 14132 - suggestions table is missing indexes - atomicupdate
[SIGNED-OFF] Bug 14132: suggestions table is missing indexes
[SIGNED-OFF] Bug 14132: suggestions table is missing indexes - atomicupdate
[PASSED QA] Bug 14132: suggestions table is missing indexes
[PASSED QA] Bug 14132: suggestions table is missing indexes - atomicupdate

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!