On 12/1/05, Jesse Erlbaum <[EMAIL PROTECTED]> wrote: > I've used both MySQL and PgSQL. I've also used Oracle, Sybase, DB2, MS > SQL Server, and Informix. I've also been developing web apps for quite > a long time, so I feel my opinions carry *some* weight.
your credentials are outstanding, and your opinion does carry a lot of weight. > That being said, my preference is still MySQL. That being said, I have to disagree with some of what you say below. And I'll state upfront that my preference is PostgreSQL (most of the time). I have used both MySQL and PostgreSQL for a long time as well (probably 8-10 years now - I even used mSQL for a time before MySQL gained ground in popularity). I have also used DB2 and recently a little bit of MS-SQL. But I guess my experience with proprietary DBs is no where near as extensive as yours. Also, it looks like this is continuing one of thousands of other discussions online about 'my favourite <blank>'. I've tried to stay objective and will state upfront that I think MySQL is useful in lots of places and is the right answer some of the time. I just don't think it is the right answer as often as other people do :) > The biggest criticism that I've heard leveled at MySQL is that "...it's > not ACID. It's more like a file system with a SQL interface." After 10 > years developing web/database apps, I've discovered that 80% of the > time, that is exactly what you need. Agreed. You don't often need those things for a simple web app. In fact DBM files are sufficient for lots of web apps (and probably faster too). And there is always sqlite, but it won't scale very well. > Web applications are software, but they are very notably different from > client-server software. One of the most significant differences is that > the web is stateless. This means that some advanced features, such as > transactions and cursors are not generally applicable. If I was writing > a client-server application I would have a stateful connection, through > which I could use a cursor to step through results, or hold a > transaction open. Agreed on cursors, but I disagree about transactions. You describe long lived transactions here, and those aren't very useful on the web. But when updating multiple tables at the same time, transactions are incredibly handy. Rollbacks are very important for data integrity, something that has never been a strength of MySQL. Of course with InnoDB tables you can do transactions in MySQL, so that is not really a differentiator between the two. > That is not the case on the web. On the web, when a page is rendered, > the application stack is effectively terminated. That's not to say you > *couldn't* devise some scheme to simulate statefulness. You simply > would not *want* to. On the web, every request has to contain all the > information needed to fully articulate a transaction. That means, you > need a FAST database with the tools necessary to get the job done. Yes, but as I said above that doesn't obviate the need of transactions. > That, in a nutshell, is MySQL. Features such as "offset/limit" (which > were practically invented by MySQL, which are not standard SQL, which > don't exist in Oracle, and only exist in PgSQL because they were so > damned useful) are a classic example of why MySQL is the most popular > database in the whole world for web applications. It is the right tool > for the job. Same with the "auto increment" columns. A feature which > didn't exist in Oracle-like databases, but was a practical solution > which made life that much more easy. But these features are implemented in odd and annoying ways. For example: // Give me the first 10 rows SELECT * FROM mytable LIMIT 10; // ?? 10 rows starting at 20 ?? // ?? or 20 rows starting at 10 ?? SELECT * FROM mytable LIMIT 10, 20; They actually change the meaning of the first number, so it is OFFSET, LIMIT. That is very confusing. Now of course MySQL has taken the PostgreSQL way of specifying limits and now allows you to use SELECT * FROM mytable LIMIT 10 OFFSET 20; Which is obvious and clear right from the start. And personally I believe auto increments are better handled as sequences. There are <tonnes> of other annoyances with MySQL but I'll only list a couple that 'really' annoy me: - the first timestamp field in any table is always updated on every UPDATE - 0000-00-00 is a valid date in MySQL - 2005-02-30 is a valid date in MySQL - insert NULL into a NOT NULL column and MySQL will give it a default value - overflowing data is truncated instead of returning an error For a more complete list (although it looks like it doesn't cover 5.0) look here: http://sql-info.de/mysql/gotchas.html And here is a list of gotchas for PostgreSQL, since it is by no means perfect: http://sql-info.de/postgresql/postgres-gotchas.html > There are dozens of other examples like these (such as the MySQL > interactive shell, which beats the pants off of sqlplus, or mysqldump > which annihilates pg_dump). The theme here is that MySQL was created to > be three things: I find psql much easier and intuitive than the mysql shell. For example it gives me help for every single SQL command right at my fingertips (\h CREATE TABLE). > 1. Simple Yes (but not always obvious in what it does) > 2. Reliable Reliable in that MySQL rarely crashes. But not reliable when it comes to data integrity. The fact that MySQL has some of the best crash recovery tools available should be a sign. I still get table corruption happening regularly on heavily used tables (Although almost all of the time MySQL is able to recover those tables with the caveat that I can never be sure that nothing was deleted in the cleanup). > 3. Fast Fast for single tables, but not noticably faster than anything out there when doing joins. Also, I find that I have to do some hand holding with the query optimizer so that it uses the right indexes for some queries. This has taken queries that took minutes down to seconds, purely because MySQL used the wrong index. > Let's not forget that the "P" in Perl stands for "Practical". PgSQL was > created as an academic exercise: "Can we write our own Oracle?" If I > wanted to be "academically correct", I'd be programming in Java. I > don't, and I'm not. Now you are getting silly Jesse. Ad Hominem attacks? PostgreSQL tries to follow the standards that are out there, and also to lead the way with new technologies (like MVCC). MySQL seems to follow the Microsoft route of "I'm gonna do it my way", which usually means completely different then the rest of the database world. Of course after years of stating that 'all those academic features are unecesary', they have gone and implemented them anyway in 5.0. > And, BTW: Nearly all those advanced, "academically correct" features > which people point to when pimping PgSQL (row-level locking, stored > procs, transactions, triggers, ref. integrity checking, clustering, > etc.) are available for MySQL right now, or are slated to be available > in the next release. So I guess they aren't useless after all. > However, PgSQL is still slow, hard to use, and of > questionable reliability. PostgreSQL is fast, rock solid and works out of the box. I have never had any issues with disappearing data, or table corruption with postgres. I have never had postgres crash on me. I rarely have to read the postgres manual to figure out how to do something. It has a fantastic query optimizer and very useful explain output. And it has a huge amount of options when it comes to complex queries. On the other hand, I still suffer from table corruption with MySQL. It still munges my data at a whim just so it doesn't have to throw an exception. I still have to do things with temporary tables that should be done with a sub-select (we don't run mysql 5 yet, since it is too new for me). In fact, just this week I have been migrating a forum from MSSQL to MySQL (new forum software). It is nice and fast, and I have been very happy with the results (see I don't hate MySQL, I just don't like it as much as most people). But during the transfer, there were some really slow queries that I couldn't optimize without altering the forum software. When migrating the 80,000 user accounts, one of the queries the forum software does is to see if that user already exists in the database. Should be a quick key based lookup, but it does a 'WHERE LOWER(username) = ?' which means it can't use the index on 'username'. In PostgreSQL I would have just created a function based index on LOWER(username) and it would have zoomed by, but with MySQL (which doesn't support function based indexes) it had to do a table scan for each user it created. There were other things I could have done on the application side (like alter the code, or skip the validation routines), but I shouldn't have needed to. > > Also, Postgres is much more Oracle-like in syntax than > > MySql, so if we should ever graduate to big-daddy-Oracle, we > > can do that > > easier. > > I'd like to shoot this idea down pretty quick: The idea that you might > "graduate" to Oracle. I'd like to point to an article in InfoWorld just > two weeks ago: > > http://weblog.infoworld.com/article/05/11/17/47FEmainmigrate_1.html > > So, there you have it: 14,000 transactions per SECOND, handling 40% of > the worldwide travel reservations... And all running on MySQL. So, it > is possible to run a HUGE enterprise using MySQL -- no graduation > necessary: Just good application architecture. I doubt that transition was easy, which is what the original comment was refering to. But, of course you can run huge database on MySQL. It takes a bit of work, but it can be done. However, that doesn't mean you can't also do the same thing with PostgreSQL. I run some very large MySQL databases. Tables with millions of rows (sometimes up to a 100 million), inserting 100,000-200,000 rows per day. But I dread it whenever I get a call about it. Usually it means there was some table corruption and it means 10 - 12 hours of downtime while the tables are cleaned up and the indexes are rebuilt. I don't have any of these problems with PostgreSQL. It just works - all the time. The best way I can describe my experience with the two is how I think when developing. When I am building or debugging an app with MySQL and something is not working, I always wonder "is MySQL doing something odd here". Whereas when developing with PostgreSQL it is always the last thing I question when things are not working. It is only a gut feeling, but it has been built over years of experience with working with the two databases. Cheers, Cees --------------------------------------------------------------------- Web Archive: http://www.mail-archive.com/[email protected]/ http://marc.theaimsgroup.com/?l=cgiapp&r=1&w=2 To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
