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>
Signed-off-by: Tomas Cohen Arazi <tomascohen@gmail.com>

diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql
index 9b17ca9..aaef328 100644 (file)
--- a/installer/data/mysql/kohastructure.sql
+++ b/installer/data/mysql/kohastructure.sql
@@ -2203,6 +2203,9 @@ CREATE TABLE `suggestions` ( -- purchase suggestions
   PRIMARY KEY  (`suggestionid`),
   KEY `suggestedby` (`suggestedby`),
   KEY `managedby` (`managedby`),
+  KEY `status` (`STATUS`),
+  KEY `biblionumber` (`biblionumber`),
+  KEY `branchcode` (`branchcode`),
   CONSTRAINT `suggestions_budget_id_fk` FOREIGN KEY (`budgetid`) REFERENCES `aqbudgets` (`budget_id`) ON DELETE SET NULL ON UPDATE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;