Why MySQL Grew So Fast

jpkunst writes "Andy Oram, who attended the MySQL Users Conference which was held April 16-18 in Orlando, Florida, attempts to explain MySQL's popularity in his weblog at oreillynet.com. (More weblogs about the 2004 MySQL Users Conference can be found at the The 2004 MySQL User's Conference & Expo Blog Collection.)"

Comments

Pretty simple.

[Maul]

1. MySQL can be installed without cost.
2. MySQL is easy to install and learn.

Re:Pretty simple.

[mabu]

4. A tight, clean system that isn't bloated with crap that is superfluous to its main objective.

5. A package that doesn't morph into a different product every six months with a new, catchy name, or divided into umpteen modules scientifically designed to require you to get every possible option in order to finish your application.

6. A software package that isn't so ridiculously complicated to install and use that companies make more money selling training and support than they do implementing applications.

Re:Pretty simple.

[kpharmer]

> 3. A very clean and easy to use API, in a language that is universally understood and almost universally portable (C) for incorporation into other products
> such as PHP, Perl, Python, Apache itself, and an endless amount of other applications.

Portable - only in the context of its ability to compile & link. Not very portable in the sense that its users require it.

MySQL has a nearly complete disregard for ANSI SQL standards - which results in the least portable syntax of any relational database I've used. This list includes:
* mysql
* postgresql
* oracle
* informix
* db2
* sql server
* sybase

Additionally, since it's the only entry on the above list that is missing or has limited support for transactions (yes I know - you can get it with its slower innodb file system now), views, etc - much of the sql written is brain-dead compared to other databases. So, one query in oracle/postgresql/etc can easily turn in three in mysql. This mapping queries between mysql and other databases causes considerable performance problems - since most other databases provide the best performance (in most cases) with given a single query to perform a unit of work.

So, if the non-ansi syntax isn't a big enough pain-in-the-butt, having to rewrite the queries and application to get reasonable performance often is. Amazingly, I've found that it's easier to convert an application from SQL Server to Oracle than from MySQL to Oracle.

Ah well. I suppose much of this will improve over time as they rewrite their engine to include more and more of that functionality that nobody wants.

But in all seriousness - there's not much to analyze about its popularity guys. It pretty much boils down to:
right place at the right time
It owned the light, easy and free niche four years ago. It's a different ballgame with postgresql now, but mysql has built up quite the following in the meanwhile.

Re:Pretty simple.

[sasha328]

It is actually quite simple. When I started to use an SQL database, I did not "choose" the engine. That was chosen for my by ht eproject I wanted to do.
Basically, I wanted a Problem tracking system. Did some searching and stumbled on the excellent: MantisBT at sourceforge. It needed MYSQL. So I started using MYSQL. If it said it needs POSTGRESQL, I would have used that.
I think that is the main reason why MYSQL is more popular. It's not the "price" or the "licence". I knew both were "free".
I started my DB development using FileMaker about 10 years ago. I wait eagerly for the day it is fully ported to Linux, not just the server.

Re:Pretty simple.

[Ed Avis]

The reason for its success: Worse is better.

What you say about disregard for SQL standards is true, see MySQL Gotchas. Doing the wrong thing is not so bad, it's *silently* doing the wrong thing that you absoultely do not want in a database system. See also Why not MySQL, which is now rather dated (MySQL has grown some features since), but is a good introduction to what a database should do.

Note also that anyone can write a database system with complete transactional integrity: you simply lock the whole database for every single operation and run only one query or update at a time, one after the other. The challenge is in getting the semantics of serialized database access but with good performance. This is what schemes like row-level locking and multi-version concurrency are for.

Re:Pretty simple.

[Laur]

you know, that little niche Operating System that happens to just run on 95% of computers out there...

Not to be pedantic, but that should be 95% of desktop computers. Servers are another story, and since we are talking about a database (not a word processor) the distinction is relevant.

Re:Pretty simple.

[110010001000]

Actually:

1) It doesn't cost anything
2) It kind of works for most use cases

Re:Pretty simple.

[Betcour]

True, but even if a database is not hosted on Windows Server (and many still are), the developper might work on a Windows desktop. Having a local copy of the database to test out your work is convenient (and also safe).

Also many newbie developpers run Windows. Once they have learned and moved on to Unix, they tend to stick to the tools they started out with.

Re:Pretty simple.

[Anonymous Coward]

Your post is so chock-full of misinformation and lack of understanding, it's hard to know where to begin to correct you.

In order to do propper transactional support, you have to inject a lot of overhead, and that's why MySQL has always been blazingly fast.

s/been/seemed/

"Speed" is relative. Having to emulate the functionality of transactions in the user app will always make your app slower than doing it in the DB, where it belongs.

The lack of views is directly related to the lack of sub-queries, which is being addressed, but is still a questionable feature.

Views are not 'questionable' - contrary to your belief that they are only good as subqueries, views serve many functions - for one, they are pretty much the only way for a DB admin to delegate permissions at row-level. Without them, you either grant access at the table-level, or not at all.

The syntax is no more deviant from ANSI than, say, Oracle or Sybase

Not true; and even if it was, it's still 'MySQL' syntax, or 'everything else' syntax. Being different, solely for the reason of being different makes you in the wrong. (MS is guilty of this - I call them on it, I'm calling MySQL on it.)

there are a few places (especially in the types arena) where MySQL does not yet implement some ANSI features, but I've never run into a significant problems in those regards.

Then you've never done any real DB programming. (Which would certainly explain some of the comments you made.)

It's not that nobody wants it, it's that most of the people who want it aren't actually providing reasons as such

Ubelievable - people have to justify wanting proper ACID compliance?!?!??! What have you been smoking?

Stored procedures and other similar features are just bloat, and gain you no real advantage

Bull. FUCKING. Shit.

Stored procedures provide a higher level of performance, and provide *MUCH* more scalability. It's just common sense - moving *all* of the data away from the DB, passing across the network, and to business logic in the application will always be slower than having the logic in the DB itself (where it can be processed without having to move it across the slowest link in the chain.)

you just use the right tool for the job. Why is this so different?

Because, in most circumstances, it's not the right tool for the job. 90% of the MySQL projects I've seen were done by people who have *no* clue that they could have saved themselves months of time by using the features of a real DB, because they have to re-invent the wheel. (You seem to fall into this category.)

Re:It's too bad

[ciroknight]

Too bad indeed.. if it weren't for poor products that get widely adopted fast, graet products would never be adopted. For instance, the reason why the world wide web took off was because Microsoft created a HORRIFIC web browser, but since now all computers had a web broswer, everyone had access.

MySQL was in it's own, a huge part of the dot com boom, and therefore a huge part of the history, and therefore, the future, of the internet. Hate it, love it, it's a great product with a great niche, and for now, it'll continue along that path.

inmy opinion

[minus_273]

2 reasons. LAMP and the fact that not everyone can afford or require for their tasks Oracle

Picking the right tool for the job

[Anonymous Coward]

Slashdot users complain that MySQL doesn't have the full feature set of some RDBMSes... but they miss the point. The reason MySQL has been succesfull precisely because it's been very good at delivering the features that a particular set of people need. To these users, additional features are a liability, not a feature.

