From: Eric Maryniak Date: March 12 1999 11:05am Subject: Some questions/concerns about MySQL List-Archive: http://lists.mysql.com/mysql/105 Message-Id: <3.0.5.32.19990312120540.00b46330@ip005.niwi.knaw.nl> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Hello, 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! : ---snip--- ... 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 processing. 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 deleted. 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. ---snip--- Kind regards, Eric Maryniak -- Eric Maryniak 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.