]> git.koha-community.org Git - koha.git/commit
Bug 36033: Add more indexes to table pseudonymized_transactions
authorFridolin Somers <fridolin.somers@biblibre.com>
Thu, 8 Feb 2024 09:25:12 +0000 (10:25 +0100)
committerLucas Gass <lucas@bywatersolutions.com>
Thu, 23 May 2024 14:47:34 +0000 (14:47 +0000)
commit49ae122dbfc6d73f12256b8f1642179d759ce1a6
tree8489792b781ecea8373e77f27db33bd8245d5d7a
parentbfc1cdccccfe6359538259441fe2b135769f51e9
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>
Signed-off-by: Katrin Fischer <katrin.fischer@bsz-bw.de>
(cherry picked from commit 0573d01eaa2da7e0b53fd24054e5a3d4e0c2b056)
Signed-off-by: Fridolin Somers <fridolin.somers@biblibre.com>
(cherry picked from commit 6ba45bc290685a517e8bc1967910b6744162499f)
Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
installer/data/mysql/atomicupdate/bug_36033.pl [new file with mode: 0755]
installer/data/mysql/kohastructure.sql