This reminds me a lot of DBase III. (Bear with me here...)
DBase III wasn't a very good database program, but in its heyday millions of people used it and it got the job done for them. Even relatively inexperienced users could make use of it and write simple programs to manipulated their data. Even though it sucked, it was the right tool for a lot of jobs at the time.

Compared to DBase III, both MySQL and PostgreSQL are excellent. I wish I'd had either one a decade ago when I started work doing clipper programming for a dog track related publishing company.

For the dog track application I would have preferred Postgres; the rollback support would be pretty compelling for an application like the one we were doing. Rosebud is a sled, and Verbal is a huge liar. Darth Vader is Luke's father, and the Sixth Sense guy is actually dead. The planet of the apes is Earth, and Rocky loses. For something where I was just kicking around a database (Which I've also done a lot of) MySQL would be perfect. MySQL would be ideal in something like the RHS Orchid Registry, for instance.

If application bigotry keeps you from choosing the right tool for a job, you will be a less valuable resource to those who employ you. Not too many people seem to "Get" this. People are often surprised that I will, on occasion, suggest that Microsoft products are the best tool for what they're trying to do. Usually those people asked me expecting a "Windows sucks use Linux" spiel, but if I think their situation warrants it (Inexperienced user, just wants to browse the web, word process and send E-Mail or wants to play games at all) I'll tell them to use Windows.

Re:Picking the right tool for the job

[Unordained]

Looks like someone never used the feature of other databases of deferring restoring constraints until after the tables are uploaded ... and something about the word "integrity" just doesn't jive with the phrase "you usually ... in the right order, anyway".

The locking aspect is interesting -- we use Firebird (the database) at work. We've got several dozen people online at any given time, doing updates. We rarely delete rows. Users can't delete -- they can mark things as deleted, but that only affects listviews' default behavior. Firebird will prevent you from updating a record that has a FK to a row also being updated by someone else -- the reasoning being that the other connection could just as well be deleting the parent row, or planning to. It's first-come, first-served, which prevents quite a few problems. Except we use pessimistic locking when users go to edit a record (it doesn't lock when you view.) So this happens often.

I can't express how often that's gotten in the way, because I know that -usually-, we don't delete. It gives my users hell, because the server won't tell me -who- has the lock, to let me tell my users "uh, go down the hall and beat X senseless for being in your way."

But boy am I glad the feature's there. Because sometimes we do delete. Integrity can't be guaranteed anywhere other than at the database level, because you have no guarantees about the client program accessing the database. Transactions can't normally see into each other to determine if a conflict might happen. E.F. Codd said, a long time ago, that the RDBMS itself had to be able to keep everything clean. And he's still right. It's not just a data-store.

I strongly disagree

[Trevor Goodchild]

Mr. Oram's long-winded screed on MySql, while interesting, really makes the situation sound much more complicated than it is. You don't need to over-analyze this thing. The truth is simple and readily clear to everybody already.

In a nutshell, MySql is free. Is it great? Hell no, but it's free. The only deep understanding of human nature or the DB marketplace one needs to comprehend here is that given the choice between something great and expensive vs. something mediocre and free, the overwhelming majority will go for free.

MySql has always had huge problems preventing it from being accepted in the real "enterprise" marketplace, but most of us aren't in that market. Most of us need to yank a bit of data and cram it into a web page moderately quickly and as cheaply as possible. MySql does this quite well.

What doesn't MySql do well? For starters, it's much slower than Oracle, MS-Sql, and even Foxpro. It has no row locking, no transaction support, and minimal cross-platform compatibility. But, it's free and it works more or less ok on Linux.

Perhaps the real truth that Oracle fears is that eventually DBAs will come to realize that 99.9% of their storage needs aren't so "mission critical" as they would like to believe. I mean really, how many people out there can truely justify the cost of a full featured, robust database like MS-Sql? 10%? 5%?

For the rest of us, a free - albeit slightly dodgy - solution will work fine.

Re:I strongly disagree

[Safety Cap]

For years, the MySQL community told developers, "You don't need [transactions | foreign keys | triggers | stored procedures | subselects | ...]! You can work around those limitations in your application code and be better off for it!"

Couple that with this quote from the article:

But MySQL's very simplicity made it so small and fast that it quickly won over small users who wouldn't even understand what they were missing and how to use the fancy features offered by "real" database engines.

And you get the problem of people using a RDBMS that have little idea what they are doing. Stored procedures for speed/security? Not needed. Normalization (3+)? Academic exercise only. And so on.

MySQL is good enough exactly because it is good enough. For enterprise-class applications, or systems that need to be maintained though several generations of DBAs/Developers, taking the "good enough" shortcuts will end up killing you in the end. Pushing the data integrity code to the app instead of asking the RDBMS to do all the heavy lifting will come to bite you in the arse when scalability becomes important.

If MySQL works for the majority of installations, so be it. You never get to be number one in your pack by following the pack. You have to innovate and do what you do really well. "Good enough" only gets you outsourced.

Re:I strongly disagree

[LostCluster]

Watch those queries get slower and slower the more users you add. With Oracle, watch the queries perform the same under a far greater load than MySQL will handle.

Of course, most of us who run on MySQL dream of the day that we'll have enough users to have to upgrade to another database engine. Afterall, it's much cheaper to throw MySQL onto a faster box than to bring in Oracle. :)

Re:I strongly disagree

[gnuman99]

So what? With MySQL, it is cheap enough just to add another CPU or whatever. Instead of running on a Xeon, you just move to a dual or quad Opeteron.

MySQL is a very fast database if the keys can fit in RAM. Today, RAM and CPU speed is not a problem as it once used to be. Differences between MySQL and Oracle are blurred.

Sure, if you need 20,000 connections to the same database, maybe MySQL is not what you want. But MySQL licensing terms (for commercial usage) are much more flexible than Oracle's, especially if you want most bang for the buck for a small to medium database installation.

Re:I strongly disagree

[Osty]

Pushing the data integrity code to the app instead of asking the RDBMS to do all the heavy lifting will come to bite you in the arse when scalability becomes important.

Hell, I don't even care about scalability. How about simply being able to trust your data? I'm currently working on a database-backed project that has aboslutely no foreign key constraints at all (among other problems, though the SQL engine is not MySQL and we are slowly but surely fixing the issues). We're constantly trying to clean up our data sets (not fun when you're talking about several tens of millions of rows) and track down the offending code so we can add constraints and then handle the insert errors properly, but it's been a long and arduous process. We're actually at the point where we're willing to throw away the current system and start over (or, well, run side-by-side for a while). It's not fun.

If MySQL works for the majority of installations, so be it. You never get to be number one in your pack by following the pack. You have to innovate and do what you do really well. "Good enough" only gets you outsourced.

Very true. Let me also add for those who think that MySQL is a good learning tool -- it's not. While MySQL does support much of the ANSI standard, you're going to run into problems (some of which are MySQL's fault, some aren't):

