[Koha-bugs] [Bug 27584] Improve OAI-PMH provider performance

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

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

David Cook <dcook at prosentient.com.au> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |dcook at prosentient.com.au

--- Comment #5 from David Cook <dcook at prosentient.com.au> ---
Curious to see what you come up with here.

--

Here's a little look at doing a UNION on nearly 600,000 biblio and
deletedbiblio entries. 

EXPLAIN select * from (select biblionumber from deletedbiblio UNION select
biblionumber from biblio) u limit 1;
+------+--------------+---------------+-------+---------------+----------+---------+------+--------+-------------+
| id   | select_type  | table         | type  | possible_keys | key      |
key_len | ref  | rows   | Extra       |
+------+--------------+---------------+-------+---------------+----------+---------+------+--------+-------------+
|    1 | PRIMARY      | <derived2>    | ALL   | NULL          | NULL     | NULL
   | NULL | 578038 |             |
|    2 | DERIVED      | deletedbiblio | index | NULL          | blbnoidx | 4   
   | NULL |   4361 | Using index |
|    3 | UNION        | biblio        | index | NULL          | blbnoidx | 4   
   | NULL | 573677 | Using index |
| NULL | UNION RESULT | <union2,3>    | ALL   | NULL          | NULL     | NULL
   | NULL |   NULL |             |
+------+--------------+---------------+-------+---------------+----------+---------+------+--------+-------------+
4 rows in set (0.00 sec)

select * from (select biblionumber from deletedbiblio UNION select biblionumber
from biblio) u limit 1;
+--------------+
| biblionumber |
+--------------+
|            3 |
+--------------+
1 row in set (13.39 sec)

OR

select * from (select biblionumber from deletedbiblio UNION select biblionumber
from biblio) u limit 0,50;
50 rows in set (12.60 sec)

select biblionumber, (select metadata from biblio_metadata where biblionumber =
u.biblionumber) from (select biblionumber from deletedbiblio UNION select
biblionumber from biblio) u limit 0,50;
50 rows in set (13.58 sec)
--

Of course, there's no index on `timestamp`, so we can't sort that list.
However, perhaps if we added a composite `timestamp,biblionumber` index to the
biblio and deletedbiblio tables... 

Still... 13 seconds isn't brilliant.

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