Bug 36033 - Table pseudonymized_transactions needs more indexes
Summary: Table pseudonymized_transactions needs more indexes
Status: Pushed to oldstable
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: Main
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-05-23 14:49 UTC (History)
3 users (show)

See Also:
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


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
Bug 36033: Add more indexes to table pseudonymized_transactions (4.09 KB, patch)
2024-03-22 07:34 UTC, Marcel de Rooy
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    |                                    |
+------+-------------+----------------------------+--------+-------------------------------------------------------------------------------------------------------------+-----------------------------------+---------+----------------------------------------------------+------+------------------------------------+
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 2024-05-23 14:49:38 UTC
Backported to 23.05.x for upcoming 23.05.12