SELECT from VIEW using joins uses indexes of not necessary left joined tables

[bbkr]

Very simple example

First create trivial schema

CREATE TABLE foo(
foo_id int unsigned not null auto_increment,
PRIMARY KEY(foo_id)
) Engine=InnoDB;

CREATE TABLE bar(
bar_id int unsigned not null auto_increment,
PRIMARY KEY(bar_id)
) Engine=InnoDB;

CREATE TABLE base(
foo_id int unsigned not null,
bar_id int unsigned not null,
FOREIGN KEY (foo_id) REFERENCES foo(foo_id),
FOREIGN KEY (bar_id) REFERENCES bar(bar_id)
) Engine=InnoDB;

CREATE VIEW view_base
AS SELECT foo.foo_id AS x, bar.bar_id AS y
FROM base
LEFT JOIN foo USING(foo_id)
LEFT JOIN bar USING(bar_id);

Feed some data

INSERT INTO foo(foo_id) VALUES (1), (2), (3), (4);
INSERT INTO bar(bar_id) VALUES (1), (2), (3), (4);
INSERT INTO base(foo_id, bar_id) VALUES (1,1), (2,2), (3,3), (4,4);

And here comes the weird part



mysql> EXPLAIN SELECT x FROM view_base WHERE x = 1;
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+------+-------------+
| 1 | SIMPLE | foo | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
| 1 | SIMPLE | base | ref | foo_id | foo_id | 4 | const | 1 | |
| 1 | SIMPLE | bar | eq_ref | PRIMARY | PRIMARY | 4 | getresponse_mig.base.bar_id | 1 | Using index |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+------+-------------+
3 rows in set (0.00 sec)

Result can be returned WITHOUT touching "bar" table - no fields from it are selected and it is left-joined and it lacks field conditions (besides USING one).

Still - optimizer cannot figure that out!

Tnis problem gets nasty on data warehouses, where you

CREATE VIEW v
AS SELECT fact1.total, * FROM fact1 LEFT JOIN dim1 LEFT JOIN dim2, LEFT JOIN dim3, LEFT JOIN dim4

and then ask a query

SELECT SUM(total) FROM v WHERE dim3_id = 1234

You can calculate it without using dim1, dim2, dim4 indexes, but MySQL performs joins anyway.

Is there any workaround to make MySQL behave smarter on unused LEFT JOINs?