1 files changed, 32 insertions, 0 deletions
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