On Sun, Jul 07, 2002 at 06:44:06PM +1000, KevinL wrote: > #include <twocents.h> > > The usual complaints regarding MySQL surface on large projects: > > 1) No transaction support (fixed with innodb, but up until recently a > problem)
has the intellectual property dispute over that and the mysql trademark been sorted out yet? last i heard, mysql and nusphere were still fighting. innodb means that mysql is more than just a fancy interface to the filesystem. it's still got a very long way to go before it's a real database server. > 2) No sub-selects (which is a pain on a moderately complex system - > makes for more awkward systems involving temporary table creation and > joins) that's about the only thing that can be worked around with client-side programming. > 3) No triggers/internal language ala pl/sql - again, this sucks for > more complex systems, but is mostly work-round-able _iff_ you have > full control over the code that talks to the database. The main body > of code I work on, for instance, has a fairly complete OO layer > overtop of the database code, so all interfacing with the database > goes through a programmatic layer anyway, where trigger-like behaviour > can be implemented. no, you can't emulate triggers or built-in procedural language, just as you can't emulate transaction support with client-side programming. sometimes, e.g. for security reasons or to ensure integrity of related tables, you MUST do things in the server, you can't rely on the client to do the right thing. > Having said that, it's faster than postgres in most situations, by all > accounts (note: crass generalisation, I'm sure there's instances of the > reverse too). it's not even most situations. mysql is only faster than postgresql in a very limited range of toy situations. in the very simple case (i.e. READING a lot of data from one table by one user, with little or no writing), mysql is faster. that's it. if that's all you ever need to do, then mysql will be faster for you. in real world situations, postgresql is MUCH faster because it works smarter rather than working harder - e.g. you can do things in one SQL query with postgres (or other real database) that requires multiple queries with mysql. now each of those mysql queries might be slightly faster than if postgres were doing them (and even that is debatable with recent versions of postgresql) but the cumulative effect of having to do two, three, four, or even six times as many queries really destroys mysql's claim to greater speed. for some interesting reading on postgresql's speed, try: http://apachetoday.com/news_story.php3?ltsn=2000-08-14-008-01-PR-MR-SW read the full article, it's worth it (but take it with a grain of salt because it is basically a press release). here's a good teaser excerpt: "On the ANSI SQL Standard Scalable And Portable (AS3AP) benchmark, a rudimentary information retrieval test that measures raw speed and scalability, Postgres performed an average of four to five times faster than every other database tested, including two major proprietary DBMS packages, the MySQL open source database, and Interbase, a formerly proprietary product which was recently made open source by Inprise/Borland." a real article from someone who uses both postgres and mysql extensively is: http://www.phpbuilder.com/columns/tim20001112.php3 tim purdue (dba and programmer for sourceforge) explains how mysql isn't coping with the sourceforge database, and what happened when he ported it to postgresql: "With that in mind, I decided to test out a full port of SourceForge.net to Postgres. The site was written with a database abstraction layer and it turned out to be a cinch to get it up and running on Postgres, including a full import of all production data from MySQL. Not only did the site come up on the first attempt, but it ran fine! In fact, our very first benchmarks showed Postgres running 6x faster than MySQL on a very database-intensive page (the "My Personal Page" for logged-in users). To make things interesting, we ran the exact same code base on MySQL 3.23.26beta and Postgres 7.1 (CVS pre-beta version). The SQL structures were identical, except that I had added Foreign Key constraints to the database schema on Postgres (MySQL doesn't really support foreign keys). This should have tilted the performance in favor of MySQL, because MySQL doesn't do any data validation on inserts/updates, but Postgres did in this test." again, read the full article. there's a lot of useful info in it whether you use mysql or postgres. it gets particularly interesting where he talks about scalability - in short, mysql simply doesn't scale well at all to multiple concurrent users. btw, that's a followup to an earlier article by the same guy: http://www.phpbuilder.com/columns/tim20000705.php3 in which he explains his reasons for choosing mysql over postgres a few months earlier. also worth reading. postgres is up to version 7.2 now, and is faster than it was back in 2000. > Craig, I reckon calling MySQL a toy now innodb exists and is fairly > stable is a bit harsh - given transactions, it's a capable database. > It often still gets _used_ as a toy, but that's not it's fault. it's not harsh at all - unless you count the truth as being harsh. it still gets used as a toy because it is a toy. > Triggers and pl/sql would be a nice add, but are not thoroughly > necessary - and will, in fact, adversely affect your > ability/likelyhood of writing a database-agnostic system (ie. you're > more likely to lean on db-provided functionality, resulting in a > system that's tied closely to one database instead of portable easily > to MS/SQL or Oracle or whatever else). the fact is that you're going to do that anyway. every db is different. every db has a different variant of SQL...they might look superficially similar but there are enough differences that you're going to have to rewrite all but the simplest of queries in any migration. as for the internal languages, at least postgresql's pl/sql clone is similar (not identical) to oracle's...not that i can see many valid reasons for migrating from postgres to oracle. > (/me uses mysql and postgres, incidentally - more mysql than postgres, > tho) i've tried using mysql. i just can't see the point. it's a huge step backwards with no benefit at all. given that postgres exists, is MUCH faster in complex real-world usage than mysql, i really don't understand why anyone uses mysql. why use a toy when a real db is available that does everything that the toy does and a whole lot more and has a far superier administration features? i know that mysql gets all the press. but press coverage and popularity aren't good reasons for picking a database, any more than they are good reasons for picking an operating system. craig -- craig sanders <[EMAIL PROTECTED]> Fabricati Diem, PVNC. -- motto of the Ankh-Morpork City Watch -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]