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
MySQLovernight, 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,
witheach query leftjoining a flag from this table, or the sum of values from another. A typical routine might have 10
ofthese 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
thetemp tables do help debugging. But this approach doesn't allow the dbase any optimising and these long chains of
queriesare quite fragile.
My question is whether there are better ways of doing it. I could probably (hopefully) convert it into one mega query,
whichwould allow the dbase the opportunity to optimise. My worry is that it would be a dense tangle of things and hence
difficultto 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