• The tricks and hacks you have to do to work around MySQL's limitations (subselects, views, stored procedures, triggers, etc) are unnecessary in a real RDBMS, but you won't know that because you only know MySQL.
• More importantly, the hoops you have to jump through in MySQL often lead to suboptimal SQL code. You may not really notice this on the light data sets where MySQL excels, but you will as soon as you try to migrate this knowledge to a larger system.
• MySQL gives you a false sense of performance. Your data sets are small (ideally, otherwise you're going to be in for some shit with MySQL), and so you don't care about proper indexing, whether you're doing index seeks versus table scans, whether your data is well-normalized versus normalization trade-offs for performance, etc. What performance tuning you do learn from MySQL likely won't translate well to other systems.
• MySQL has its own non-ANSI syntax additions that don't apply to other RDBMSs (AUTO_INCREMENT column type, for example)
• Similarly, other RDBMSs have their own set of specific keywords, so you still have to put in the time to learn them (do you know how to do an auto-incrementing column in SQL Server? MySQL won't teach you that)

If you want to learn, get yourself a real RDBMS. Microsoft's desktop engine version of SQL Server is free, and Oracle has free downloads available as well. If you don't qualify for either of those or don't agree with the licensing, at least use something more robust like PostgreSQL. If you're trying to learn, you'll be much better off learning on any of those platforms than you will with MySQL.

Re:I strongly disagree

[GooberToo]

200,000 rows is still considered to be a small database. If you access this database with anything concurrent select/insert/update activity, you might consider looking into PostgreSQL.

While obviously, PostgreSQL isn't a cure all bullet, you might be surprised as how well it performs and how much better it scales.

Re:I strongly disagree

[LostCluster]

Postgre might be Free, but converting an existing project that's already designed for MySQL to any other database engine has its costs.

Re:I strongly disagree

[GooberToo]

I wish someone would mod this up.

MySQL users constantly forget that they are doing things wrong compared to most other systems. As a result, when they compare to other systems, they are almost always comparing best MySQL performance with absoluete worst case performance on other systems.

Re:I strongly disagree

[TheRealSlimShady]

If you read the article, it seems that MySQL performs so well because it basically returns most of the results from cache - i.e. the tables aren't changing much - which means the server isn't under load. Doing a select isn't really a load generating exercise. Try the same test when the database is doing considerable amounts of updates and the results will be very different. You'll also notice that the performance of MySQL drops by 2/3rds (according to the article) when you disable the in memory query results cache.

Re:I strongly disagree

[GooberToo]

Hate to burst your bubble, but that test highlights MySQL's greatest strength. Which is, what pretty much everyone openly admits. That is, readonly (select) environment. Serving web pages, what is generally very simple selects, has always been MySQL's best trick. Period.

Obviously, 4.x has further helped its best trick scale even better. The complaint about MySQL is under load, especially in a more complex environment, (concurrent selects/updates/inserts), MySQL historically turns into a major dog. Generally speaking, it normally falls to the back of the pack in every test where scalability and a non-read-only environment exists.

Now then, having said that, the chart only shows part of the picture. Were they allowed to turn the benchmarks to target each database or did they all run the exact same code? If the answer is, they all ran the exact same code, then the test is immediatley invalidated as it ignores many, many significant features that are pretty much standard on all modern RDBMS, save only MySQL. Did they use the default tables or did they use Inno tables? They need to use Inno tables to a fair comparison, especially so if the SQL wasn't implemented and optimized for the other platforms. Were the database connections persistent or transient? That too can be a major factor.

Based on the results, I *suspect* some of the concerns I have are true, but clearly, that's nothing more than FUD at this point. Care to provide a link which provides more details?

Re:I strongly disagree

[joib]

Actually posgres isn't preforking, it forks an engine for each incoming connection.

As the parent poster said, windows applications tend to use threading more than unix apps, because fork is very inefficient on windows, and I think that IPC is not as good either as on unix (IPC is used for shared caches etc.). On the unix platform, there is no clear-cut winner in the multithreaded vs. multi-process "war".

E.g. apache 2 can be run both in multi-process mode and multithreaded mode. And guess what, on unix platforms the multiprocess mode is the default. If nothing else, this strongly suggests that at least the apache developers think that multi-process is better on unix than multi-threaded.

Now, the one thing where multithreading clearly is better than multi-process is the time to open a new connection (creating a new thread is much faster than forking). And that's why apache uses pre-forking and with postgres you use connection pooling or persistent connections. So in the end the fork overhead doesn't matter at all.

Re:I strongly disagree

[GooberToo]

Please.

Okay, since you insist.

Postgres is the only database in wide use which is not multithreaded.

So? And your point is?

Don't make up lame excuses as to why it's better then multi threaded databases.

Ah. Thankfully, I didn't have to make anything up. It's a simple fact. That means it's true to the layman reading this. ;) In threaded applications, simple stack or data segment corruption is all that is needed to corrupt anything from a single field, all the way up to a page, and perhaps more. This doesn't mean that threading shouldn't be used. It does, however, mean that simply stating that PostgreSQL uses a process model and try to imply that it's bad, is completely false. Especially in light of the fact that electing to use a threaded model carries significantly higher risks. Worse, should a single thread become corrupted, it corrupts the whole process. That means, even if the moment the corruption occurs, no damage is done, the potential for damage is still lurking as it may still be lurking within the process it self. This also means that should the process fail, the entire database is down, having crashed more than likely. This also means that the likely hood for repeated corruption is also increased.

A process model, on the other hand, means that, at worst, a single connection (a single backend) will fail, allowing all other backends to continue. This, in theory, means a larger window for larger uptimes. Best of all, in the event that process corruption occurs, and the connection is transient, the potential for damage ends when the client disconnects. So, this leaves us with pretty much just shared memory being open to corruption. While it's possible this could go unnoticed, the odds are significantly lower that the corruption will go undetected before it has a chance to be written to disk, because of the data layout in shared memory and the implementation of checksums of hashes.

In other words, the simple fact is, aside from the process fork versus thread spawn overhead, a process model is easily argued to be a superior model. So really, what you hoped to be a plus is litterally a negative in the eyes of all that understand how these things are put together.

Because they were the first to support subqueries

[rimu guy]

Not.

MySQL has always been fast. That is probably why most people use it.

MySQL has also been easy to manage (e.g. move database files from one subdirectory to another and the tables have also moved). That kind of simplicity brings tears to the eyes of an Oracle admin. There are a few options you can tune and teak, but by and large it just works out of the box (er, RPMs).

And of course the reason it has been so popular is that it has been so popular. If you get my circular drift. People use it because there is a lot of documentation about it. Perl and PHP pretty much always have the MySQL libraries so it can be used on web sites, etc.

Speacking of those subqueries, what's up with the delay getting 4.1 out from alpha to beta/gamma/production. I want to start using it. And 4.1 has been out in alpha for over a year now. Not to mention new development is already proceeding with the 5.0 release.

- Run the latest and greatest alpha MySQL database on your own VPS

I like MySQL

[Anonymous Coward]

Not everyone is a database elitist. Not everyone has to worry about transactions nor store procedures. Triggers are neat, but not always necessary. (Insert obligatory VHS/Beta comment here.)

What is great about MySQL is that it gives the average Joe or Ho with a machine a chance to build a database backed application of some sorts. Its cool. Its free. Its fun.

Now for all of those who have based their fragile nerd self esteem on their DB experience or knowledge need to turn off their computers and go down to the local bar and talk to the local people about local people's reality. Ya MySQL is not DB2 nor Oracle, but it is still pretty cool. And the fact that Monty has written the greater portion of it is pretty cool too.

Naysayers need to get laid!

Re:It's too bad

[localman]

As a programmer who values practicality above theoretical purity, I don't really understand how something as incredibly useful as MySQL can be so "poor".

All I know is that I've built three highly successful, high volume websites off of MySQL over the past five years and there's no way I could have done it as cheaply or quickly otherwise.

Poor product indeed.

Cheers.

Re:Because they were the first to support subqueri

[Anonymous Coward]

The statement "move database files from one subdirectory to another and the tables have also moved" is a tautology. The tables are in the files, so of course they move.

"That kind of simplicity brings tears to the eyes of an Oracle admin." No, it doesn't. I'm an Oracle DBA, and I'm not crying because MySQL lets you move datafiles - so does Oracle. Typing "alter database rename datafile..." isn't exactly rocket science.

Oracle also works "out of the box", especially when it's used for the sort of applications that can make do with MySQL. Granted, big motherfucker DBs might need some basic memory tweaking, but small sites can generally get by with the default parameters.

MySQL is popular because it's free, and it meets the needs of certain users. That's all there is to it. It isn't better, and it isn't worse.

Re:It's too bad

[batkiwi]

Access and mysql aren't even competing. It's like saying, "Why would I use openoffice when I can use notepad?"

Access is a minimal driver-loaded (no deamon) RAD tool, for when you need a quick and dirty forms and business logic driven app for a few people.

People use it as a simple DB, but people also use MSword as a note-taking app. To replace access, you'd need mysql + a gui DB design tool (I know they're out there, just can't think of one off the cuff) + one of:
-apache + php (no gui designer though!)
-java (swing or swt with a gui designer)
-VB
-VC++ (although now you're getting heavier...)

Plus a server of some sort to run the mysql on.

Access is generally crap, and I hate using it, but it's great for a small office of 10 people to do small amounts of ordertracking/whatever type of small app they want pieced together quickly and cheaply, without UPKEEP of a server.

Re:It's too bad

[Anonymous Coward]

In fact PHP is a highly customizable, fast-rendering and highly scalable platform. It's not PHP maintainers' problem if their tool is badly used all over the place.

I think you should keep your faulty beliefs for yourself young man, and give due respect to these very fine men.

- Bernard Wolkacz

Re:It's too bad

[webword]

Poor design probably is less important than wide adoption when it comes to growth. But that is circular. Growth and wide adoption are really the same thing, right? At a minimum, wide adoption is a result of growth. They are tied together.

So, taking a step back, what elements drive growth? That's the question. Google taught us that popularity matters.

Taking a different step back, I would argue that usability has driven growth. Namely, ease of use. A quote from the article supports this:

"But MySQL's very simplicity made it so small and fast that it quickly won over small users who wouldn't even understand what they were missing and how to use the fancy features offered by "real" database engines."

My final comment about "poor design" is this. Assuming the design is poor, does it really matter? If it solves problems, and if people use it, and it is a Good Enough solution, and if the price is right, poor design is largely unimportant, right?

bullshit

[ErichTheWebGuy]

Remember when Reasoning, Inc audited the code? They found that it had 0.09 errors per 1,000 lines of code while proprietary competitors had 0.56 errors per 1,000 lines. That's more than 6 times as many errors in the proprietary databases. http://searchenterpriselinux.techtarget.com/origin alContent/0,289142,sid39_gci941817,00.html

Quality product. That is why it is popular. Perhaps you should research your argument before posting a flame next time.

Re:bullshit

[BiggerIsBetter]

A well implmented bad design is still a bad design. It might not segfault, but it can still screw your indexes if the algorithm was wrong in the first place.

/just saying

Re:bullshit

[Hangtime]

This will probably come out as a troll, but oh well. MySQL in terms of complexity is no where near what DB2, Oracle, or MS SQL are today. It boils down to that probably MySQL is supporting 1/4 to 1/3 of the things that these particular platforms support today. Also, these platforms have to support legacy apps as well. Freaking DB2 has to support stuff from darn near the inception of SQL while also adding new features. MySQL has relatively a short history of code and a feature set that is much smaller in comparison. So if you have a fairly new product and your doing things that are simpler and already solved by others of course your going to have an easier time doing it and thus less errors.

MySQL, PostgreSQL, Oracle, MSSQL, etc....

[linuxtelephony]

Over the years I have been a user on PostgreSQL, MySQL, Oracle, and MSSQL, and an admin on PostgreSQL and MySQL.

Having said that, I prefer MySQL and PostgreSQL to both Oracle and MSSQL, in most situations. However, given my experience with MySQL and PostgreSQL, I am glad that I have returned to PostgreSQL.

Why PostgreSQL? Simple. I am able to use referential integrity, triggers, and foreign keys in my databases. I can use subqueries, and more. There are certain databases where the data integrity is the important part. Having the database enforce that integrity is cheap insurance. Having transaction support, including rollbacks, are great for operations that affect multiple tables. I also like the way Postgres strives for SQL compliance.

MySQL is improving. Everytime I check they are getting more and more support of things I consider critical. Especially in the last 9 months to a year. But not yet enough for me.

I was involved in a fairly large scale production system that used MySQL as its heart. Unfortunately, at the time, PostgreSQL just did not have the performance that was needed. And, the main DBA was a mysql zealot. With MySQL, we seemed to constantly have to figure out creative work arounds for what MySQL lacked. Table level locking was a headache. No referential integrity and lack of transactions were a nightmare.

I still see MySQL as the better solution when you need to serve text files via SQL really really fast. But, when you need to provide a specific level of accountability and traceability, PostgreSQL is still my choice.

Yeah... I'm gonna sqitch from Oracle to MySQL

[l0ungeb0y]

"On the other side of me, at that lunch, sat a database administrator whose facility is planning a migration from Oracle to MySQL"

Whatever moron made that decision needs to be outsourced to India. Thats sort of like trading in a shiny BMW for a freakin go-cart.

Sure, MySQL has gotten better, has always been speedy and is great for down and dirty webservices. But the bottom line is still the same: It's not a **real** database. Transactions? Stored Procedures? Triggers? Schemas? Groups? Views? Uhhhh Hello!!!

Granted, MySQL is popular; just about every cheapo hosting service has installed it and offers it up as part of their base level $20.00 a month hosting pack.

Being a seasoned webdeveloping gun for hire I deal with online data services all the time. Time and time again I use postgreSQL.
Sure, the client always brings up the MySQL question, but when I show them what can be done with postgreSQL and what can't with MySQL it becomes glaringly obvious that MySQL is __NOT__ the tool to use if you have any real service to offer or data to mine.

For all you MySQL advocating web developers out there:
If you put all the SQL functionality where it should be -- in the database -- and not the middleware you'd never even think of MySQL as a real alternative, because MySQL doesn't support that.

Re:Yeah... I'm gonna sqitch from Oracle to MySQL

[evilviper]

Thats sort of like trading in a shiny BMW for a freakin go-cart.

Considering the price of gas, it would be a good trade.

Re:It's too bad

[localman]

Hmmm... a $72 million a year toy? That's what the most recent company I work for had in revenues last year. Entirely built upon MySQL. I'm sure that's small change from your point of view, right?

Sorry, but practical trumps theoretical every time.

Cheers.

Views? Subqueries? Easy to move databases?

[linuxhansl]

I for one cannot understand how anybody can do *any* serious database work without views and subqueries (the latest MySQL alphas/betas have support for subqueries). The whole relational theory is (almost) broken without these.
To me that's mindboggling. Without that I'd rather use berkeley DB or flat files to load and store my data. How do you do row-level security without views, what about column security. Or just different views for different users. These are just a few example that require *a lot* of coding without database support (not to speak about performance). Heck, do people even understand what views (or triggers, etc) are?

People say it's easy to move databases around my MySQL. Yeah, sure, as long as you stay with the ISAM tables, which do not support ACID. InnoDB tables support ACID but cannot simply be copied around.

It makes me shudder to think about all the future DBAs that accept the low standards MySQL is setting.

Re:Views? Subqueries? Easy to move databases?

[LostCluster]

If you want "views"... you want recent PhpMyAdmins which let you "bookmark" a SELECT query so you can run it again sometime. That's really all a view is.

MySQL is nothing more than a database engine. You need some other application to provide a UI if you want to see what's going on visually, otherwise you're just interfacing with command line...

Re:Views? Subqueries? Easy to move databases?

[GooberToo]

Well, I think you missed the point of MySQL. MySQL targets the "select x from y where a='123'" group. There's nothing complex about it. Serious database work is not done with MySQL, thusly, serious features required to do serious work are absent. This may change as MySQL matures, but the fact remains, its simplicity generally meets its user's skill level. Many (most?) MySQL users are there because they are clueless about RDBMS systems and MySQL was the first free product that ran on Windows that they heard associated with SQL. So, for them, the vast majority have no idea they are even missing powerful concepts such as subselects, ACID, triggers, RI, stored procedures, etc.

Remember, happiness is completley subjective.

Re:It's too bad

[localman]

Really? That's odd... I've been using it on high volume sites for about 5 years now and I've never had one data corruption problem. Does my anecdotal experience cancel out yours?

(Once did have an index become corrupt, but a "repair table" command fixed it).

From what I can tell, most people who trash MySQL do so on theoretical grounds. In practice it is amazingly useful. That's all any product needs to be.

Cheers.

Re:Why?

[LostCluster]

Access is the database sibling to Visual Basic... in fact, a code module in a .mdb file is Visual Basic for Applications... which aside from the fact that it depends on having Microsoft Access present is just about as powerful as Visual Basic itself. Access projects can even be compiled into .mbe files which locks down the forms and code users can't see or change your source.

It's a great cheap tool when what you've got to do is open up a bunch of flat files, grab some data from each of them, and then output a pretty-looking report. You can then get it down to a push-button interface so that a newbie can run your tool, and you can go on to something more important.

Re:Note to the Mods...

[DAldredge]

Me too, It just bothers me when people promote is as better than oracle...

But, I guess I like being down moded.

Re:MySQL for Sybase admins...

[LostCluster]

From what I see so far, there isn't much equivalent to any of this so far. You create a database, and your server config determines where ig goes and (I think) the format/type used for the physical files.

To put it bluntly, you're going to use the default filetype for everything in MySQL. There's some nods to the idea of having ram-based tables and the like, but if you're really serious about that stuff then MySQL is the wrong tool to be using.

One of the biggest knocks against MySQL is that its favorite file type is basically a flat file with just a bit of an index to it. More or less, that's true. MySQL isn't meant for somebody who cares about where exactly on the hard drive the data is going, just that it's being saved and will respond when called upon with a SELECT query.

Think of yourself as a database user rather than a database admin. This thing has most of the "administrative" stuff hardcoded. You don't start until it's time to define tables and indexes...

Re:Note to the Mods...

[Anonymous Coward]

Yeah, but this is Slashdot. Just because something is the truth, doesn't mean it's an accepted opinion

Re:The Truth About MySQL

[fimbulvetr]

You are what most people consider to be a ignorant, propaganda-influenced bloke (or average consumer) if you're willing to:

A. Believe that 33.5% is a significant number, of any type. See the book, how to lie with statistics.

B. Believe any propaganda released by Microsoft, or any other released "benchmarks" when they haven't released exactly how they've done the testing.

C. Believe that an MCSE is enough to make me consider you an industry professional. I can read and memorized books too.

I stopped trusting MySQL

[yem]

.. the day it returned a date column with the value '2309-46-39'.

(that, retarded access control system and the random data corruption..)

Re:I stopped trusting MySQL

[Anonymous Coward]

In databasey land you CANT have this shit happen. Did u know that MySQL will LET u insert illegal dates? it wont even bitch about it. Virtually ALL other databases check dates. if im building anything where i care about my data it's not going near MySQL for that simple reason.

95% of ppl use Windows but that doesn't mean it's really better does it?

Keep MySQL but only use it for stuff u dont care about.

Just before I go there seems to be this stupid idea amongst script kiddies and all those 20 yo wanna be's who have just learnt how to do a for loop in php. The concept is put all ur validation in ur php code or ur vb app or whatever - let ur tables be free (which is the only option with MySQL, even fucking MS Access has constraints) - real databases have constraints and u can capture error messages from the databases and transform them into something useful. let the database manage the data and let the app be the front end - no more of this db = file/data interface shit.

now im all worked up *sigh*

Re:I stopped trusting MySQL

[Hektor_Troy]

I actually think he's being serious.

What most people don't seem to get is that the database isn't your filesystem. It is to your data what the kernel is to your OS.

Do he expect a sys_call function to be executed from any random program? Why not? He seems quite willing to judging by his lack of understanding of what the database is for.

There's a reason database admins can rake in huge fees, and it's not because they think data-integrety checking is done outside the database ...

I dislike MySQL

[Osty]

Not everyone is a database elitist. Not everyone has to worry about transactions nor store procedures. Triggers are neat, but not always necessary. (Insert obligatory VHS/Beta comment here.)

You're right, not everybody has to worry about those issues, but maybe they should. However, the problem is not so much with MySQL itself (it's a good, fast, lightweight storage system for simple and small amounts of data). It's with the perception that MySQL is every bit as good as a more robust engine (Oracle, MSSQL, DB2, take your pick) for any application. That is definitely not the case. As well, knowing MySQL does not make you uniquely qualified to decide that it's better than one of the other choices for a system that needs that level of robustness. The biggest problem is that people who only know MySQL choose MySQL because that's all they know, even when it's completely unsuited to the task.

Add to that the arrogance of the MySQL developers ("These aren't the stored procedures you're looking for ..."), and the zealotry of the user base, and it's easy to see why those of us who do know a thing or two are bitter about MySQL. I laugh anytime someone tells me that they can enforce data integrity from their application layer instead of using foreign keys (usually while trying to clean up their mess of a data set so the data itself can be trusted). I find it hysterical when I'm told that stored procedures are a complete waste of time (typically while fixing someone else's SQL injection problems because they insisted on writing dynamic SQL queries from their code).

I'm all for making databases and db technology more available to the Average Joe, but MySQL is not the way to do it. If you need free, there are many better alternatives to MySQL (especially if you only need free for training purposes, because then the big three are available to you as well).

Re:I dislike MySQL

[Decaff]

Scalability is often not a case of 'enterprise level design', but a sensible choice of platform and technology at the start. Its about allowing for the possibility of enterprise design in the future. Its not about buying Oracle or whatever, its about enabling that possibility.

This does not take a lot of work, but it does require a more professional attitude than "I gave up with Postgresql after a few hours, and decided to go with MySQL because its cool."

Re:It's too bad

[rycamor]

>> ...and if the price is right, poor design is largely unimportant, right?

I suppose that's something you'll enjoy saying to your clients when they realize their data is corrupted because an application bug circumvented your data model ;-).

OK, sorry... that was a cheap shot.

But really, the question is largely one of awareness. Most developers are simply unaware of the logical capabilities of the relational model, so they assume that the things they would do with a "full" SQL system are almost the same as what they would do with MySQL. If that is the case, then of course there is less reason to use MySQL. (there are also those developers who ARE aware of those capabilities but are too much in love with writing extra code, so they prefer to dismiss them)

But, once you realize that with the proper SQL system...

1) You no longer need to worry about validating every little piece of data you insert. The design of the [table/view/procedure + constraints] IS the validation, if done properly.

2) When you need to change or add business logic, you can do it in just one place, and rest assured that no code elsewhere in the system, whether written by you or someone else, can circumvent this.

