By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.27 |
CREATE TABLE `notification` (
`notif_id` BIGINT NOT NULL,
`name` VARCHAR(45) NULL,
`status` TINYINT NULL,
PRIMARY KEY (`notif_id`));
CREATE TABLE `user` (
`user_id` INT NOT NULL,
`name` VARCHAR(45) NULL,
PRIMARY KEY (`user_id`));
CREATE TABLE `notif_user` (
`notif_id` BIGINT NOT NULL,
`user_id` VARCHAR(45) NULL
);
CREATE TABLE `user_group` (
`group_id` BIGINT NOT NULL,
`name` VARCHAR(45) NULL
);
CREATE TABLE `notif_group` (
`notif_id` BIGINT NOT NULL,
`group_id` VARCHAR(45) NULL
);
insert into notification values (1,'Notif1',0);
insert into notification values (2,'Notif2',0);
insert into notification values (3,'Notif3',1);
insert into notification values (4,'Notif4',0);
insert into notification values (5,'Notif5',0);
insert into user values (1,'First');
insert into user values (2,'Second');
insert into user_group values (1,'One');
insert into user_group values (2,'Two');
insert into notif_user values (1,1);
insert into notif_user values (1,2);
insert into notif_user values (2,1);
insert into notif_user values (3,2);
insert into notif_user values (4,1);
insert into notif_user values (4,2);
insert into notif_group values (1,1);
insert into notif_group values (1,2);
insert into notif_group values (2,1);
insert into notif_group values (3,2);
insert into notif_group values (5,1);
explain analyze
select * from notification where status = 0 and (
notif_id in (select notif_id from notif_user where user_id = 1) OR
notif_id in (select notif_id from notif_group where group_id = 1))
| EXPLAIN |
|---|
| -> Filter: ((notification.`status` = 0) and (<in_optimizer>(notification.notif_id,notification.notif_id in (select #2)) or <in_optimizer>(notification.notif_id,notification.notif_id in (select #3)))) (cost=0.75 rows=1) (actual time=0.064..0.101 rows=4 loops=1) -> Table scan on notification (cost=0.75 rows=5) (actual time=0.017..0.020 rows=5 loops=1) -> Select #2 (subquery in condition; run only once) -> Filter: ((notification.notif_id = `<materialized_subquery>`.notif_id)) (cost=0.95..0.95 rows=1) (actual time=0.009..0.009 rows=1 loops=5) -> Limit: 1 row(s) (actual time=0.008..0.008 rows=1 loops=5) -> Index lookup on <materialized_subquery> using <auto_distinct_key> (notif_id=notification.notif_id) (actual time=0.001..0.001 rows=1 loops=5) -> Materialize with deduplication (cost=0.95..0.95 rows=1) (actual time=0.040..0.040 rows=3 loops=1) -> Filter: (notif_user.user_id = 1) (cost=0.85 rows=1) (actual time=0.008..0.019 rows=3 loops=1) -> Table scan on notif_user (cost=0.85 rows=6) (actual time=0.005..0.014 rows=6 loops=1) -> Select #3 (subquery in condition; run only once) -> Filter: ((notification.notif_id = `<materialized_subquery>`.notif_id)) (cost=0.85..0.85 rows=1) (actual time=0.014..0.014 rows=0 loops=2) -> Limit: 1 row(s) (actual time=0.013..0.013 rows=0 loops=2) -> Index lookup on <materialized_subquery> using <auto_distinct_key> (notif_id=notification.notif_id) (actual time=0.001..0.001 rows=0 loops=2) -> Materialize with deduplication (cost=0.85..0.85 rows=1) (actual time=0.026..0.026 rows=3 loops=1) -> Filter: (notif_group.group_id = 1) (cost=0.75 rows=1) (actual time=0.007..0.015 rows=3 loops=1) -> Table scan on notif_group (cost=0.75 rows=5) (actual time=0.006..0.013 rows=5 loops=1) |
explain analyze
select * from notification where status = 0 and (
notif_id in (
select notif_id from notif_user where user_id = 1 union all
select notif_id from notif_group where group_id = 1)
);
| EXPLAIN |
|---|
| -> Filter: ((notification.`status` = 0) and <in_optimizer>(notification.notif_id,<exists>(select #2))) (cost=0.75 rows=1) (actual time=0.026..0.067 rows=4 loops=1) -> Table scan on notification (cost=0.75 rows=5) (actual time=0.011..0.014 rows=5 loops=1) -> Select #2 (subquery in condition; dependent) -> Limit: 1 row(s) (actual time=0.010..0.010 rows=1 loops=4) -> Append (actual time=0.010..0.010 rows=1 loops=4) -> Stream results (cost=0.85 rows=1) (actual time=0.008..0.008 rows=1 loops=4) -> Limit: 1 row(s) (cost=0.85 rows=1) (actual time=0.007..0.007 rows=1 loops=4) -> Filter: ((notif_user.user_id = 1) and (<cache>(notification.notif_id) = notif_user.notif_id)) (cost=0.85 rows=1) (actual time=0.007..0.007 rows=1 loops=4) -> Table scan on notif_user (cost=0.85 rows=6) (actual time=0.003..0.005 rows=4 loops=4) -> Stream results (cost=0.75 rows=1) (actual time=0.008..0.008 rows=1 loops=1) -> Limit: 1 row(s) (cost=0.75 rows=1) (actual time=0.007..0.007 rows=1 loops=1) -> Filter: ((notif_group.group_id = 1) and (<cache>(notification.notif_id) = notif_group.notif_id)) (cost=0.75 rows=1) (actual time=0.007..0.007 rows=1 loops=1) -> Table scan on notif_group (cost=0.75 rows=5) (actual time=0.002..0.005 rows=5 loops=1) |
explain analyze
select * from notification n where status = 0 and
exists (
select * from notif_user nu where nu.user_id = 1 and nu.notif_id = n.notif_id union all
select * from notif_group ng where ng.group_id = 1 and ng.notif_id = n.notif_id
);
| EXPLAIN |
|---|
| -> Filter: ((n.`status` = 0) and exists(select #2)) (cost=0.75 rows=1) (actual time=0.022..0.059 rows=4 loops=1) -> Table scan on n (cost=0.75 rows=5) (actual time=0.011..0.014 rows=5 loops=1) -> Select #2 (subquery in condition; dependent) -> Limit: 1 row(s) (actual time=0.009..0.009 rows=1 loops=4) -> Append (actual time=0.009..0.009 rows=1 loops=4) -> Stream results (cost=0.85 rows=1) (actual time=0.007..0.007 rows=1 loops=4) -> Filter: ((nu.user_id = 1) and (nu.notif_id = n.notif_id)) (cost=0.85 rows=1) (actual time=0.006..0.006 rows=1 loops=4) -> Table scan on nu (cost=0.85 rows=6) (actual time=0.003..0.005 rows=4 loops=4) -> Stream results (cost=0.75 rows=1) (actual time=0.007..0.007 rows=1 loops=1) -> Filter: ((ng.group_id = 1) and (ng.notif_id = n.notif_id)) (cost=0.75 rows=1) (actual time=0.007..0.007 rows=1 loops=1) -> Table scan on ng (cost=0.75 rows=5) (actual time=0.002..0.005 rows=5 loops=1) |
explain analyze
select * from notification n where status = 0 and
(
exists (select * from notif_user nu where nu.user_id = 1 and nu.notif_id = n.notif_id )
or exists(select * from notif_group ng where ng.group_id = 1 and ng.notif_id = n.notif_id)
)
| EXPLAIN |
|---|
| -> Filter: ((n.`status` = 0) and (exists(select #2) or exists(select #3))) (cost=0.75 rows=1) (actual time=0.019..0.054 rows=4 loops=1) -> Table scan on n (cost=0.75 rows=5) (actual time=0.011..0.013 rows=5 loops=1) -> Select #2 (subquery in condition; dependent) -> Limit: 1 row(s) (cost=0.85 rows=1) (actual time=0.006..0.006 rows=1 loops=4) -> Filter: ((nu.user_id = 1) and (nu.notif_id = n.notif_id)) (cost=0.85 rows=1) (actual time=0.006..0.006 rows=1 loops=4) -> Table scan on nu (cost=0.85 rows=6) (actual time=0.002..0.005 rows=4 loops=4) -> Select #3 (subquery in condition; dependent) -> Limit: 1 row(s) (cost=0.75 rows=1) (actual time=0.007..0.007 rows=1 loops=1) -> Filter: ((ng.group_id = 1) and (ng.notif_id = n.notif_id)) (cost=0.75 rows=1) (actual time=0.007..0.007 rows=1 loops=1) -> Table scan on ng (cost=0.75 rows=5) (actual time=0.002..0.005 rows=5 loops=1) |