Why MySQL Grew So Fast 621
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.)"
Pretty simple. (Score:5, Insightful)
2. MySQL is easy to install and learn.
Re:Pretty simple. (Score:5, Interesting)
Re:Pretty simple. (Score:5, Insightful)
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. (Score:4, Funny)
Re:Pretty simple. (Score:5, Insightful)
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. (Score:5, Insightful)
> 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. (Score:5, Insightful)
What you say about disregard for SQL standards is true, see MySQL Gotchas [sql-info.de]. 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 [openacs.org], 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. (Score:5, Interesting)
Re:Pretty simple. (Score:5, Informative)
Just flat-out untrue.
I know - you can get it with its slower innodb file system now
Actually, Berkeley DB and InnoDB both support transactions, but InnoDB is more complete, which is also (of course) why it is slower. 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.
The great part is that you can still fall back on the MyISAM table type (which implements everything you need in terms of atomic inserts and updates, as long as that atomicity is not required across several statements) if you want that speed, because MySQL is modular enough to support that. Imagine that, a modular database... who'da thunk.
views
The lack of views is directly related to the lack of sub-queries, which is being addressed, but is still a questionable feature. Ultimately, I've never seen a query that used a sub-query that didn't actually need to be optimized through judicious uses of de-normalization. Views are just a hard-coded sub-query, and as such give tremendous flexibility to the programmer, but are nearly impossible to correctly optimize, and the performance bottlenecks aren't always obvious on the first pass.
Once sub-queries and views appear, I would still caution STRONGLY against their use.
So, if the non-ansi syntax isn't a big enough pain-in-the-butt
Well, that's really not fair. The syntax is no more deviant from ANSI than, say, Oracle or Sybase, but those databases' extensions have become so widly accepted that we don't think about how non-ANSI they are anymore.
Granted 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. My applications port fairly cleanly when I want them to, but in many cases, I've choses in the architecture to rely on some key features of MySQL that aren't standard, and I've benefitted greatly from having done so. Sure, I could port to something else later, if I had to, but it would be a pain, and I'd lose functionality.
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
It's not that nobody wants it, it's that most of the people who want it aren't actually providing reasons as such. They are just whining about x, y or z metric to which their pet database conforms, and MySQL doesn't. That game is not interesting. Transactions were required for certain applications, so they were added. Sub-queries less so, and so they will be added, but much slower. Things like full ANSI type compatibility are required only if a) you don't use an ODBC layer that translates types for you or b) you want to just take your native SQLServer app and re-point it to a MySQL database.
Stored procedures and other similar features are just bloat, and gain you no real advantage (other than making a bridge between languages, which is the wrong place to solve that problem... after all it doesn't help you with your network protocols...).
The bottom line is that MySQL is a fairly low-level database. If you want something higher level, cool, go for it. But, in programming langauges you don't say "C is useless because it doesn't have all of the features of ADA", you just use the right tool for the job. Why is this so different?
Re:Pretty simple. (Score:5, Informative)
Re:Note to the Mods... (Score:3, Funny)
(Disclaimer: I like MySQL.)
Re:Note to the Mods... (Score:5, Insightful)
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.
Re:Note to the Mods... (Score:4, Interesting)
I went to talk to the guy in charge because he got in his head to buy Oracle. I was trying to convinve him to use MySQL because of cost. He thought Oracle was the only way a company could operate.
Here's what actually happened:
(1) We settled on Sybase
(2) Took forever to get plans worked out and the software in place (it would have taken a matter of hours for me to get MySQL in place...)
(3) We never moved off our "temporary development" system that was a flat text file[1]
(4) Investors realized nothing was making it to market and too much was being spent, and company went under.
[1] The flat text file -- 100's of MB -- was being read several times each time the front page of the company website was loaded. And no, nobody ever got sybase in place behind it. After all, details like that don't impress investors.
Disclaimer: I now use postgresql. At the time ('99) postgresql still had some rough edges, but now it's the best database out there for everything that I use an RDBMS for.
Re:Note to the Mods... (Score:4, Funny)
(1) We settled on Sybase.
(4) Investors realized nothing was making it to market and too much was being spent, and company went under.
Heh... you could've just combined steps 1 and 4, and skipped 2 and 3 altogether, and the story still would've made perfect sense.
Re:Pretty simple. (Score:3, Funny)
Re:Pretty simple. (Score:5, Informative)
Interestingly, nowadays no one seems to remember that in '97-'98, there were basically three usable databases: Postgres95: sometimes difficult to compile and set up; mSql from Australia, which was popular, but was for non-commercial use only (thus they excluded themselves from many "markets"), and mysql, which at the time looked like a buggy clone of msql, but free to use.
Most of the people at that time usually heard about apache + msql, and then stormed over to apache + mysql. Me, I managed to get Postgres95 to work, and never longed for anything else :)
mSql, aka minisql tried to make a comeback lately, but I they botched their opportunity years ago with this "non-commercial use" stuff.
Re:Pretty simple. (Score:5, Informative)
I moved from mSQL to MySQL in 1997 for a PHP2 project, because of mSQL's licensing restrictions. It was trivially easy to move because the API was almost identical (add a y in all the function calls).
This dropin replacement for mSQL is what gave MySQL the start it needed in the web-backend market.
Why MySQL grew so fast: (Score:5, Funny)
2. Possible steroid abuse
Re:Pretty simple. (Score:5, Insightful)
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.
Why? (Score:4, Interesting)
MySQL gets a lot of heat from the DBAs here (and probably with reason), but it's kind of like bashing MS Access -- it's useful enough for most small businesses.
Re:Why? (Score:5, Funny)
Sorry if I sound bitter but MS Access has given me more than one headache.
Re:Why? (Score:5, Interesting)
The problems with Access is just that people often don't know what they're doing and create awfull legacy systems that they then expect to keep extending. That's a problem that lies between the keyboard and chair, not with Access.
If you want a database to complain about, complain about FileMaker Pro. It's an ugly, non-relational database that seems to ecourage people to make terrible, ugly systems.
Re:Why? (Score:4, Informative)
MS Access from hell (Score:5, Interesting)
Things got interesting when I loaded all 2 million rows of data (one per file) into the poor POS Access DB. It took over 8 hours (I left it running and went home; it was still running when I got back. Lo and behold, it accepted every row. Trouble started when I discovered that trying to save a query or report would send the machine into la-la land. So, I had to dump the DB for every tweak of the report. After a week of messing around (time "well spent," as using Oracle/MS-SQL would have saved at least 4 days of waiting for row population), I finally got everything working, and turned in the report (something like a 500 page PDF).
Naturally, they wanted to change the criteria and group by something else. "Sorry, but today's my last day," I grinned. "And it takes at least one full day to make any changes, assuming you got it right the first time."
Suck. Ers.
Re:Why? (Score:5, Insightful)
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.
My Theory? (Score:4, Funny)
They do not recognize "Postgres", or even know how to pronounce it. It sounds vaguely French and therefore un-American.
Yep, it is that simple.
inmy opinion (Score:3, Insightful)
Why MySQL Grew So Fast? (Score:5, Funny)
Picking the right tool for the job (Score:5, Insightful)
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 (Score:5, Interesting)
At this point anyone with RDBMS experience is rolling on the floor. THAT is why MySQL gets no respect.
Re:Picking the right tool for the job (Score:4, Informative)
I think the changes are pretty entertaining...
Don't miss this gem! (Score:5, Insightful)
"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:Picking the right tool for the job (Score:5, Interesting)
Pardon my ignorance, but what is it exactly that MySQL is good at that Postgresql isn't at least equal or better at? I'm all for using the right tool for the right job, but every time I've used at MySQL I've frequently encountered all sorts of crazy problems [sql-info.de] with no killer features to justify the change, that aren't already provided by both postgres and other databases. Maybe it's my application bigotry speaking, but I honestly can't see how MySQL is better at anything.
The only things I can think of are the familiarity aspect, for people who are already very knowledgable about MySQL not wanting to switch, and possibly some minor issues with moving database files around on the underlying file system.
I strongly disagree (Score:5, Insightful)
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 (Score:5, Informative)
I've never used MS SQL Server or Foxpro. But having very recently developed a project on two DBMS tracks (Oracle and MySQL) I can tell you that identical queries on identical schemata with identical data are provably faster with MySQL than with Oracle.
It has no row locking, no transaction support,
This is no longer true.
minimal cross-platform compatibility
Huh? I've successfully migrated several complex databases, and the associated applications, between MySQL servers on Linux, Windows, BSD, and Mac OS X with no problem. And I mean no problem; in most of these cases, I haven't had to make a single change to the architecture, data, or application code. Everything just works.
If you want to criticize MySQL, there are plenty of legitimate grounds to do so. (E.g., the lack of support for views, which in my primary job as a MySQL DBA drives me nuts at least once a week.) Don't just make stuff up.
Re:I strongly disagree (Score:5, Interesting)
...provided the MySQL server isn't under load. 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.
Re:I strongly disagree (Score:4, Insightful)
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 (Score:3, Insightful)
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, especial
Re:I strongly disagree (Score:3, Informative)
Re:I strongly disagree (Score:5, Interesting)
I call bullshit. In e-Week's tests, Oracle and MySQL were dead even under load [eweek.com].
Re:I strongly disagree (Score:3, Interesting)
Re:I strongly disagree (Score:5, Interesting)
Re:I strongly disagree (Score:5, Insightful)
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 (Score:5, Interesting)
How about 100%!?!? Ever hear of PostgreSQL? [postgresql.org]
ACID compliance, features that compare very nicely against MS-SQL/Oracle, foreign keys, triggers, transactions, embedded function definitions, the whole shebang.
About the only thing that it lacks (for free) is decent clustering/replication capability - and you can buy that fairly easily in the form of patches.
I've been using it for years, and it's a joy to work with!
Re:I strongly disagree (Score:3, Informative)
Re:I strongly disagree (Score:5, Insightful)
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.
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.
Re:I strongly disagree (Score:3, Insightful)
Re:I strongly disagree (Score:5, Informative)
Before all of the MySQL zealots jump all over you, I should point out that MySQL does have transaction support (with the proper table type, and so long as it's built in, and you're using a current enough version, and you made sure to tag your tables with the right syntax to make sure they are of the right table type, etc), and does cross-platform well enough (better than PostgreSQL, as much as I love that engine). I don't know about row-level locking, but I'm sure it can't be far now.
The biggest problem with MySQL is inconsistencies in both the engine itself and the development community. 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!" Only they then go and implement those features that developers "don't need". That would be fine, except that the implementation of the features often leaves something to be desired, and have too many quirks. For instance, I mentioned above that you can only get transactions and referential integrity if you're using the correct table type. However, that table type is not the default, and even if you do create your tables properly to take advantage of those features, MySQL doesn't fail if the table type is not supported, choosing instead to make your table an inferior type. Now you think you have transactional support and referential integrity because your database built just fine, but what you don't know is that your hosting provider didn't build that table type into their deployment of MySQL, and you really don't have those features at all. Good luck trying to figure out why your data is corrupted even though you had proper transactioning in your code.
MySQL has other problems [sql-info.de] as well. For example, if I want a column to be NOT NULL, I want any code that tries to insert a NULL into that column to fail. I don't want the engine to try to pick some default value for me. If I wanted a default, I would've added a default. That's why default constraints exist. By that same token, if I want a column to allow NULLs, I want to be able to put a NULL in the column. I don't want the current date/time instead of a NULL. If I define a column as auto-incrementing, I want to get an error if I try to insert something into that column. I don't want it to quietly succeed.
There's plenty more on that page, though most MySQL apologists will tell you either that the problem is fixed (which is fine, except that being fixed in the latest beta is far different from being fixed in the most widely-deployed versions from different hosting providers and such), or that it "will be in the next release". These are the same people that will tell you that stored procedures are unecessary, and anybody that thinks they are is stupid (or they'll tell you that the performance gains from being able to compile your SQL code is negligible, while completely ignoring all of the other benefits of stored procedures ... *COUGH*security*COUGH* ...). And so on. MySQL is fine for what it does, but it's not the end-all of SQL software. Far from it.
Re:I strongly disagree (Score:5, Insightful)
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 (Score:5, Insightful)
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.
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):
If you want to learn, get yourself a real RDBMS. Microsoft's desktop engine version of SQL Server is free [microsoft.com], and Oracle has free downloads [oracle.com] 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.
Because they were the first to support subqueries (Score:5, Insightful)
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 [mysql.com]. I want to start using it. And 4.1 has been out in alpha for over a year now [mysql.com]. 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 [rimuhosting.com]
Re:Because they were the first to support subqueri (Score:3, Interesting)
You forgot to add an important qualifier here - for a certain set of circumstances. MySQL is one of those products that is suitable for database content that isn't changing much - it's very fast reading from the database. The numbers change quite a bit when you're doing heavier work on the database, which is where Oracle & MS-SQL (or even PostGreSQL) come into their own.
Re:Because they were the first to support subqueri (Score:5, Insightful)
"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.
I like MySQL (Score:5, Insightful)
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!
I dislike MySQL (Score:5, Insightful)
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).
ASUG 2004 (Score:3, Informative)
JavaScript, DOM, and Page Reloads [webword.com]
Usability Interview with David Clark of TandemSeven [webword.com]
More Observations on ASUG 2004 [webword.com]
ASUG 2004 and RFID [webword.com]
I still prefer PostgreSQL (Score:5, Informative)
MySQL has grown up a lot though. Given how primitive and featureless it used to be it's gotten much better where the differences between the two have become smaller.
MySQL got there first and was "good enough" (Score:5, Interesting)
MySQL, even now, is actually rather sparse as database engines go: it lacks stored procedures, triggers, constraints, etc., in short many of the things that a serious DBA considers necessary in a database engine.
But the mission it was originally created for is a mission that's a very common one: a simple, network-enabled data store with a SQL interface. That it lacked transactional capabilities didn't really matter: it was good enough for what many people needed.
So its popularity exploded. In the free software world, there weren't any other contenders at the time that were sufficiently reliable or fast to do the job. PostgreSQL back then just wasn't fast enough, and tended to eat data. Not that MySQL was perfect in that regard, mind you, but at least MySQL gave you the tools to recover your data quickly in the event of a hiccup. PostgreSQL didn't -- it required you to do a full restore from backups, whereas MySQL let you use 'isamchk' to get you up and running quickly. That made a big difference to a lot of people.
Today the story is very different. PostgreSQL is at least as fast, if not faster, than MySQL in many situations, especially under load, and has essentially all the features needed to make it a "real" database: transactions, stored procedures, triggers, views, constraints, etc. About all it lacks now is built-in replication (there exist third-party solutions), nested transactions, and point-in-time recovery (a.k.a. archive logs), things which MySQL is not likely to get anytime soon.
Nevertheless, despite the fact that PostgreSQL is very much a superior solution in just about every respect, MySQL is more popular and thus has better third-party support. And it's thanks to the fact that it was in the right place, at the right time, with a "good enough" feature set.
Re:MySQL got there first and was "good enough" (Score:5, Informative)
The 3rd party replication solution was donated to the project a release or two back. It's now freely available. Having said that, internal code changes continue to take place to support better, faster, stronger replication capabilities in the future. No known ETA.
I believe nested transactions as getting some low priority cycles. No known ETA. It is nice to know that it is are on their radar.
Point-in-time recovery is being actively worked on and has been for some time. Expect it in the next major release or two. Probably will be in the next major release but may slip. It has slipped before. They elected to get better infrastructure in place so they wouldn't have to shoehorn it in. That's fine by me. That means a rock solid implementation with no major surprises down the road.
things which MySQL is not likely to get anytime soon
Too true. PostgreSQL is working on "enterprise features" and occationally someone stops to try to optimise here and there. As is, PostgreSQL is pretty fast. Performance is generally regarded to be in league with the big boys of the RDBMS world. PostgreSQL makes an attempt to adhere to SQL specifications. MySQL is pretty far from being conforming to any specification and is missing very basic RDBMS features. It will be years and years before MySQL is as feature rich as PostgreSQL is today.
I believe other items which are getting some attention include distributed queries and two-phase commits. Along those lines, the protocol that PostgreSQL uses has been getting attention to better support more complex communication requirements (two-phase commit, distributed queries, etc).
Lots of cool stuff is comming out of the PostgreSQL pipeline. Sadly, it just won't all be available tomorrow.
Re:MySQL got there first and was "good enough" (Score:3, Interesting)
Generally speaking, MySQL is wicked fast for a single user that only does selects. Once you leave that safety zone, it becomes a huge question mark for MySQL.
Want transactions and ACID? Performance just dropped. Want concurrent select/insert/updates? Performance just dropped a lot! It's fairly well understood that MySQL scales poorly.
I remember back in the day when Crays where king. Someone with a workstation came out
I've Tried PostgreSQL (Score:5, Interesting)
Re:I've Tried PostgreSQL (Score:3, Interesting)
Postgres also stores users in a table which is editable like any other table (this is nice, but obviously dangerous). You don't have to do it through SQL-ish commands. All of this is made very simple by PgAdmin though.
Simplest database (Score:4, Interesting)
People need to use SQL and need something simple and fast. Postgresql is not optimised for simple web applications out of the box.
sqlite I think came much later, but would have fit the bill and IMHO would have taken Mysqls place early on. I know I was looking for something like sqlite making my simple website and mysql seemed to complex.
MySQL, PostgreSQL, Oracle, MSSQL, etc.... (Score:5, Insightful)
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 (Score:5, Insightful)
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.
Comment removed (Score:5, Insightful)
Re:Yeah... I'm gonna sqitch from Oracle to MySQL (Score:5, Interesting)
I can easily think of a reason to go from Oracle to MySQL.
1. You don't have a database that needs Oracle.
2. Therefore downgrading will save you fat sacks of cash.
I've known someone who was fired for choosing Oracle. They suck an enormous amount of money into the project and it was far more database than they needed.
It's all in the name or is it the license? (Score:3, Interesting)
MySQL, maybe it is My-Ess-Que-Ell or maybe it is My-Sequel, but Postgresql? Postgr-ehz-Que-Ell? Postgreh-Sequel, Postgray-Que-Ell? (Does sound vaguely French.) Ugh! (Nothing against French.) Hate the name, but love the program. Although it seemed that there were more people choosing MySQL over Postgresql when I started evaluating the two, there were two key features that led me to choose Postgres(whatever) over MySQL
1) Views (How can you have a/an SQL database and not support views!)
2) Free license for corporate use
So, if I chose to work with MySQL I would have to give up using views. Also, if I wanted to use it at work, I would have to convince the boss to buy a license. On the other hand if I worked with Postgres(whatever), I got views, and did not have to persuade the boss to part with any money. So it was 2 minuses in the MySQL column, versus 2 pluses in the Postgres column and the Ayes have it. After MySQL changed their license, inertia kept me going forward with Postgres (and MySQL's lack of views and triggers).
Now that MySQL has a GPL version, it is less of an issue, but at first, it was free for non-profit use only (or so I understood), while Postgres was free for any use. It surprised me that so many would choose the non-free / less free license.
Views? Subqueries? Easy to move databases? (Score:5, Insightful)
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.
Are you kidding? (Score:4, Insightful)
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.
Re:Views? Subqueries? Easy to move databases? (Score:4, Informative)
Views represent first class relational objects that can be used in yet other relational expressions (just like physical tables can be used), like joins, etc. A view itself may represent a join of a data table with a security table (for example), and maybe used instead of a physical table. If specified correctly a view can be updatable.
"Bookmarked" SELECTs are *exactly* the kind of hack you have to resort to with databases like MySQL.
I stopped trusting MySQL (Score:3, Insightful)
.. the day it returned a date column with the value '2309-46-39'.
(that, retarded access control system and the random data corruption..)
All of these posts are leaving out mSQL (Score:3, Insightful)
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?
MySQL doesn't scale (Score:5, Informative)
Yes there are work arounds for the missing features. These work arounds are usually - do it in the application code. Yes I can do it in the application code, but that takes away many of the benefits of using a RDBMS in the first place.
To give you a quick idea. Our clients have complex MS SQL Server db's that hold a lot of data, all somehow related to each other. A few quick queries on my dev db gives the following results
1061 tables
1742 stored procs
1281 triggers
The database gets accessed in a lot of different ways. This includes, but is not limited to
C++ via ODBC
C++ via ADO
Delphi via ODBC
Delphi via ADO
JSP Pages
ASP Pages
Java Servlets
Perl Scripts
Access
If something new technology comes along we can use it on our db. Why? Because the database is kept consistent through the use of triggers, stored procs and key constraints. If you have mutliple ways to access a database, you do not want your bussiness rules to sit in application code, you want it on the db.
Re:MySQL doesn't scale (Score:5, Interesting)
Are they liable for any consequences of losing data? No. Do they need to keep an audit trail? No.
None of these things have anything to do with how "big" a database is.
The kicker is, slashdot isn't very big at all! Can you come back to a story a few months later and comment on it, or even view it according to your preferences? No, it's been removed from the database because and put to static HTML because MySQL doesn't scale!
Right On (Score:5, Insightful)
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.
simplicity translates to speed (Score:4, Insightful)
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???
Often textfiles are perfectly sufficient. (Score:4, Insightful)
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.
Easy answer... (Score:5, Insightful)
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:It's too bad (Score:5, Insightful)
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.
Re:It's too bad (Score:5, Informative)
When Microsoft realized that they had backed the wrong horse, they had to come up with their own Internet strategy in a hurry, or be left behind. That is why early versions of IE were such hack jobs. And for some years, other browsers still did more to raise awareness of the Web. But once the Web was established, nobody bothered to install other browsers -- why bother, when Windows came with one? Between that and Netscape's declining interest in browser development...
As for MySQL: when the Web exploded, web developers needed data engines that didn't cost a lot and were easy to understand. The excluded all serious SQL servers. I'm not sure why nobody picked up on simple ISAM systems like Berkeley DB -- perhaps they all had licensing issues. Anyway, MySQL was something they could use for free, it was easy to understand, and it was powerful enough for most web applications. You can't do the complicated operations that separate a true RDBMS [webopedia.com] from a simple dataset library -- but most web developers didn't have the skill [slashdot.org] to use these operations anyway.
Re:It's too bad (Score:5, Informative)
That, buddy, is double-plus untrue. The second part anyway.
Re:It's too bad (Score:3, Interesting)
But no-one used that "HORRIFIC" web browser, except to download Netscape. At least util NN4, when Netscape shot themselves in the foot (and took off most of their leg in the same shot). After that (well, until recently) Internet Explorer was a better browser - far from "HORRIFIC".
Re:It's too bad (Score:3, Interesting)
Re:It's too bad (Score:5, Funny)
Oracle, of course.
Love,
Larry Ellison
Re:It's too bad (Score:5, Insightful)
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 (Score:5, Insightful)
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:It's too bad (Score:3, Insightful)
Sorry, but practical trumps theoretical every time.
Cheers.
Re:It's too bad (Score:3, Insightful)
(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:It's too bad (Score:5, Insightful)
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?
Re:It's too bad (Score:3, Insightful)
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 s
Re:It's too bad (Score:5, Informative)
At anyrate, the better way to look at MySQL is the kind, gentle introduction to SQL until your needs drive you to a grown up database. For my dev team, we just needed a backend for our existing Bug database without paying exhorbitant charges to IT Support to use MS SQL. MySQL so fits the bill.
bullshit (Score:5, Insightful)
Quality product. That is why it is popular. Perhaps you should research your argument before posting a flame next time.
Re:bullshit (Score:3, Insightful)
Re:It's too bad (Score:5, Insightful)
Now the feature set is stable, it can always be re-implemented in a more "beautiful" style. 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:Why MySQL Grew So Fast (Score:5, Interesting)
Being free is one thing, being fast, elegant, easy to use and administrate, having a pretty clean security record, AND being free, those traits are something to write home about.
Yes, we know, we know, MySQL isn't 99.99999% standards compatible. Well that's something to work toward, but at the time, it was quite usable for anyone who needed to use it, therefore it was used. If the internet needed transactions for things as simple as serving dynamic pages, then MySQL probably wouldn't have prospered, but it did have what the developers needed, when they needed it.
Re:MySQL for Sybase admins... (Score:4, Insightful)
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:The Truth About MySQL (Score:3, Insightful)
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.
Re:A Good DBMS for Beginners (Score:4, Interesting)
If, on the other hand, your objective is to learn MySQL, then by all means, learn by using MySQL. MySQL is well known for teaching people enough to be very dangerous but never enough to produce clean, fast, effecient SQL code for other RDMBS. In other words, learning on MySQL will probably teach you how to make the least effective queries for other platforms.
Re:Win32 Binaries (Score:4, Insightful)
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.