MySQL Forums
Forum List  »  Views

View takes forever to select from - did I miss the point?
Posted by: bingo wings
Date: November 19, 2010 04:30AM


We have a complex set of tables that we want to create a view from to simplify using products such as Crystal reports that have minimal tools for crafting queries - so we want to aggregate a persons data into a single row in a view rather than spread across many tables.

The select for this view is large with 8 subselects that have left joins, and the main select having 11 left joins - its a very large set of data - 121k rows in the result set.
If I just run the select and limit to 1000 rows I can get the result in about 20 secs (this is only on a laptop with 8gb memory, x64 corei5 - not on our production machine) now I know that 121 x 20 = 40 mins (omg) but I was under the impression that once the view was built that selecting from it would be really quick - but in reality selecting a single row takes up to 20 mins.

have I totally missed the point of views here? Thew view seems to be recreating itself each tmie I select from it maybe? or do I need to make some config change?

I created the view by simply:

CREATE VIEW xyz AS SELECT ..........

Options: ReplyQuote

Written By
View takes forever to select from - did I miss the point?
November 19, 2010 04:30AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.