[Koha-bugs] [Bug 31222] DBIC queries for batch mod can be very large

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

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

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

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

--- Comment #16 from David Cook <dcook at prosentient.com.au> ---
(In reply to Jonathan Druart from comment #8)
> IN limit is max_allowed_packet, which is 16M by default.

(In reply to Jonathan Druart from comment #9)
> Why do we want to reduce mysql query length exactly? You are not supposed to
> have mysql logging in production servers, you will face perf issues (but I
> guess you know that already).

While I don't think I've seen it in Koha, I have seen other systems throw MySQL
errors, because their SQL queries were too long. Of course "barcode in(?, ?,
?)" could still have that happen, but I suppose it's less likely than "barcode
= ? or barcode = ? or barcode = ?". 

--

I keep looking at https://mariadb.com/kb/en/in/, but it doesn't really make
sense outside their limited examples.

In the example 'SELECT 2 IN (0,3,5,7);' it makes sense that it would do a
binary search of that value list, since that would be the most efficient
operation. It would just need to do 1 search.

However, if it's "SELECT barcode WHERE barcode IN (1,2,3,4,5)", then it would
be extremely inefficient to binary search the value list, because it would have
to row scan the whole table for each "barcode" field and then binary search the
value list.

At a glance, it looks like both query styles actually have the same
performance:

analyze select * from items where barcode = '1' or barcode = '2' or barcode =
'3' or barcode = '4' or barcode = '5';

analyze select * from items where barcode in ('1','2','3','4','5');

Both queries do a range query using the itembarcodeidx. 

Now that idea of the database row scanning the whole table and then binary
searching the value list can still happen, if the number of rows in the
database is < the number of values in the value list. 

For example:
analyze select * from z3950servers where id in ('1','2','3','4','5');

That will do an ALL type query (ie table scan of every row).

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