MySQL Forums
Forum List  »  Views

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 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
select .......
from ..... innner join .....
select ......
from ..... innner join .....
select ......
from ..... innner join .....

and of course running
select * from MyInfos where like '%pattern%'
takes around 15s because the 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

Written By
Any way to optimize view
January 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.