Bug 36033 - Table pseudonymized_transactions needs more indexes
Summary: Table pseudonymized_transactions needs more indexes
Status: Signed Off
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: master
Hardware: All All
: P5 - low minor (vote)
Assignee: Fridolin Somers
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2024-02-08 09:04 UTC by Fridolin Somers
Modified: 2024-02-10 21:54 UTC (History)
1 user (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
Bug 36033: Add more indexes to table pseudonymized_transactions (3.96 KB, patch)
2024-02-08 09:47 UTC, Fridolin Somers
Details | Diff | Splinter Review
Bug 36033: Add more indexes to table pseudonymized_transactions (3.95 KB, patch)
2024-02-08 09:53 UTC, Fridolin Somers
Details | Diff | Splinter Review
Bug 36033: Add more indexes to table pseudonymized_transactions (4.00 KB, patch)
2024-02-10 21:52 UTC, David Nind
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Fridolin Somers 2024-02-08 09:04:51 UTC
Table pseudonymized_transactions contains :
  KEY `pseudonymized_transactions_ibfk_1` (`categorycode`),
  KEY `pseudonymized_transactions_borrowers_ibfk_2` (`branchcode`),
  KEY `pseudonymized_transactions_borrowers_ibfk_3` (`transaction_branchcode`)

To improve SQL queries performance, it needs more indexes, specially on itemnumber.
Comment 1 Fridolin Somers 2024-02-08 09:47:17 UTC
Created attachment 161879 [details] [review]
Bug 36033: Add more indexes to table pseudonymized_transactions

Table pseudonymized_transactions contains :
  KEY `pseudonymized_transactions_ibfk_1` (`categorycode`),
  KEY `pseudonymized_transactions_borrowers_ibfk_2` (`branchcode`),
  KEY `pseudonymized_transactions_borrowers_ibfk_3` (`transaction_branchcode`)

To improve SQL queries performance, it needs more indexes, specially on itemnumber.

Looking at table statistics :
  KEY `timeidx` (`datetime`),
  KEY `branch_idx` (`branch`),
  KEY `type_idx` (`type`),
  KEY `itemnumber_idx` (`itemnumber`),

So index is need on pseudonymized_transactions columns :
itemnumber => For join with table items
transaction_type => For filter on type issue, return ...
datetime => For filter on date, this will help cleanup script

Test plan :
1) Run updatedatabase.pl
2) Check indexes are created in table pseudonymized_transactions
3) Run SQL query :
   describe select * from pseudonymized_transactions join items using(itemnumber)
   where transaction_type='issue' and datetime < date_sub(curdate(), INTERVAL 30 DAY)
=> You see the 3 new indexes used in 'possible_keys'.
Comment 2 Fridolin Somers 2024-02-08 09:53:13 UTC
Created attachment 161880 [details] [review]
Bug 36033: Add more indexes to table pseudonymized_transactions

Table pseudonymized_transactions contains :
  KEY `pseudonymized_transactions_ibfk_1` (`categorycode`),
  KEY `pseudonymized_transactions_borrowers_ibfk_2` (`branchcode`),
  KEY `pseudonymized_transactions_borrowers_ibfk_3` (`transaction_branchcode`)

To improve SQL queries performance, it needs more indexes, specially on itemnumber.

Looking at table statistics :
  KEY `timeidx` (`datetime`),
  KEY `branch_idx` (`branch`),
  KEY `type_idx` (`type`),
  KEY `itemnumber_idx` (`itemnumber`),

So index is need on pseudonymized_transactions columns :
itemnumber => For join with table items
transaction_type => For filter on type issue, return ...
datetime => For filter on date, this will help cleanup script

Test plan :
1) Run updatedatabase.pl
2) Check indexes are created in table pseudonymized_transactions
3) Run SQL query :
   describe select * from pseudonymized_transactions join items using(itemnumber)
   where transaction_type='issue' and datetime < date_sub(curdate(), INTERVAL 30 DAY)
=> You see the 3 new indexes used in 'possible_keys'.
Comment 3 David Nind 2024-02-10 21:52:26 UTC
Created attachment 162012 [details] [review]
Bug 36033: Add more indexes to table pseudonymized_transactions

Table pseudonymized_transactions contains :
  KEY `pseudonymized_transactions_ibfk_1` (`categorycode`),
  KEY `pseudonymized_transactions_borrowers_ibfk_2` (`branchcode`),
  KEY `pseudonymized_transactions_borrowers_ibfk_3` (`transaction_branchcode`)

To improve SQL queries performance, it needs more indexes, specially on itemnumber.

Looking at table statistics :
  KEY `timeidx` (`datetime`),
  KEY `branch_idx` (`branch`),
  KEY `type_idx` (`type`),
  KEY `itemnumber_idx` (`itemnumber`),

So index is need on pseudonymized_transactions columns :
itemnumber => For join with table items
transaction_type => For filter on type issue, return ...
datetime => For filter on date, this will help cleanup script

Test plan :
1) Run updatedatabase.pl
2) Check indexes are created in table pseudonymized_transactions
3) Run SQL query :
   describe select * from pseudonymized_transactions join items using(itemnumber)
   where transaction_type='issue' and datetime < date_sub(curdate(), INTERVAL 30 DAY)
=> You see the 3 new indexes used in 'possible_keys'.

Signed-off-by: David Nind <david@davidnind.com>
Comment 4 David Nind 2024-02-10 21:54:01 UTC
Results of the query before and after the patch is applied
==========================================================

Before:

+------+-------------+----------------------------+--------+---------------+---------+---------+----------------------------------------------------+------+-------------+
| id   | select_type | table                      | type   | possible_keys | key     | key_len | ref                                                | rows | Extra       |
+------+-------------+----------------------------+--------+---------------+---------+---------+----------------------------------------------------+------+-------------+
|    1 | SIMPLE      | pseudonymized_transactions | ALL    | NULL          | NULL    | NULL    | NULL                                               | 1    | Using where |
|    1 | SIMPLE      | items                      | eq_ref | PRIMARY       | PRIMARY | 4       | koha_kohadev.pseudonymized_transactions.itemnumber | 1    |             |
+------+-------------+----------------------------+--------+---------------+---------+---------+----------------------------------------------------+------+-------------+

After:

+------+-------------+----------------------------+--------+-------------------------------------------------------------------------------------------------------------+-----------------------------------+---------+----------------------------------------------------+------+------------------------------------+
| id   | select_type | table                      | type   | possible_keys                                                                                               | key                               | key_len | ref                                                | rows | Extra                              |
+------+-------------+----------------------------+--------+-------------------------------------------------------------------------------------------------------------+-----------------------------------+---------+----------------------------------------------------+------+------------------------------------+
|    1 | SIMPLE      | pseudonymized_transactions | ref    | pseudonymized_transactions_items_ibfk_4,pseudonymized_transactions_ibfk_5,pseudonymized_transactions_ibfk_6 | pseudonymized_transactions_ibfk_5 | 67      | const                                              | 1    | Using index condition; Using where |
|    1 | SIMPLE      | items                      | eq_ref | PRIMARY                                                                                                     | PRIMARY                           | 4       | koha_kohadev.pseudonymized_transactions.itemnumber | 1    |                                    |
+------+-------------+----------------------------+--------+-------------------------------------------------------------------------------------------------------------+-----------------------------------+---------+----------------------------------------------------+------+------------------------------------+