MySQL Forums
Forum List  »  Optimizer & Parser

Re: Help with index in a view. Index not used. MySql Bug?
Posted by: Emilio Nicolás
Date: April 06, 2009 04:20PM

When you create a view, you can specify the algorithm MySQL should use to obtain the data: http://dev.mysql.com/doc/refman/5.0/en/create-view.html

The problem is that merge algorithm cannot be used with unions subquieries as it is said in the reference manual.

I have created a new view

CREATE OR REPLACE VIEW vfriends AS
select straight_join u.id as idUser, uFriend.id as idFriend,
u uFriend.name, uFriend.lastname,
from users u, friends f, users uFriend
where (u.id=f.idUser and f.idFriend=uFriend.id) or
(u.id=f.idFriend and f.idUser=uFriend.id)

But indexes still dont work as i expected when i filter by idUser in:
explain Select * from vfriends where idUser=6;

The table uFriend is scanned fully (ALL). :-( Any ideas?

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Help with index in a view. Index not used. MySql Bug?
3331
April 06, 2009 04:20PM


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.