Bug 36033

Summary: Table pseudonymized_transactions needs more indexes
Product: Koha Reporter: Fridolin Somers <fridolin.somers>
Component: DatabaseAssignee: Fridolin Somers <fridolin.somers>
Status: Pushed to oldstable --- QA Contact: Testopia <testopia>
Severity: minor    
Priority: P5 - low CC: david, lucas, m.de.rooy
Version: Main   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33890
Change sponsored?: --- Patch complexity: Trivial patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
24.05.00,23.11.06,23.05.12
Circulation function:
Attachments: Bug 36033: Add more indexes to table pseudonymized_transactions
Bug 36033: Add more indexes to table pseudonymized_transactions
Bug 36033: Add more indexes to table pseudonymized_transactions
Bug 36033: Add more indexes to table pseudonymized_transactions

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    |                                    |
+------+-------------+----------------------------+--------+-------------------------------------------------------------------------------------------------------------+-----------------------------------+---------+----------------------------------------------------+------+------------------------------------+
Comment 5 Marcel de Rooy 2024-03-15 10:51:56 UTC
(In reply to Fridolin Somers from comment #0)
> To improve SQL queries performance, it needs more indexes, specially on
> itemnumber.

Could you provide further details for the need of those indexes? I am seeing a search on datetime in cleanup_database.
Where, when do we need the other ones?

   KEY `pseudonymized_transactions_ibfk_1` (`categorycode`),
   KEY `pseudonymized_transactions_borrowers_ibfk_2` (`branchcode`),
-  KEY `pseudonymized_transactions_borrowers_ibfk_3` (`transaction_branchcode`)
+  KEY `pseudonymized_transactions_borrowers_ibfk_3` (`transaction_branchcode`),
+  KEY `pseudonymized_transactions_items_ibfk_4` (`itemnumber`),
+  KEY `pseudonymized_transactions_ibfk_5` (`transaction_type`),
+  KEY `pseudonymized_transactions_ibfk_6` (`datetime`)

The names are confusing? But seeing more confusion in structure there. No blocker.
Comment 6 Fridolin Somers 2024-03-21 10:53:33 UTC
Table "pseudonymized_transactions" is dedicated to be used in SQL reports.

There is a good chance "transaction_type" will be used to count checkouts, checkins ...

The "itemnumber" can be used for a join with items table. This will be much more performant with an index.
Some item datas are in pseudonymized_transactions (for when item is deleted).
But for catalogs that mostly do not delete there items they may want to join for more datas.
Also can be used to count patron checkouts on same item.

See that table "statistics" as indexes :
  KEY `type_idx` (`type`),
  KEY `itemnumber_idx` (`itemnumber`),

I agree we need to balance query performance with row creating speed.
So we may not add indexes on all columns.
Comment 7 Marcel de Rooy 2024-03-22 07:34:19 UTC
Created attachment 163659 [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>

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 8 Katrin Fischer 2024-03-22 09:37:29 UTC
Pushed for 24.05!

Well done everyone, thank you!
Comment 9 Fridolin Somers 2024-05-22 13:48:50 UTC
Pushed to 23.11.x for 23.11.06
Comment 10 Lucas Gass (lukeg) 2024-05-23 14:49:38 UTC
Backported to 23.05.x for upcoming 23.05.12