MySQL Table Elimination

-- Test run on MySQL 6.0.10 Alpha (2009-04-23)

DROP TABLE IF EXISTS AN_Anchor;

CREATE TABLE AN_Anchor (
    AN_ID INT NOT NULL,
    PRIMARY KEY(AN_ID)
);

DROP TABLE IF EXISTS ANAT1_AnchorAttribute1;

CREATE TABLE ANAT1_AnchorAttribute1 (
    AN_ID INT NOT NULL,
    ANAT1_AnchorAttribute1Value CHAR(10) NOT NULL,
    PRIMARY KEY(AN_ID)
);

DROP TABLE IF EXISTS ANAT2_AnchorAttribute2;

CREATE TABLE ANAT2_AnchorAttribute2 (
    AN_ID INT NOT NULL,
    ANAT2_AnchorAttribute2Value CHAR(10) NOT NULL,
    ANAT2_FromDate DATE NOT NULL,
    PRIMARY KEY(AN_ID, ANAT2_FromDate)
);

DROP VIEW IF EXISTS lAN_Anchor;

CREATE VIEW lAN_Anchor
AS
SELECT
    AN.AN_ID,
    ANAT1.ANAT1_AnchorAttribute1Value,
    ANAT2.ANAT2_AnchorAttribute2Value,
    ANAT2.ANAT2_FromDate
FROM
    AN_Anchor AN
LEFT JOIN
    ANAT1_AnchorAttribute1 ANAT1
ON
    ANAT1.AN_ID = AN.AN_ID
LEFT JOIN
    ANAT2_AnchorAttribute2 ANAT2
ON
    ANAT2.AN_ID = AN.AN_ID
AND
    ANAT2.ANAT2_FromDate = (
        SELECT
            MAX(sub.ANAT2_FromDate)
        FROM
            ANAT2_AnchorAttribute2 sub
        WHERE
            sub.AN_ID = AN.AN_ID
    );

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

ANALYZE TABLE AN_Anchor;

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

ANALYZE TABLE ANAT1_AnchorAttribute1;

INSERT INTO ANAT2_AnchorAttribute2 (
    AN_ID,
    ANAT2_AnchorAttribute2Value,
    ANAT2_FromDate
)
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 ANAT2_AnchorAttribute2;

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;

/*
+----+--------------------+-------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
| 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            | anat1 | eq_ref | PRIMARY       | PRIMARY | 4       | test.an.AN_ID      |    1 |   100.00 |             |
|  1 | PRIMARY            | anat2 | 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 |
+----+--------------------+-------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
*/

SELECT ANAT1_AnchorAttribute1Value FROM lAN_Anchor;

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

EXPLAIN extended SELECT ANAT1_AnchorAttribute1Value FROM lAN_Anchor;
SHOW warnings;

/* No table elimination present
+----+--------------------+-------+--------+---------------+---------+---------+--------------------+------+----------+--------------------------+
| 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            | anat1 | eq_ref | PRIMARY       | PRIMARY | 4       | test.an.AN_ID      |    1 |   100.00 |                          |
|  1 | PRIMARY            | anat2 | eq_ref | PRIMARY       | PRIMARY | 7       | test.an.AN_ID,func |    1 |   100.00 | Using where; Using index |
|  3 | DEPENDENT SUBQUERY | sub   | ref    | PRIMARY       | PRIMARY | 4       | test.an.AN_ID      |    1 |   100.00 | Using index              |
+----+--------------------+-------+--------+---------------+---------+---------+--------------------+------+----------+--------------------------+
*/

SELECT ANAT2_AnchorAttribute2Value FROM lAN_Anchor;

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

EXPLAIN extended SELECT ANAT2_AnchorAttribute2Value FROM lAN_Anchor;
SHOW warnings;

/* No table elimination present
+----+--------------------+-------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
| 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            | anat1 | eq_ref | PRIMARY       | PRIMARY | 4       | test.an.AN_ID      |    1 |   100.00 | Using index |
|  1 | PRIMARY            | anat2 | 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 |
+----+--------------------+-------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
*/