No announcement yet.

Any ways to optimize views aka having them use indexes

  • Filter
  • Time
  • Show
Clear All
new posts

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

    I am pretty sure that I could be more successfull trying Percona forums ;-)

    Any feedback welcome
    Thanks and best regards