Bug 11249 - Add db indexes on borrowers names
Summary: Add db indexes on borrowers names
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: master
Hardware: All All
: P5 - low normal (vote)
Assignee: Galen Charlton
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2013-11-14 13:29 UTC by Fridolin Somers
Modified: 2018-04-24 11:52 UTC (History)
2 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:


Attachments
Bug 11249 - Add db indexes on borrowers names (2.84 KB, patch)
2013-11-14 13:33 UTC, Fridolin Somers
Details | Diff | Splinter Review
Bug 11249 - Add db indexes on borrowers names (2.90 KB, patch)
2013-11-18 16:11 UTC, Fridolin Somers
Details | Diff | Splinter Review
[PATCH][SIGNED OFF] Bug 11249 - Add db indexes on borrowers names (3.00 KB, patch)
2013-11-29 13:01 UTC, Mathieu Saby
Details | Diff | Splinter Review
[PASSED QA] Bug 11249 - Add db indexes on borrowers names (3.21 KB, patch)
2013-12-03 12:41 UTC, Katrin Fischer
Details | Diff | Splinter Review
Bug 11249: DBRev 3.15.00.006 (1.60 KB, patch)
2018-04-17 18:03 UTC, David Gustafsson
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
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)