Bug 11249

Summary: Add db indexes on borrowers names
Product: Koha Reporter: Fridolin Somers <fridolin.somers>
Component: DatabaseAssignee: Galen Charlton <gmcharlt>
Status: CLOSED FIXED QA Contact: Testopia <testopia>
Severity: normal    
Priority: P5 - low CC: glasklas, mathsabypro
Version: Main   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: Trivial patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Attachments: Bug 11249 - Add db indexes on borrowers names
Bug 11249 - Add db indexes on borrowers names
[PATCH][SIGNED OFF] Bug 11249 - Add db indexes on borrowers names
[PASSED QA] Bug 11249 - Add db indexes on borrowers names
Bug 11249: DBRev 3.15.00.006

Description Fridolin Somers 2013-11-14 13:29:36 UTC
The borrowers search is by default on columns surname, firstname, othernames and cardnumber.
(See C4::Members::_express_member_find).
Addind db indexes will really increase the query speed.
Comment 1 Fridolin Somers 2013-11-14 13:33:44 UTC Comment hidden (obsolete)
Comment 2 Fridolin Somers 2013-11-18 16:11:14 UTC Comment hidden (obsolete)
Comment 3 Mathieu Saby 2013-11-29 13:01:30 UTC Comment hidden (obsolete)
Comment 4 Katrin Fischer 2013-12-03 12:41:35 UTC
Created attachment 23262 [details] [review]
[PASSED QA] Bug 11249 - Add db indexes on borrowers names

The borrowers search is by default on columns surname, firstname, othernames and cardnumber.
(See C4::Members::_express_member_find).
Addind db indexes will really increase the query speed.

This patch adds bd indexes on surname, firstname, othernames (cardnumber has already an index).
Those indexes must be defined with a size because columns are mediumtext.

Test plan :
Test with mysql client :
mysql> explain select * from borrowers where surname like 'A%';
+----+-------------+-----------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table     | type  | possible_keys | key         | key_len | ref  | rows | Extra       |
+----+-------------+-----------+-------+---------------+-------------+---------+------+------+-------------+
|  1 | SIMPLE      | borrowers | range | surname_idx   | surname_idx | 767     | NULL |  395 | Using where |
+----+-------------+-----------+-------+---------------+-------------+---------+------+------+-------------+
=> key show the index is used

Signed-off-by: Mathieu Saby <mathieu.saby@univ-rennes2.fr>
Signed-off-by: Katrin Fischer <Katrin.Fischer.83@web.de>
Works as described, changes from updatedatabase and in kohastructure match.
I think deletedborrowers can be left out, as it's not queried when doing
patron searches. Patron deletes still work as expected.
Comment 5 Galen Charlton 2013-12-17 15:32:55 UTC
Pushed to master.  Thanks, Fridolin!
Comment 6 Fridolin Somers 2014-01-27 11:29:47 UTC
Patch pushed to 3.14.x, will be in 3.14.3.
(creates updatedatabase 3.14.02.001)
Comment 7 David Gustafsson 2018-04-17 18:03:21 UTC Comment hidden (obsolete)
Comment 8 David Gustafsson 2018-04-17 18:09:38 UTC Comment hidden (obsolete)