MariaDB 10.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE passengers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100));
INSERT INTO passengers (name) VALUES ('Иван'), ('Петр'), ('Сидор');

CREATE TABLE flights(pass_id INT, name VARCHAR(10));
CREATE INDEX i1 ON flights (pass_id);
INSERT INTO flights (pass_id, name) VALUES (1, 'A'), (2, 'A'), (1, 'B'), (1, 'C');
SELECT p.id, p.name, 
    (SELECT COUNT(*) FROM flights f WHERE f.pass_id = p.id) AS cnt
FROM passengers p;

EXPLAIN SELECT p.id, p.name, 
    (SELECT COUNT(*) FROM flights f WHERE f.pass_id = p.id) AS cnt
FROM passengers p;



id
name
cnt


1
Иван
3

2
Петр
1

3
Сидор
0




id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra


1
PRIMARY
p
ALL
null
null
null
null
3


2
DEPENDENT SUBQUERY
f
ref
i1
i1
5
db_1842861616.p.id
1
Using index


SELECT p.id, p.name, COUNT(f.pass_id) AS cnt 
    FROM passengers p 
    LEFT JOIN flights f ON f.pass_id = p.id
    GROUP BY p.id;

EXPLAIN SELECT p.id, p.name, COUNT(f.pass_id) AS cnt 
    FROM passengers p 
    LEFT JOIN flights f ON f.pass_id = p.id
    GROUP BY p.id;


id
name
cnt


1
Иван
3

2
Петр
1

3
Сидор
0




id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra


1
SIMPLE
p
index
null
PRIMARY
4
null
3


1
SIMPLE
f
ref
i1
i1
5
db_1842861616.p.id
1
Using index

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	p	ALL	null	null	null	null	3
2	DEPENDENT SUBQUERY	f	ref	i1	i1	5	db_1842861616.p.id	1	Using index

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	p	index	null	PRIMARY	4	null	3
1	SIMPLE	f	ref	i1	i1	5	db_1842861616.p.id	1	Using index