-- 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 |
+----+--------------------+-------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
*/