Skip navigation links

MySQL Forums :: Views :: Any way to optimize view


Advanced Search

Any way to optimize view
Posted by: Olivier Bourdon ()
Date: January 21, 2013 08:55AM

Views are really great however there are somecase where I find them very non performant and was wonderning if there was a way to cope with this.

Let's say I have a view which is a UNION of 3 select queries with INER JOINs and which look alike but do not work on the same tables.

Running explain on each and every of the 3 select with a WHERE clause containing
item.name like '%pattern%' shows perfect usage of indexes and each select return in
less than 1/10th of a second

However the view is defined with
CREATE VIEW MyInfos AS
select item.name .......
from ..... innner join .....
union
select item.name ......
from ..... innner join .....
union
select item.name ......
from ..... innner join .....

and of course running
select * from MyInfos where item.name like '%pattern%'
takes around 15s because the item.name like '%pattern%'
can not be used with each select to make proper uses of indexes

Is there a way to solve this or should I mode to procedure instead of view for this
very particular case ?

Options: ReplyQuote


Subject Views Written By Posted
Any way to optimize view 1278 Olivier Bourdon 01/21/2013 08:55AM


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.