3) If for some reason another development team needs to access this data, you don't have to lose sleep wondering if they are screwing up your data or worse.

4) The *types* in your DBMS actually mean something. (MySQL is so bad at type enforcement that it is almost typeless) ...and these are just a few of the things I am talking about. Yes, if you don't know about them, then of course they don't matter. I developed with MySQL for at least 2 years, blissfully unaware of these things. Then, I found out the capabilities of a real DBMS, and I was able in some cases to reduce my application code by 80-90%. That results in not only a better designed system, but in a *cheaper* system, since writing and debugging is much less trouble, and your liability for data corruption is far less.

Taking this in the larger context of your post, MySQL can be compared to fast food, such as McDonalds. It's cheap, it's everywhere, and sometimes it hits the spot, but a steady diet of it is ... not recommended ;-).

Comment removed

[account_deleted]

Comment removed based on user account deletion

All of these posts are leaving out mSQL

[astrashe]

I think that MySQL had a lot of help from mSQL.

I don't remember the details of the licenses, or exactly what happened, so some of what I say here might be wrong. But wasn't mSQL sort of dominant in this space (people writing simple web/db apps on linux) until they did something ugly with their license?

And wasn't MySQL's API sort of similar to mSQL's, making it easier for projects like PHP to pick up MySQL?

