#OBSERVATION #1

The MySQL Documentation gives you the [***Restrictions on Views***][1]

> - It is not possible to create an index on a view.
> - Indexes can be used for views processed using the merge algorithm. However, a view that is processed with the temptable algorithm is unable to take advantage of indexes on its underlying tables (although indexes can be used during generation of the temporary tables).
>
> Subqueries cannot be used in the FROM clause of a view.

Therefore

- The result set of a View would be some temp table used for row-by-row retrieval
- There is no existing mechanism to index the result set coming from a view

Creating indexes only help the underlying base tables within the JOIN a the View, not the View itself.

#OBSERVATION #2

You mentioned this

> Some suggest to do aggregation in subquery before join which is not possible as MySQL doesn't allow subquery in FROM clause.

That's not true. I have a post in StackOverflow ([**Fetching a Single Row from Join Table**][2]) that clearly demonstrates doing an aggregation in a subquery followed by all kinds of JOINs to that subquery.


  [1]: http://dev.mysql.com/doc/refman/5.6/en/view-restrictions.html
  [2]: http://stackoverflow.com/questions/5983156/fetching-a-single-row-from-join-table/6023217#6023217