[Evergreen-dev] Optimize SQL query

[Tiffany Little]

Good morning,

I'm hoping someone who's more skilled with SQL can help me with this query
I'm working with.

My goal is to find copies where:

   - They are on a bib record that has parts
   - The parts can't be deleted
   - The copy does not have a part assigned
   - The copy is holdable, and in a shelving location/status that is
   holdable
   - Temporarily I'm also limiting it to bib records with a Dtst of "m"
   just to work with a smaller subset of records

I have the query I'm working with attached, but when I add EXPLAIN to it I
see that it's scanning indexes multiple times, so I'm getting lots of dupes
in my results. (Which is fine, I can dedupe, but still.)

Is there a better way to write this query, or has anyone already done
something similar I can crib from?

Thank you!
Tiffany

Tiffany Little, PINES Bibliographic Projects Manager

------------------------------

Georgia Public Library Service

2872 Woodcock Blvd, Suite 250 | Atlanta, GA 30341

(404) 235-7161 | tlittle at georgialibraries.org

Join our email list <http://georgialibraries.org/> for stories of Georgia
libraries making an impact in our communities.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://list.evergreen-ils.org/pipermail/evergreen-dev/attachments/20230831/8df8d622/attachment.htm>
-------------- next part --------------
select
acp.barcode,
bre.tcn_value,
mrd.bib_level as BLvl,
mrd.pub_status as Dtst,
mrd.date1 as Date1,
mrd.date2 as Date2,
acn.label as call_number,
aou.shortname as circ_lib,
acl.name as shelving_location,
acp.create_date,
acp.circulate,
acp.holdable,
acp.ref,
acp.opac_visible,
ccs.name as status
from asset.copy acp
join asset.call_number acn on acn.id=acp.call_number
join asset.copy_location acl on acl.id=acp.location
join biblio.record_entry bre on bre.id=acn.record
left join asset.copy_part_map acppm on acppm.target_copy=acp.id
join biblio.monograph_part bmp on bmp.record=bre.id
join actor.org_unit aou on aou.id=acp.circ_lib
join metabib.rec_descriptor mrd on mrd.record=bre.id
join config.copy_status ccs on ccs.id=acp.status
where acp.deleted='f' and acppm.id is null and bmp.id>0 and bmp.deleted='f' and acp.holdable='t' and ccs.holdable='t' and acl.holdable='t' and mrd.pub_status='m';