[Zope-DB] SQL optimisation: follow up

[Charlie Clark]

> 
> >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=E9 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.
Make sure you know the order in which you can build your tables and pull 
them apart.

This isn't supposed to be a rant. More like a few notes to be found when 
someone else in a similar situation (bound to happen) trawls the 
ML-archives.

Three cheers for Python, Zope and PostgreSQL

Charlie