MariaDB Table Elimination

-- Test run on MariaDB 5.2.5 (2011 March 26)

DROP TABLE IF EXISTS AN_AT1_Anchor_Attribute1;
DROP TABLE IF EXISTS AN_AT2_Anchor_Attribute2;
DROP TABLE IF EXISTS AN_Anchor;

CREATE TABLE AN_Anchor (
    AN_ID INT NOT NULL,
    CONSTRAINT pk_AN_Anchor PRIMARY KEY(AN_ID)
) ENGINE=InnoDB;

CREATE TABLE AN_AT1_Anchor_Attribute1 (
    AN_ID INT NOT NULL,
    AN_AT1_Anchor_Attribute1 CHAR(10) NOT NULL,
    CONSTRAINT pk_AN_AT1_Anchor_Attribute1 PRIMARY KEY(AN_ID),
    CONSTRAINT fk_AN_AT1_Anchor_Attribute1 FOREIGN KEY(AN_ID) REFERENCES AN_Anchor(AN_ID) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE AN_AT2_Anchor_Attribute2 (
    AN_ID INT NOT NULL,
    AN_AT2_ValidFrom DATE NOT NULL,
    AN_AT2_Anchor_Attribute2 CHAR(10) NOT NULL,
    CONSTRAINT pk_AN_AT2_Anchor_Attribute2 PRIMARY KEY(AN_ID, AN_AT2_ValidFrom),
    CONSTRAINT fk_AN_AT2_Anchor_Attribute2 FOREIGN KEY(AN_ID) REFERENCES AN_Anchor(AN_ID) ON DELETE CASCADE
);

DROP VIEW IF EXISTS lAN_Anchor;

CREATE VIEW lAN_Anchor
AS
SELECT
    AN.AN_ID,
    AN_AT1.AN_AT1_Anchor_Attribute1,
    AN_AT2.AN_AT2_Anchor_Attribute2,
    AN_AT2.AN_AT2_ValidFrom
FROM
    AN_Anchor AN
LEFT JOIN
    AN_AT1_Anchor_Attribute1 AN_AT1
ON
    AN_AT1.AN_ID = AN.AN_ID
LEFT JOIN
    AN_AT2_Anchor_Attribute2 AN_AT2
ON
    AN_AT2.AN_ID = AN.AN_ID
AND
    AN_AT2.AN_AT2_ValidFrom = (
        SELECT
            MAX(sub.AN_AT2_ValidFrom)
        FROM
            AN_AT2_Anchor_Attribute2 sub
        WHERE
            sub.AN_ID = AN.AN_ID
    );

INSERT INTO AN_Anchor (
    AN_ID
)
VALUES
(1),
(2),
(3);

ANALYZE TABLE AN_Anchor;

INSERT INTO AN_AT1_Anchor_Attribute1 (
    AN_ID,
    AN_AT1_Anchor_Attribute1
)
VALUES
(1, 'Green'),
(2, 'Blue'),
(3, 'Red');

ANALYZE TABLE AN_AT1_Anchor_Attribute1;

INSERT INTO AN_AT2_Anchor_Attribute2 (
    AN_ID,
    AN_AT2_Anchor_Attribute2,
    AN_AT2_ValidFrom
)
VALUES
(1, 'Car', '2008-01-01'),
(1, 'Bus', '2008-12-12'),
(2, 'Train', '2008-01-01'),
(2, 'Car', '2008-12-12'),
(3, 'Train', '2008-01-01'),
(3, 'Bus', '2008-12-12');

ANALYZE TABLE AN_AT2_Anchor_Attribute2;

SELECT * FROM lAN_Anchor;

/*
+-------+-----------------------------+-----------------------------+----------------+
| AN_ID | ANAT1_AnchorAttribute1Value | ANAT2_AnchorAttribute2Value | ANAT2_FromDate |
+-------+-----------------------------+-----------------------------+----------------+
|     1 | Green                       | Bus                         | 2008-12-12     |
|     2 | Blue                        | Car                         | 2008-12-12     |
|     3 | Red                         | Bus                         | 2008-12-12     |
+-------+-----------------------------+-----------------------------+----------------+
*/

EXPLAIN extended SELECT * FROM lAN_Anchor;
SHOW warnings;

/* No table can be eliminated
+----+--------------------+--------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
| id | select_type        | table  | type   | possible_keys | key     | key_len | ref                | rows | filtered | Extra       |
+----+--------------------+--------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
|  1 | PRIMARY            | AN     | index  | NULL          | PRIMARY | 4       | NULL               |    3 |   100.00 | Using index |
|  1 | PRIMARY            | AN_AT1 | eq_ref | PRIMARY       | PRIMARY | 4       | test.AN.AN_ID      |    1 |   100.00 |             |
|  1 | PRIMARY            | AN_AT2 | eq_ref | PRIMARY       | PRIMARY | 7       | test.AN.AN_ID,func |    1 |   100.00 |             |
|  3 | DEPENDENT SUBQUERY | sub    | ref    | PRIMARY       | PRIMARY | 4       | test.AN.AN_ID      |    1 |   100.00 | Using index |
+----+--------------------+--------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
*/

SELECT AN_AT1_Anchor_Attribute1 FROM lAN_Anchor;

/*
+-----------------------------+
| ANAT1_AnchorAttribute1Value |
+-----------------------------+
| Green                       |
| Blue                        |
| Red                         |
+-----------------------------+
*/

EXPLAIN extended SELECT AN_AT1_Anchor_Attribute1 FROM lAN_Anchor;
SHOW warnings;

/* Table elimination in effect
+----+-------------+--------+--------+---------------+---------+---------+---------------+------+----------+-------------+
| id | select_type | table  | type   | possible_keys | key     | key_len | ref           | rows | filtered | Extra       |
+----+-------------+--------+--------+---------------+---------+---------+---------------+------+----------+-------------+
|  1 | PRIMARY     | AN     | index  | NULL          | PRIMARY | 4       | NULL          |    3 |   100.00 | Using index |
|  1 | PRIMARY     | AN_AT1 | eq_ref | PRIMARY       | PRIMARY | 4       | test.AN.AN_ID |    1 |   100.00 |             |
+----+-------------+--------+--------+---------------+---------+---------+---------------+------+----------+-------------+
*/

SELECT AN_AT1_Anchor_Attribute1 FROM lAN_Anchor WHERE AN_AT1_Anchor_Attribute1 IS NOT NULL;

/*
+--------------------------+
| AN_AT1_Anchor_Attribute1 |
+--------------------------+
| Green                    |
| Blue                     |
| Red                      |
+--------------------------+
*/

EXPLAIN extended SELECT AN_AT1_Anchor_Attribute1 FROM lAN_Anchor WHERE AN_AT1_Anchor_Attribute1 IS NOT NULL;
SHOW warnings;

/* Less than optimal plan: the table AN_Anchor can also be eliminated thanks to the declared foreign key
+----+-------------+--------+-------+---------------+---------+---------+------+------+----------+--------------------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+--------+-------+---------------+---------+---------+------+------+----------+--------------------------------+
|  1 | PRIMARY     | AN     | index | PRIMARY       | PRIMARY | 4       | NULL |    3 |   100.00 | Using index                    |
|  1 | PRIMARY     | AN_AT1 | ALL   | PRIMARY       | NULL    | NULL    | NULL |    3 |   100.00 | Using where; Using join buffer |
+----+-------------+--------+-------+---------------+---------+---------+------+------+----------+--------------------------------+
*/

SELECT AN_AT2_Anchor_Attribute2 FROM lAN_Anchor;

/*
+-----------------------------+
| ANAT2_AnchorAttribute2Value |
+-----------------------------+
| Bus                         |
| Car                         |
| Bus                         |
+-----------------------------+
*/

EXPLAIN extended SELECT AN_AT2_Anchor_Attribute2 FROM lAN_Anchor;
SHOW warnings;

/* Table elimination in effect
+----+--------------------+--------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
| id | select_type        | table  | type   | possible_keys | key     | key_len | ref                | rows | filtered | Extra       |
+----+--------------------+--------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
|  1 | PRIMARY            | AN     | index  | NULL          | PRIMARY | 4       | NULL               |    3 |   100.00 | Using index |
|  1 | PRIMARY            | AN_AT2 | eq_ref | PRIMARY       | PRIMARY | 7       | test.AN.AN_ID,func |    1 |   100.00 |             |
|  3 | DEPENDENT SUBQUERY | sub    | ref    | PRIMARY       | PRIMARY | 4       | test.AN.AN_ID      |    1 |   100.00 | Using index |
+----+--------------------+--------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
*/

SELECT AN_AT2_Anchor_Attribute2 FROM lAN_Anchor WHERE AN_AT2_Anchor_Attribute2 IS NOT NULL;

/*
+--------------------------+
| AN_AT2_Anchor_Attribute2 |
+--------------------------+
| Bus                      |
| Car                      |
| Bus                      |
+--------------------------+
*/

EXPLAIN extended SELECT AN_AT2_Anchor_Attribute2 FROM lAN_Anchor WHERE AN_AT2_Anchor_Attribute2 IS NOT NULL;
SHOW warnings;

/* Less than optimal plan: the table AN_Anchor can also be eliminated thanks to the declared foreign key
+----+--------------------+--------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
| id | select_type        | table  | type   | possible_keys | key     | key_len | ref                | rows | filtered | Extra       |
+----+--------------------+--------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
|  1 | PRIMARY            | AN     | index  | PRIMARY       | PRIMARY | 4       | NULL               |    3 |   100.00 | Using index |
|  1 | PRIMARY            | AN_AT2 | eq_ref | PRIMARY       | PRIMARY | 7       | test.AN.AN_ID,func |    1 |   100.00 | Using where |
|  3 | DEPENDENT SUBQUERY | sub    | ref    | PRIMARY       | PRIMARY | 4       | test.AN.AN_ID      |    1 |   100.00 | Using index |
+----+--------------------+--------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
*/