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
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 ?
Original can be found at
[url]MySQL :: MySQL Forums :: Views
I am pretty sure that I could be more successfull trying Percona forums 
Any feedback welcome
Thanks and best regards