Bug 36178

Summary: Add index for status on message queue table
Product: Koha Reporter: Kyle M Hall (khall) <kyle>
Component: Architecture, internals, and plumbingAssignee: Kyle M Hall (khall) <kyle>
Status: Failed QA --- QA Contact: Marcel de Rooy <m.de.rooy>
Severity: minor    
Priority: P5 - low CC: m.de.rooy, wizzyrea
Version: Main   
Hardware: All   
OS: All   
GIT URL: Change sponsored?: ---
Patch complexity: --- Documentation contact:
Documentation submission: Text to go in the release notes:
Version(s) released in:
Circulation function:
Attachments: Bug 36178: Add index for status on message queue table
Bug 36178: Add index for status on message queue table

Description Kyle M Hall (khall) 2024-02-27 14:44:29 UTC
For large libraries, the query to gather pending notices from the message queue can take a very long time. ByWater's Rackpace DBA Mike was kind enough to do some work on this for us and found the issue. He recommends adding a simple index on the status column to fix this issue.
Comment 1 Kyle M Hall (khall) 2024-02-27 14:59:20 UTC
Created attachment 162503 [details] [review]
Bug 36178: Add index for status on message queue table

For large libraries, the query to gather pending notices from the message queue can take a very long time. ByWater's Rackpace DBA Mike was kind enough to do some work on this for us and found the issue. He recommends adding a simple index on the status column to fix this issue.

Test Plan:
1) Apply this patch
2) Run updatedatabase.pl
3) SHOW CREATE TABLE message_queue
   should show the new index via koha-mysql
Comment 2 Liz Rea 2024-02-27 15:08:01 UTC
Mike Griffin at Rackspace was the originator of this suggested change. :D
Comment 3 Andrew Fuerste-Henry 2024-03-01 18:30:48 UTC
Created attachment 162685 [details] [review]
Bug 36178: Add index for status on message queue table

For large libraries, the query to gather pending notices from the message queue can take a very long time. ByWater's Rackpace DBA Mike was kind enough to do some work on this for us and found the issue. He recommends adding a simple index on the status column to fix this issue.

Test Plan:
1) Apply this patch
2) Run updatedatabase.pl
3) SHOW CREATE TABLE message_queue
   should show the new index via koha-mysql

Signed-off-by: Andrew Fuerste Henry <andrewfh@dubcolib.org>
Comment 4 Marcel de Rooy 2024-04-05 08:38:22 UTC
Weird condition:
+        unless ( index_exists( 'message_queue', 'language_subtag' ) ) {

 WARN   installer/data/mysql/atomicupdate/bug_36178.pl
   WARN   tidiness
                The file is less tidy than before (bad/messy lines before: 0, now: 2)