Are you kidding?

[Anonymous Coward]

A view is just a query which you can run again sometime?

You really need to learn RDBMS theory.

I do find it interesting that Linux users like to lord over Windows users how "sophisticated" they are, but when it comes to MySQL, they use the "well it does what I need" excuse, ignoring the gaping technical issues with the product.

Don't miss this gem!

[Nugget]

From the same URL:

"MySQL uses table locking (instead of row locking or column locking) on all table types, except BDB tables, to achieve a very high lock speed. For large tables, table locking is MUCH better than row locking for most applications"

Re:Win32 Binaries

[GooberToo]

I completely agree with you here. I believe that once the native Win32 port of PostgreSQL finally becomes available, it will start to make some serious inroads.

The cygwin port is good for playing and development purposes, but it is not ready for production purposes. If someone is using it in production, good luck and more power to them. While I consider my self to be very pro-PostgreSQL, I wouldn't use the Win32 cygwin port in a production environment for anything.

For what it's worth, the native Win32 port is being actively worked on. Hopefully it will be available in the next major release. It may slip to the second release out. Nonetheless, it is actively being tested, developed, and worked on.

Right On

[Caiwyn]

The article rambles a bit, but it does hit the nail on the head when it comes to what drove the rapid increase in popularity of MySQL -- that it was small, fast, and easy to learn, mainly due to the fact that it did not include features that were, for many users, extraneous.

