Bug 26356 - Search by first and last name takes a long time
Summary: Search by first and last name takes a long time
Status: CLOSED INVALID
Alias: None
Product: Koha
Classification: Unclassified
Component: Searching (show other bugs)
Version: 20.05
Hardware: All All
: P5 - low major (vote)
Assignee: Bugs List
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2020-09-02 15:06 UTC by Jonathan Cuéllar
Modified: 2022-12-12 21:23 UTC (History)
3 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
Search by first and last name takes a long time (63.24 KB, text/plain)
2020-09-02 15:06 UTC, Jonathan Cuéllar
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Jonathan Cuéllar 2020-09-02 15:06:40 UTC
Created attachment 109546 [details]
Search by first and last name takes a long time

Currently when performing a search by user in Koha using first and last name, the system takes more than 22 minutes to execute the search, while the identification number does not delay, what can this inconvenience be?.
Attachment dispatch script reported that performs search and delay.
Comment 1 Katrin Fischer 2020-09-02 15:09:18 UTC
how many patrons do you have in your database? Which Koha version?

What was the search term like? (not asking for personal information, but maybe a similar made up one)
Comment 2 Owen Leonard 2020-09-02 15:27:30 UTC
Based on this and the other report you posted today, Bug 26353, it sounds like you have a server issue which is outside the scope of the help we can provide via Bugzilla.

I suggest you seek help on the Koha mailing list (http://koha-community.org/support/koha-mailing-lists/), in the #koha IRC channel (http://koha-community.org/get-involved/irc/) or seek paid support here: https://koha-community.org/support/
Comment 3 Jonathan Cuéllar 2020-09-02 15:44:51 UTC
(In reply to Katrin Fischer from comment #1)
> how many patrons do you have in your database? Which Koha version?
> 
> What was the search term like? (not asking for personal information, but
> maybe a similar made up one)

how many patrons do you have in your database? 
There are more than 51653 records in the borrowers table
Which Koha version?
Koha 20.05
What was the search term like?
I do the search using my name, Jonathan Cuellar
Comment 4 Jonathan Cuéllar 2020-09-02 16:17:08 UTC
(In reply to Owen Leonard from comment #2)
> Based on this and the other report you posted today, Bug 26353, it sounds
> like you have a server issue which is outside the scope of the help we can
> provide via Bugzilla.
> 
> I suggest you seek help on the Koha mailing list
> (http://koha-community.org/support/koha-mailing-lists/), in the #koha IRC
> channel (http://koha-community.org/get-involved/irc/) or seek paid support
> here: https://koha-community.org/support/

I don't think the problem is server-side, knowing that the reports were working, additionally I am more focused on why the query generates so many OR borrower ... OR borrower ... Or borrower, and if there is any way to monitor the SQL generated by the platform. I appreciate the help you can give me.
Comment 5 Katrin Fischer 2020-09-02 19:36:45 UTC
People reading bugs are not that many, on the mailing list you will reach a wider audience and probably achieve more help in confirming the issue. It can help to get a clearer picture of the issue as well and help in fixing if there is a code problem behind things.
Comment 6 David Cook 2020-09-03 00:18:31 UTC
(In reply to Jonathan Cuéllar from comment #0)
> I don't think the problem is server-side, knowing that the reports were
> working, additionally I am more focused on why the query generates so many
> OR borrower ... OR borrower ... Or borrower, and if there is any way to
> monitor the SQL generated by the platform. I appreciate the help you can
> give me.

Mmm very interesting. I don't have time to comprehensively look into this, but it appears that the SQL has so many "OR borrower" strings because Koha found patrons with attributes that matched your search terms... 

That server-side code is problematic. If you had too many matches (eg around 30,000-40,000), you'd probably actually crash Koha (as there limits on the length of SQL queries). I've actually seen this issue in other relational database-driven systems. 

But... if you're searching "jonathan cuellar" and you're getting that many matches off attributes, then it sounds like an issue with your data.
Comment 7 David Cook 2020-09-03 00:19:27 UTC
(In reply to Jonathan Cuéllar from comment #0)
> Currently when performing a search by user in Koha using first and last
> name, the system takes more than 22 minutes to execute the search

I find it doubtful that it took 22 minutes. Did you mean 22 seconds?

The web server would typically time out after 5 minutes, so I don't know how it possibly could have taken 22 minutes for an out-of-the-box installation.
Comment 8 Jonathan Cuéllar 2020-09-03 00:53:20 UTC
(In reply to David Cook from comment #7)
> (In reply to Jonathan Cuéllar from comment #0)
> > Currently when performing a search by user in Koha using first and last
> > name, the system takes more than 22 minutes to execute the search
> 
> I find it doubtful that it took 22 minutes. Did you mean 22 seconds?
> 
> The web server would typically time out after 5 minutes, so I don't know how
> it possibly could have taken 22 minutes for an out-of-the-box installation.

The timeout was extended, so it is 22 minutes
Comment 9 David Cook 2020-09-03 02:16:54 UTC
(In reply to Jonathan Cuéllar from comment #8)
> The timeout was extended, so it is 22 minutes

Thanks for clarifying. So it's not an out-of-the-box installation. On that note, does it take 22 minutes for the search, or does the search timeout after 22 minutes?
Comment 10 David Cook 2020-09-03 02:27:37 UTC
I can't replicate your issue using the attachment provided.

First, the attachment is invalid SQL. It looks like this is only a fragment of the actual SQL being run (plus it contains a number of syntax errors).

Second, when I run the corrected SQL fragment directly against the database, I get a response in about .0131 seconds. So I don't think it's the SQL. 

I think there's probably more going on with your data and your server than is made clear in this issue report. You may have a genuine issue, but it's not reproducible or even clear what it is at this point. 

As Owen suggested earlier, I think your issue qualifies as a support issue rather than a bug report. 

I'd say you probably need to look at getting a paid support provider or dig into the code yourself. 

Alternatively, you may try to reproduce your own issue, and break down the steps to make it clearer to us what might be happening. 

For instance, with a freshly installed Koha, does this happen after importing 50,000 borrowers (and making no other changes)? How are those borrowers imported? Can you share the dataset so that someone else more technical can try to reproduce the problem in a technical environment?
Comment 11 Jonathan Druart 2020-12-01 10:00:24 UTC
Closing this one. Please ask the mailing list (koha-devel) if the problem persists.
Comment 12 Alexander Borkowski 2021-12-03 09:34:03 UTC
*** Bug 28645 has been marked as a duplicate of this bug. ***