Method of building views

Hello everyone.

I am a recent convert from quite an old version of MySQL. I have an ancient business app/database that I dump into MySQL overnight, then run a whole set of queries on to form tables that users then query using Excel DSN.

Up to now, because MySQL didn't have subqueries, these routines have been built using a series of temporary tables, with each query leftjoining a flag from this table, or the sum of values from another. A typical routine might have 10 of these routines appending fields. Several of these things run every morning, and on a 6G dbase take a couple of hours.

I suppose what I'm doing is building some quite large, complicated views. Now, thus far I haven't had much choice, and the temp tables do help debugging. But this approach doesn't allow the dbase any optimising and these long chains of queries are quite fragile.

My question is whether there are better ways of doing it. I could probably (hopefully) convert it into one mega query, which would allow the dbase the opportunity to optimise. My worry is that it would be a dense tangle of things and hence difficult to maintain/explain.

Should I be looking at the procedural languages ?

Am I worrying about nothing ? Should I just take a common sense view about chunking the query ?

Any advice from people further up the learning curve would be nice.

thanks

Rich