When I first went looking for a database to drive my website, my more knowledgeable friends and professional acquaintences all hawked postgresql. Since it was the default db that shipped with Red Hat, I figured I'd try it. I liked how robust it was, but I had a hell of a time finding support for it in the applications I wanted to run. I eventually switched to MySQL (which I had already used for various other projects) because it still remains easier to use, and because PostgreSQL is way more than I need.

The simple fact of the matter is that most users don't need ACID compliance, or transactions, or what have you. They need a storage system with sql interface, and that's it. Users who need more from a database would pass up MySQL for something better suited to their needs... but those users are in the minority. Everyone else's needs are simple -- MySQL sacrificed the less essential features for speed, simplicity, and ease of use. As a result, it was more attractive to people who were adequately served by its feature set.

And as MySQL has progressed, it has added in many of those features that higher-level databases like PostgreSQL offer, allowing us the option of using those features in the future.

The dual license is, in my view, a great business model. It provides the revenue stream open source projects need without sacrificing the freedom for those users who embrace the open source concept. As I understand it, it's free for use, and free to distribute under the terms of the GPL... but you have to pay if you want to use it in a non-GPL product. To me that's genius -- it forces a licensee to play by the same rules he sets, which seems only fair. I wouldn't be surprised to see more projects adopting similar models, nor would I mind.

Re:Note to the Mods...

[golgotha007]

who says that MySQL is better than Oracle? i've never read or seen that anywhere. it is two different products in two different classes. they can't be compared.

it's all about the right tool for the right job.
coding up the next Ebay? use Oracle.
wanna keep track of your DVD collection? use MySQL.

i've used Oracle and Postgres, and i've setup triggers and stored procedures and hard relationships.. sure it's useful,
but it's rare when i'm doing a project that's out of scope of MySQL (and i can do all the trigger, stored procedure stuff within the application code, big deal).

MySQL also allows you to do rapid development of small to medium sized projects. what if one of my projects gets so big that i need to scale it up? well, if i am so unfortunate to have one of my projects go big time, then i'm sure i'll get big dollars and redesign the project for the big leagues.

here's another example:
i was working for a small company that finally recieved it's funding (15 mil for 10 employees). well, the company started hiring corporate types left and right.
well, these corporate folks had little to do except dress nice and figure out how to spend our funding.
i was called into the CTO's office. he sat me down and explained to me that he wanted to setup a database to store employee information (address, phone number, normal stuff). at that point we had about 25 employees. i was like, no problem i'll setup a MySQL database with a PHP front end and have it done this afternoon.
he told me to do nothing and wait for the Oracle people that were coming the next day to discuss licensing.

