[Zope-DB] SQL optimisation: follow up

[M.-A. Lemburg]

kapil thangavelu wrote:
> On Thursday 07 November 2002 11:01 am, Charlie Clark wrote:
>=20
>>>>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 lik=
e
>>>Views and Subselects (would be useful in this I think) and all the thi=
ngs
>>>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 th=
e
>>data migration. I thought SQL/Zope was supposed to make it *really* eas=
y 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 tim=
e (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 reall=
y
>>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 M=
ySQL
>>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.
>=20
>=20
> i completely agree with most of this, but i feel compelled to point out=
 two=20
> points. first mysql with innodb, actually qualifies as an rdbms in my b=
ook,=20
> ableit a poor one as it does have limited form ri, and good txn semanti=
cs.=20
> otoh, the lack of stored procs, views, subselects, and other common rdb=
ms=20
> techniques pushes alot of db logic into the app layer. second, advice o=
n=20
> queries shouldn't be ignored out of hand, esp. if you get it at a good =
source=20
> like the pg lists. 'explain' cmd is your friend.

Funny that nobody ever considers using the open-source SAP DB.
That's a full-blown RDBMS with a long history of being reliable
and fast.

--=20
Marc-Andre Lemburg
CEO eGenix.com Software GmbH
_______________________________________________________________________
eGenix.com -- Makers of the Python mx Extensions: mxDateTime,mxODBC,...
Python Consulting:                               http://www.egenix.com/
Python Software:                    http://www.egenix.com/files/python/