DROP VIEW IF EXISTS view_parent;
DROP VIEW IF EXISTS view_child2;
DROP VIEW IF EXISTS view_child2b;
DROP TABLE IF EXISTS child1;
DROP TABLE IF EXISTS child2;
DROP TABLE IF EXISTS parent;
DROP TABLE IF EXISTS common;

CREATE TABLE common (
common_id INT UNSIGNED NOT NULL PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE parent (
parent_id INT UNSIGNED NOT NULL,
common_id INT UNSIGNED NOT NULL,
PRIMARY KEY (parent_id, common_id),
FOREIGN KEY (common_id) REFERENCES common(common_id)
) ENGINE=InnoDB;

CREATE TABLE child1 (
child_id INT UNSIGNED NOT NULL PRIMARY KEY,
parent_id INT UNSIGNED NOT NULL,
common_id INT UNSIGNED NOT NULL,
FOREIGN KEY (common_id) REFERENCES common(common_id)
) ENGINE=InnoDB;

CREATE TABLE child2 (
child_id INT UNSIGNED NOT NULL PRIMARY KEY,
parent_id INT UNSIGNED NOT NULL,
common_id INT UNSIGNED NOT NULL,
FOREIGN KEY (common_id) REFERENCES common(common_id)
) ENGINE=InnoDB;

CREATE VIEW view_parent AS
SELECT parent.* FROM parent;

CREATE ALGORITHM=MERGE VIEW view_child2 AS
SELECT child2.*
FROM child2
LEFT JOIN view_parent USING(parent_id)
WHERE child2.common_id = view_parent.common_id;

CREATE ALGORITHM=TEMPTABLE VIEW view_child2b AS
SELECT child2.*
FROM child2
LEFT JOIN view_parent USING(parent_id)
WHERE child2.common_id = view_parent.common_id;

INSERT INTO common SET common_id = 1;
INSERT INTO child1 SET common_id = 1, parent_id = 1, child_id = 1;

explain extended SELECT
1
FROM child2
LEFT JOIN child1 ON child1.child_id = child2.child_id AND child1.common_id = child2.common_id
LEFT JOIN parent ON parent.parent_id = child2.parent_id AND parent.common_id = child2.common_id
WHERE
( child1.child_id IS NOT NULL ) AND ( child2.child_id = 1 )\G
show warnings\G

INSERT INTO parent SET parent_id = 1, common_id = 1;
INSERT INTO child2 SET child_id = 1, parent_id = 1, common_id = 1;

explain extended SELECT count(*) FROM child2 WHERE child_id=1\G
show warnings\G
SELECT count(*) FROM child2 WHERE child_id=1;

explain extended SELECT count(*) FROM view_child2b WHERE child_id=1\G
show warnings\G
SELECT count(*) FROM view_child2b WHERE child_id=1;

explain extended SELECT count(*) FROM view_child2 WHERE child_id=1\G
show warnings\G
SELECT count(*) FROM view_child2 WHERE child_id=1;

flush tables;

explain extended SELECT count(*) FROM view_child2 WHERE child_id=1\G
show warnings\G
SELECT count(*) FROM view_child2 WHERE child_id=1;