my jaw hit the floor.

but this is exactly the problem. people don't realize which tool they should use for what job.

simplicity translates to speed

[vallis]

One thing that MySQL isn't is a bloated whale of an application. Oracle is feature rich and under heavy load, when administered correctly, is blazing fast. But that also makes it a system resource pig.

Part of the reason why every SQL feature in the world isn't implemented is because it sometimes pays to make an application lean. I tend to believe the authors/maintainers have a lean-mean philosophy, and sometimes prefer to let the users implement their own creative solutions instead of providing every bell, whistle and horn.

As a hypothetical example, one can easily implement an auto_increment feature outside of MySQL using a combination of a simple table declaration and some create PHP or Perl programming. Not that you'd want to, but some creativity can make up for non-implemented features.

In simple terms, MySQL is the equivalent of a cheetah. It's fast and lean, and accomplishes it's task with agility and grace.

MySQL is also easy to learn and easy to implement, especially if you are using the Apache/MySQL/PHP or Perl combination. Even better, this entire scheme will run using only 128-megabytes of RAM (thereby making my 5-year old AMD 500MHz still usable!). Try that with Oracle... can you say swap partition hell???

Re:simplicity translates to speed

[Just Some Guy]

In simple terms, MySQL is the equivalent of a cheetah. It's fast and lean, and accomplishes it's task with agility and grace.

That's a pretty good analogy! Now, put a saddle and a 200-pound rider on that cheetah and see how fast it moves.

MySQL is blazingly fast when doing nothing. PostgreSQL is still blazingly fast when pulling a heavy load, like a Clydesdale on meth. How does that make MySQL better?

In all seriousness, MySQL is fast because it doesn't do anything for you. By the time you shift all of your core logic into the application layer, which is often something terrible like PHP, the end result will probably be much slower than if you built a proper system with PostgreSQL in the first place.

Re:It's too bad

[jadavis]

When someone is examining a couple alternatives, and one works right away and the other requires reading (even merely 5 pages), the reading just won't happen unless the user really cares. You eliminate a large number of users just by raising the bar a very slight amount.

However, I'm a postgres user, and I'm here to say that reading 5 pages of docs is not required to issue your first "create table" command.

In Debian, it's one command to install and it will ask you simple questions, like where to put the data. For those who like a GUI, another command will install pgadmin3. Really, how much easier can it get to install?

To connect for the first time in Debian, just become the user "postgres" and connect to "template1" and it will let you in without a password (after that you can create more databases and users).

Why are these vague accusations about usability never pointed at the distributor (Redhat, etc)? I don't complain about the installation difficulties of KDE because Debian handles that for me. If I were to write a usability report of KDE without using Debian, would it be fair to speak of the myriad dependency problems and long compile times? No, it wouldn't, 'cause the distributor handles that for most people.

And the thing is, MySQL and PostgreSQL, as far as using the database, basically both just provide the ability to execute SQL commands. The difficulty couldn't be over PostgreSQL's syntax for creating a table, I assume. So where is this usability issue appearing? If it's during the install, shouldn't the distribution do a better job of installing it for you?

It's really not fair to point the finger at postgres when the reality is that your distributor just doesn't care enough to make it easy for you, and maybe their default database is MySQL.

And even compiling from source, there aren't many packages anywhere near as complicated as PostgreSQL that compile and install so easily.

Why MySQL is more popular than Postgres

[relativePositioning]

It doesn't seem like anyone is honest enough to spill the beans on this one. Postgres isn't available for windows without using Cygwin. The truth is that most beginers are looking for an executable download... with an installer. If you tell them that they have to compile or install something else first, they freak out.
The truth is that people are learning on their home systems which are more often than not windows systems. On top of this, most hosting companies only support what is most popular, hence MySQL.
Once Postgres is available on windows as an executable download with an installer, watch its popularity soar.

Often textfiles are perfectly sufficient.

[Qbertino]

MySQLs popularity is due to it being percieved as an extension of the textfile solution and it's open and closing problems rather than what database savy would call a database.
If you want to drop data somewhere and pick it up later - which is usually the case in 99% of the time - MySQL is perfectly sufficient.
Real usecases of databases however acutally require a solid integration of data and code and transparent runtime access to it. In terms of true object orientednes the 'real' DBs are just as much a compromise as MySQL is and require tall stacks of code to compensate for hardware constraints and data obscurety.
Object relational DB's that offer absolute transparence of data and application space at runtime are the true thing. But those, appart from a few exeptions, aren't quite there yet.
Zope/ZopeDB comes to mind as an example of what DBs should be like. Compared to Zope, MaxDB, Postgres or even Oracle are not much more than MySQL. It's all dependent on the perspective.

Re:It's too bad

[ajs318]

it has no separation between syntax and libraries - making it a complete nightmare to compile and install if you want to enable as many features as possible. They should have designed it so that the language interpreter could be compiled, and then you could add extensions without recompiling PHP itself. PEAR is a step in the right direction, but too many things still rely on built in extensions.

