Bug 36033

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'.

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'.

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>

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    |                                    |
+------+-------------+----------------------------+--------+-------------------------------------------------------------------------------------------------------------+-----------------------------------+---------+----------------------------------------------------+------+------------------------------------+

(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.

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.

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>

Pushed for 24.05!

Well done everyone, thank you!

Pushed to 23.11.x for 23.11.06

Backported to 23.05.x for upcoming 23.05.12