MySQL Forums
Forum List  »  Knowledge Base

SELECT from VIEW using joins uses indexes of not necessary left joined tables
Posted by: bbkr
Date: January 09, 2009 10:48AM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
SELECT from VIEW using joins uses indexes of not necessary left joined tables
25764
January 09, 2009 10:48AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.