Read your history. Up till version 4, PHP was still growing. It was designed so that new bits could be kludged in on an as-and-when basis. That may not be "perfect" from the point of view of a snotty programmer who thinks there is only one right way to do anything, but not everyone thinks that way {imagine an electronic engineer and a mechanical engineer showing one another their latest inventions: the elec. eng. is going to bemoan the proliferation of moving parts in the mech. eng.'s design and the mech. eng. is going to deride the elec. eng. for using too few moving parts}.

Now the feature set is stable, it can always be re-implemented in a more "beautiful" style.

and WAY too many programs use the mysql_* functions directly. Those things are an abomination to good design. Why the hell should you have to completely rewrite your code to support a different database in these days?

Well, since the mysql_*, pg_*, sybase_* and so on functions use very similar syntax, try using sed.

But I think the question we should be asking is, why would you want your code to support a different database anyway? MySQL is free software, so it'll always be available and supported. Ditching some of the bells and whistles and relying on the scripting language (perl, PHP or python) to do some of the donkey work made it bloody fast {e.g. the primitive % and _ wildcards work so much quicker than full-blown regular expression matching, that it's quicker to pull out more records than you need, have the wrapper script do the regular expression matching and just throw away the ones it doesn't need; more of the queries you are going to do are going to be right than wrong, so let the script provide any 'rollback' functionality you may need}, and -- barring a power failure -- it doesn't corrupt its own tables either.

You obviously think that constraining a programme so it only performs one function is a bad thing -- I guess your ultimate piece of software is one that doesn't care what kind of hardware it is running on or what function it is being asked to perform. But such high ideals are too far removed from reality for most ordinary people to take seriously.

Most programmes don't need to have so much changeability, because they are designed to do a specific task. You can add your fancy object oriented classes and methods, abstraction layers and sundry filibustering tricks all you like; but nothing will change the fact that, at the end of the day, sooner or later, you can't avoid the inevitable fact of having to get your hands dirty and actually manipulate some data. It does mean that a programme meant for handling order forms with a Postgres backend is going to need a lot changed to make it do cooking recipes with a MySQL backend, but if your audience prefers to see a pony doing one trick well rather than a full repertoire of tricks badly, who's disappointed?

Re:I've Tried PostgreSQL

[wagemonkey]

Also a way to emulate MySQL idioms transparently on PostgreSQL would make migration a blast. I've been considering moving our system from MySQL to PostgreSQL but having to check and correct every single SQL query of a 100k+ PHP application is just not worth it.

I hope this is a joke.
You want PostgreSql to emulate MySQL code? Please mod parent up +5 Funny.

That would put a layer of code onto PostgreSQL to slow it down to enable it to run bad SQL even slower. If you're trying to turn PostgreSQL into a dog and complain about performance it's a brilliant idea.
Migrating from one RDBMS to another will always involve tweaking. If you're migrating from MySQL you can call it re-writing instead. There're lots of other posts explaining this better, but it's not really compatible with anything except itself. It's probably easier to migrate from Access....

Easy answer...

[MattRog]

I can give you the reason why MySQL is so popular: practitioners are ignorant of data management fundamentals (perennial links: Unskilled and Unaware of it and Database Debunkings).

If you don't understand or know the necessity of things like constraints and tying business logic close to the data then you don't care that MySQL can't do them. It's obvious that MySQL developers do not have a clear understanding of the relational model, either.

And how is this elitist? Is it elitist to require that engineers who build bridges know the physics behind bridge building? Would you go to a doctor that didn't know the science of human physiology? Why do we not expect the same level of competence from people who build databases?

As computer professionals we need to hold ourselves to the same standards that we require other professionals. I'm not suggesting, or even think it's a good idea, to license developers but we need to get out of the mindset that it's acceptable to eschew formal ideas (predicate logic/set theory and the Relational Model) for ad hoc junk science (XML, UML, virtually every SQL DBMS product, etc.) all in the nebulous name of 'performance'.

Re:The Truth About MySQL

[pw1972]

My company is a big MS shop, and shis is exactly why we won't hire anyone with an MCSE. They can spout off all sorts of numbers, percentages, tell you the storage size of every variable in the MS world, but when it comes right down to it, they can't deliver results and work SO SO slow.

This is not the point...

[aksansai]

Read the article again as to why MySQL has had enormous acceptance. It really is a good read. The article's intention was not to say the MySQL is the answer to any and all RDBMS questions. Point being - MySQL works because it's light-weight and fast enough to support even large websites. For the vast majority of general web scripts and even small applications, MySQL does the job - and does so quite nicely.

If you, in particular, need row-locking capability - look elsewhere. The beauty of open-source is that you have a choice, whether to use or go without. So as far as your "bad" point - MySQL would not be the answer would it?

To address your peeve, the nice thing about MySQL is that it provides a structured, easy-to-interface method to store a large amount of data for a variety of applications. Sure, you could use a flat-file or BerkleyDB - but then you constrict yourself to how you can access that database.

MySQL (not to say it's the only one) allow you to connect via a variety of interfaces, a local socket or across a network (standard or encrypted). Having a networked database allows for all kinds of uses for applications that may or may not want to store its data on the computer they are running upon.

Problem isn't the database

[Stone316]

Its the administrators. I hate to say it but alot of people who run/support databases aren't DBA's! They were either thrown into supporting it, or had an interest but its not their 'job'.

I've been a DBA for 8 years or so now and I encounter this problem all the time. Once I had a dev. group's call a meeting with us to find out what services we offered. The developers obviously didn't want to be there and were very vocal that they didn't need our services.

My first question was how did they backup their database? (They were running Oracle..)

Their response, "Oh, we let the filesystem backup catch it."

I responded, "Do you shutdown your database first?"

Them: "No"

Me: "You need us."

Save yourself the hassle, if you have a database that is critical to your business, hire someone that knows the internals. Whether its mysql, postgresql, db2, oracle, mssql, your going to save yourself alot of headaches.

Yeah, I know, DBA's are cool and its fun pretending but seriously, save this stuff for the experts.

I'm of the opinion that alot of database applications could run just as effectively off of flat files because most apps don't use the advanced features of RDBMS's. Thats why mysql is popular if you ask me.

Re:It's too bad

[ttfkam]

Well, since the mysql_*, pg_*, sybase_* and so on functions use very similar syntax, try using sed.

Well since Perl DBI, JDBC, ODBC, and PHP ADO et al. work perfectly well, why not use the better solution instead of relying on sed after the fact?

But I think the question we should be asking is, why would you want your code to support a different database anyway?

Project requirements change. That is a fact. Your project may not be 99.9% selects anymore. Inserts and updates may become more important. Maybe you want to add a lot more concurrent database clients? In those situations, MySQL turns into a dog compared to other options. When one engine (or operating system) doesn't cut the mustard anymore, it's a question of whether the transition costs are tripled or not.

Then there's the case where you are not the end user, and the client already has a database they are happy with and/or have trained personnel to maintain it. In this case, MySQL is more expensive for them.

Most MySQL advocates say that they don't need stored procedures, views, triggers, etc. in the projects they do. Fair enough. But what happens when you come across a project that does need one of these? Oops! Sorry. You're married to MySQL.

What's that? Stored procedures aren't portable? Mostly that's correct, but they aren't all fundamentally different. In fact, Oracle's procedural language has more in common with PostgreSQL's default procedural language than MySQL has with the SQL92 and SQL99 standards. And porting stored procedures are a damn sight easier than trying to port the custom MySQL functions people write in C in a MySQL-only API.

MySQL fits your task the best? By all means, use it. But tying yourself inextricably to it? Better get the first-aid ready because sooner or later, you're gonna get bit in the ass for that decision.

You can add your fancy object oriented classes and methods, abstraction layers and sundry filibustering tricks all you like; but nothing will change the fact that, at the end of the day, sooner or later, you can't avoid the inevitable fact of having to get your hands dirty and actually manipulate some data.

I agree. I also agree that you don't need to use every feature and technique at your disposal on every project. But that's what MySQL requires much of the time. Your application layer grows in a couple of places when you don't have CHECK constraints in your database. You know what CHECK constraints are, right? They're one of the things that MySQL happily accepts in the CREATE TABLE query, but silently ignores because it's not implemented.

People say to rely on the app layer to make sure your data is correct and that when MySQL fails silently, it's your fault. If that is so, what happens when a project has more than one developer? Do you know the other's mind? Do they know yours? Gotta rely on good communication, hunh? Wouldn't it be better if the database threw an error during development so that you two were prompted to converse on the subject? Or are you one of those "good teams mind-meld" individuals who discounts that there are different skill sets and expectations on any project.

Just make your app layer better? If that is so, why not have a database backend that actually catches the errors so you can fix your app layer where broken?

Still want things more loose? Here's an idea: don't use foreign keys or check constraints. No matter what database you use, you don't have to use every feature. It's good to know that the features are there if you do need them though. MySQL is a leap of faith that you will never need anything more. There is a big difference between the two camps.