#OBSERVATION #1

The MySQL Documentation gives you the Restrictions on Views

• 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) that clearly demonstrates doing an aggregation in a subquery followed by all kinds of JOINs to that subquery.

OBSERVATION #1

The MySQL Documentation gives you the Restrictions on Views

• 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) that clearly demonstrates doing an aggregation in a subquery followed by all kinds of JOINs to that subquery.

#OBSERVATION #1

The MySQL Documentation gives you the Restrictions on Views

• 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) that clearly demonstrates doing an aggregation in a subquery followed by all kinds of JOINs to that subquery.

#OBSERVATION #1

The MySQL Documentation gives you the Restrictions on Views

• 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) that clearly demonstrates doing an aggregation in a subquery followed by all kinds of JOINs to that subquery.