[Koha-bugs] [Bug 6946] New: Import Batches _update_batch_record_counts has inefficient SQL

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

http://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=6946

             Bug #: 6946
           Summary: Import Batches _update_batch_record_counts has
                    inefficient SQL
    Classification: Unclassified
 Change sponsored?: ---
           Product: Koha
           Version: Rel_3_8
          Platform: All
        OS/Version: All
            Status: NEW
          Severity: minor
          Priority: P5
         Component: Architecture, internals, and plumbing
        AssignedTo: ian.walls at bywatersolutions.com
        ReportedBy: ian.walls at bywatersolutions.com
         QAContact: koha-bugs at lists.koha-community.org


The queries to update the number of biblios and the number of items for any
given import batch are much slower than they need to be.

UPDATE import_batches 
SET num_biblios = (SELECT COUNT(*)
                   FROM import_records
                   WHERE import_batch_id = import_batches.import_batch_id
                   AND record_type = 'biblio')
WHERE import_batch_id = ?

UPDATE import_batches 
SET num_items = (SELECT COUNT(*)
                 FROM import_records
                 JOIN import_items USING (import_record_id)
                 WHERE import_batch_id = import_batches.import_batch_id
                 AND record_type = 'biblio')
WHERE import_batch_id = ?")

The "AND record_type = 'biblio'" lines are unnecesary; every record within the
same import_batch_id will have the same record_type, and more importantly,
'biblio' is currently the only supported type.

Removing the line changes the return time of the subquery from over 2 seconds
down to 0.01 seconds (on my machine with 34944 items in a batch).  I think this
is going to be faster than adding an index on record_type.

-- 
Configure bugmail: http://bugs.koha-community.org/bugzilla3/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the QA Contact for the bug.