Bug 15406

Summary: Very SLOW OAI server performance
Product: Koha Reporter: grharry <dhd.koha>
Component: Web servicesAssignee: Bugs List <koha-bugs>
Status: RESOLVED DUPLICATE QA Contact: Testopia <testopia>
Severity: enhancement    
Priority: P5 - low CC: dhd.koha, f.demians, jonathan.druart, m.de.rooy, olli-antti.kivilahti, tomascohen
Version: 3.20   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Circulation function:
Bug Depends on: 15108    
Bug Blocks: 15342    

Description grharry 2015-12-22 11:07:08 UTC 
I noticed a very slow performance on oai server koha version 3.22 ! 

In particular the query 

(
SELECT biblioitems.biblionumber, biblioitems.timestamp, marcxml 
FROM biblioitems JOIN oai_sets_biblios ON biblioitems.biblionumber = oai_sets_biblios.biblionumber  
WHERE timestamp >= '1970-01-01 00:00:00' AND timestamp <= '2015-12-22 23:59:59'  AND oai_sets_biblios.set_id = '4' 
)       
UNION 
(
SELECT deletedbiblio.biblionumber, null as marcxml, timestamp 
FROM deletedbiblio JOIN oai_sets_biblios ON deletedbiblio.biblionumber = oai_sets_biblios.biblionumber  
WHERE DATE(timestamp) >= '1970-01-01T00:00:00Z' AND DATE(timestamp) <= '2015-12-22T23:59:59Z'  AND oai_sets_biblios.set_id = '4' 
) 
ORDER BY biblionumber LIMIT 51 OFFSET 0

As constructed by oai.pl 

Requires about a minute to get results for each fetch! 

So according to my rough calculations 
a 500K marcs catalogue would require about 6 days to be harvested!

Harry!
Comment 1 Frédéric Demians 2016-02-02 07:44:20 UTC 
Bug 15106 contains the solution. Especially, an index must be added to biblioitems/deletebiblioitems timestamp field:

KEY `timestamp_bibno` (`timestamp`, `biblionumber`)

You can confirm that this solution works for you by altering your DB schema:

ALTER TABLE biblioitems ADD KEY `timestamp_bibno` (`timestamp`, `biblionumber`);
ALTER TABLE deletedbiblioitems ADD KEY `timestamp_bibno` (`timestamp`, `biblionumber`)
Comment 2 Jonathan Druart 2016-02-02 09:17:53 UTC 
(In reply to Frédéric Demians from comment #1)
> Bug 15106 contains the solution.

Must be a typo, bug 15106 is about batch patron modification.
Comment 3 Frédéric Demians 2016-02-02 10:20:26 UTC 
Yes, sorry. It's bug 15108.
Comment 4 Marcel de Rooy 2016-12-09 10:35:10 UTC 
Frederic,
Can you confirm that 15108 would actually resolve this one?
Comment 5 Frédéric Demians 2016-12-09 12:08:00 UTC 
(In reply to Marcel de Rooy from comment #4)
> Frederic,
> Can you confirm that 15108 would actually resolve this one?

Yes. Even I haven't re-check recently.
Comment 6 Olli-Antti Kivilahti 2017-03-14 13:58:32 UTC 
Then please go sign it off!
Comment 7 Tomás Cohen Arazi (tcohen) 2017-05-05 14:51:54 UTC 

*** This bug has been marked as a duplicate of bug 15108 ***