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;