Any ways to optimize views aka having them use indexes

Almost one year ago I posted the following question on MySQL forums but never got any answers

The basic idea behind my question is that for me views were kind of “macros” which means that
the definition I typed for a view will be dynamically expanded my the MySQL engine therefore being
able to fully use partitions and indexes created appropriately to optimize the complexity of joined+union statements

In other words, if I define a view X as select x from table1 inner join table 2 … union …
I was expecting that typing select … from X where …
would be quite equivalent to select … from table1 inner join table 2 … union … where …

It occured to me that this assumption was wrong as partitions/indexes are not used AT ALL

I also want to have this code factorisation (via View definition) to reside in the MySQL server and not
in my application because I want to use those views in several programs written in different languages (Java, Perl, PHP, Python, …)

May be there is another way to do this than using views

here is the original text I posted: ====================================== 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 ?

Original can be found at,577646,577646

I am pretty sure that I could be more successfull trying Percona forums :wink:

Any feedback welcome
Thanks and best regards