We have some internal discussions about the suitability
of MySQL as a core (central) and/or satellite database
(database that has a part of the total central database,
eg. on the web, usually read-only). We're about to change
to a whole new setup for our databases and we have to make
some "strategic" (longterm) decisions.
A collegue of mine had serious difficulties with some missing
features in MySQL, and I give his comments verbatim below.
We have read the manual :) so we're aware of TcX's view and
workarounds, and that some features are (more or less) on the
(more or less in the future) TODO list.
Personally I feel that the question of performance <--> some
features is partly philosophical: it depends on the relative
importance you give to performance vs. eg. referential integrity.
Imho you never let it come to a ref.int. violation in your
front end apps, because you don't want to confront a user with
that error---you'll usually write some (PHP eg.) procedures
that will consistently insert, update or delete an object and
related objects (or refuse to after precondition checking that
will usually be more complex than a simple ref.int. check).
And the openness and programmability (and fast) is an extra+.
It's a real "programmer's database", i think.
Anyways, what I would like to know is if anybody on this list
did make a strategic choice for MySQL and how they handled
(if so) some of the missing features in MySQL.
Here are my collegue's thoughts, comments invited! :
But studying the manuals I am confronted with several fundamental
limitiations of MySQL in relation to the use NIWI wants to make of it.
A www-online database in which NIWI can maintain it's documentation,
client and suppliers data.
The problems are:
1. No Sub-selects, which is very awkward, but can be surpassed in www
applications, but they will be more complicated and will have more
unnecessary fault possibilities and become bigger.
2. Transacties, foreign keys, stored procedures, triggers en commit
rollback are not supported. In other words referential integrity is not
supported. Because the NIWI databases are primarily meant for (online)
input and mutation of records this is a very fundamental problem.
In fact NIWI doesn't do anything else as (online) transaction
For instance how to maintain a multithesaurus database that is used to
index several objects in the databases without referential integrity
implemented in the database engine.
3. No views make life very complicated.
Conclusion: MySQL is a possible candidate (if it can search better and
faster) to replace the NIWI search engine software PLweb. Because the
data are stable. But that means unnecesarry replication of data.
Nicer would be of course a direct web interface to the database in which
the data are maintained.
Online database applicaties become unnecesarry heavy and unreliable.
Which is shown in the suggested solution for the deleting of
mother-child related records on page 240 of the manual. Here the mother
is killed before the children are removed. In case of problems there
will be no way to find the children because the motherrecord is already
In a good database I have only to implement refential integraty once
when designing the database and that is independant of the application
for which I use the database.
Eric Maryniak <e.maryniak@stripped>
Home page: http://pobox.com/~e.maryniak/
Netherlands Institute for Scientific Information Services (NIWI)
Tel/Fax: +31 20 4628650/6685079. Internet: http://www.niwi.knaw.nl/
Microsoft announced that the official release date for the new
"Windows 2000" will be delayed until the second quarter of 1901.