[Koha-bugs] [Bug 36033] Table pseudonymized_transactions needs more indexes

[bugzilla-daemon at bugs.koha-community.org]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=36033

Marcel de Rooy <m.de.rooy at rijksmuseum.nl> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
 Attachment #162012|0                           |1
        is obsolete|                            |

--- Comment #7 from Marcel de Rooy <m.de.rooy at rijksmuseum.nl> ---
Created attachment 163659
  -->
https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=163659&action=edit
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 at davidnind.com>

Signed-off-by: Marcel de Rooy <m.de.rooy at rijksmuseum.nl>

-- 
You are receiving this mail because:
You are watching all bug changes.