blob: c82d27e481cc4898d59e1c5d753e9bccf0b8fd6d (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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;
```
|
