[Zope-DB] SQL optimisation: follow up

[kapil thangavelu]

On Thursday 07 November 2002 11:01 am, Charlie Clark wrote:
> > >From what I have read it seems like a good idea to switch DBs at some
> > >point
> >
> > and I would probably switch to Postgresql so that I can use things like
> > Views and Subselects (would be useful in this I think) and all the things
> > MySQL doesn't support. Marc-André Lemburg also recommends using the SAP
> > database but I don't know if that's supported under Zope; it would
> > certainly be more difficult to get on my ISP.
>
> Well, I've spent a couple of days getting to know Postgres and doing the
> data migration. I thought SQL/Zope was supposed to make it *really* easy to
> change databases/RDBMS till I actually did it.
>
> Postgres spent a lot of time barfing and complaining: it's very fussy! But
> I think it has been worth it. While priming the database with test data
> takes about 30% longer, all queries seem to take the same amount of time (a
> couple of seconds) now regardless of how complex they are.
>
> Lessons learned: don't use MySQL for anything for more than prototypes for
> which Gadfly is usable; MySQL is so "error tolerant", ie. makes me have to
> worry about what to do with bad data *after* it's there that it's really
> worrying. I'm sure there are plenty of people out there who know what
> they're doing with it (and similar approaches) and are aware of all the
> pitfalls but I was really shocked to see myself assuming that the way MySQL
> did SQL was the way SQL worked.
> Get a good book on RDBMS design (there aren't that many of them,
> apparently);
> Understand the relational model and stick to it and ignore marketing
> slogans or helpful advice about how to optimise your queries.

i completely agree with most of this, but i feel compelled to point out two 
points. first mysql with innodb, actually qualifies as an rdbms in my book, 
ableit a poor one as it does have limited form ri, and good txn semantics. 
otoh, the lack of stored procs, views, subselects, and other common rdbms 
techniques pushes alot of db logic into the app layer. second, advice on 
queries shouldn't be ignored out of hand, esp. if you get it at a good source 
like the pg lists. 'explain' cmd is your friend.

cheers,

-k