From f9034731fa234a4b5efa1d2d6147fe1e798b6d36 Mon Sep 17 00:00:00 2001 From: Jasper Ras Date: Fri, 4 Apr 2025 14:31:53 +0200 Subject: vault backup: 2025-04-04 14:31:53 --- ...iting subselect into just an additional join.md | 32 ++++++++++++++++++++++ 1 file changed, 32 insertions(+) create mode 100644 Rewriting subselect into just an additional join.md (limited to 'Rewriting subselect into just an additional join.md') diff --git a/Rewriting subselect into just an additional join.md b/Rewriting subselect into just an additional join.md new file mode 100644 index 0000000..c82d27e --- /dev/null +++ b/Rewriting subselect into just an additional join.md @@ -0,0 +1,32 @@ +--- +tags: + - mysql +--- +Optimizing a query with subselects into a "flat" query with just additional joins. This query finds rows that are missing for instances. + +``` +select * +from instance_system_metadata ism1 +where ism1.instance_uuid in ( + select i.uuid + from instances i + left join instance_system_metadata ism + on ism.instance_uuid = i.uuid and ism.key = "image_os_admin_user" + where i.deleted_at is null and ism.id is null +) +and ism1.key = "image_hw_qemu_guest_agent"; +``` + +Can be rewritten more nicely with an additional join +``` +select i.uuid +from instances i +left join instance_system_metadata ism_agent + on ism_agent.instance_uuid = i.uuid + and ism_agent.key = "image_hw_qemu_guest_agent" +left join instance_system_metadata ism_user + on ism_user.instance_uuid = i.uuid + and ism_user.key = "image_os_admin_user" +where i.deleted is null + and ism_user.key is null; +``` \ No newline at end of file -- cgit v1.2.3