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

It's too bad

[PretzelBat]

It's too bad that poorly designed products like MySQL become the standard simply due to their wide adoption.

Re:It's too bad

[Anonymous Coward]

Yup, exactly like PHP that is probably the main cause of its popularity

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.

Re:It's too bad

[pagan26]

Like Access 2003 it the cream of the crop? I use Access at work and I would gladly change over to MySQL. Better yet, what would you recommend?

Re:It's too bad

[Anonymous Coward]

Atleast it's a free product that has been adopted as the 'standard' unlike any poorly written MS product.

Re:It's too bad

[Anonymous Coward]

Better yet, what would you recommend?

Oracle, of course.

Love,

Larry Ellison

Re:It's too bad

[Anonymous Coward]

What? Netscape was *far* ahead of Microsoft in getting a browser out. MS missed that market by a longshot. Not that Netscape didn't suck. :)

Re:It's too bad

[mabu]

As opposed to what? Oracle? ROFL!

Re:It's too bad

[ciroknight]

Maybe they were far ahead, but now when you sit down at 97% of computers in the world and look for a web browser, what are you going to find? Internet Explorer. By winning the browser war, they earned their place on everyone's desktop, and earned their place as everyone's pit of insults. MySQL, in a way, has suffered the same fate.

Re:It's too bad

[Anonymous Coward]

You said the reason the web took off was because of IE. It was actually quite popular well before IE was ever forced down our throats.

Re:It's too bad

[ciroknight]

well let's think of it this way, what would have happened if microsoft never wrote their own broswer, but barred netscape from producing theirs? (yes, microsoft didn't have as much leverage as it has now, but it still could have bought netscape long before AOL did..) simply, competition drives innovation. And usually the competitors both have their weaknesses..

Re:It's too bad

[Anonymous Coward]

One word: PostgreSQL. Also free, but makes mySQL look like a toy.

Re:It's too bad

[Anonymous Coward]

Fair enough, and I'm not saying you're wrong. The web is obviously more popular today because of IE than if it had never existed. However, it was still popular and quickly becoming more so before IE was first released.

Therefore, the popularity of the web cannot be laid squarely on Microsoft shoulders, and I'd say it's not even primarily Microsoft's doing as Microsoft never got into the market until after they realized what they were missing.

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: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?

Re:It's too bad

[Anonymous Coward]

Dear mabu:

Yes.

Love,
Larry Ellison

Re:It's too bad

[Anonymous Coward]

It's your own fault that you don't know PostgreSQL. MySQL is fine if your sites are toys. But when you need things like triggers, use a real database like PostgreSQL.

Hell, it wasn't till relatively recently that MySQL even got TRANSACTIONs for god's sake.

Re:It's too bad

[holloway]

Use Access 2003 as your frontend to MySQL

Re:It's too bad

[MagikSlinger]

Maybe for you. For me, it was dead easy to set-up, quick to learn and the @#$! thing WORKED out of the box! I can't say the same about Posgress.

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.

Re:It's too bad

[Anonymous Coward]

It's a fucking mess, and that's all there is to it.

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.

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?

Re:It's too bad

[Bob Uhl]

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."

Because, until fairly recently, it was quite likely that one's data would eventually become corrupt. This is no longer the case, but MySQL still doesn't offer all that PostgreSQL does.

Re:It's too bad

[fm6]

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.

Say what? The web had lots of momentum as early as 95. At which time Microsoft was still in denial about the whole Internet thing. Which is why Windows 95 came with a lot of MSN software and libraries (then based on proprietary protocols) and no TCP/IP stack at all. That fact that MSN began as an "online service" (in the model of pre-Internet AOL and CompuServe) rather than as an ISP indicates how little interest Microsoft had in the Internet in general and the Web in particular.

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 from a simple dataset library -- but most web developers didn't have the skill to use these operations anyway.

Re:It's too bad

[Anonymous Coward]

Slashdot uses MySQL. Personally, I wouldn't class slashdot as a "toy" site.

Re:It's too bad

[Anonymous Coward]

You do not get the point mister. PHP is a highly customizable, fast-rendering and highly scalable platform. You should give more respect to these fine working American developer teams and help them a bit if you have better ideas than them. They are wise enough to take them under consideration.

-BW

Re:It's too bad

[PretzelBat]

Death to slashdorks. I hate this stoopid place. Die die. Long live Microsoft. Death to Linux dweebs.

Re:It's too bad

[zgornz]

Well let's think of it this way, you could say that same thing about /anything/. What if Coke never made Coca-Cola and at the same time bough Pepsi and stopped them from making Pepsi. The world would lack brown beverages.

Microsoft definitely had an effect, but not because they didn't buy netscape and cancel the browser project and then made IE. They had an effect because they made their browser FREE (forcing Netscape to follow suite), and bundled it with almost every desktop computer sold.

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.

Re:It's too bad

[Anonymous Coward]

Windows 95 did come with a TCP/IP stack and a PPP dialer. In fact "get on the internet" was probably the #1 thing that sold Win95.

Re:It's too bad

[Anonymous Coward]

The world would lack brown beverages.

No, because the world would still enjoy drinking my liquified shit.

Re:It's too bad

[Anonymous Coward]

Yeah, it would be a real shame if your comment disappeared.

Slash also has a big data caching layer in Perl.

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:It's too bad

[drsmithy]

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.

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

[Anonymous Coward]

Hey Bernie:

PHP is a complete frigging mess. People use it because it's a free alternative to ASP, and there are many open source retards that write free PHP software.

However, these same retards are trying to use PHP for something it was just not meant to do. It doesn't scale well at all.

ASP is a much better product, but you get what you pay for.

Re:It's too bad

[Anonymous Coward]

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?

You make a very valid point. In ASP I would only have to change about 3 lines in the entire script to switch databases! Not so with PHP!

Re:It's too bad

[Anonymous Coward]

Our critiques are mutually exclusive my very dear sire...

- Bernard Wolkacz, Ing. PhD Wien

Re:It's too bad

[cscx]

Which is why Windows 95 came with a lot of MSN software and libraries (then based on proprietary protocols) and no TCP/IP stack at all.

That, buddy, is double-plus untrue. The second part anyway.

Re:It's too bad

[Anonymous Coward]

PHP is a highly customizable, fast-rendering and highly scalable platform...

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 ;-).

Re:It's too bad

[gnuman99]

That is because they are using it on systems that do not have integrity checking in hardware.

Personally, for mission critical stuff I would not trust MySQL or Oracle or PostgreSQL. I would just add another column with a MD5 sum or whatever. Then integrity can be checked in a reliable fashion (by mysqlf).

Re:It's too bad

[Anonymous Coward]

One thing I will say about IE is that it was the first browser that felt stable enough to use for "real work" -- intranet applications and the like.

Netscape was OK when the web was in its toy stage, but you would never have seen as much commercial adoption with that crashy POS.

Re:It's too bad

[localman]

Wow, that's really overkill unless lives are at stake. If you're just dealing with millions of dollars (like I am) MySQL has shown to be perfectly acceptable. We just passed multiple financial audits.

Cheers.

Re:It's too bad

[GooberToo]

I'm not sure about Oracle, but I'd be surprised if this isn't true for it too. That is, PostgreSQL has some type of checksum or hash or something, I forget which, on data pages. So, if you have a corrupt row, it will be caught at the page level.

Doing it your way, on PostgreSQL and probably Oracle too, is adding extra work which is already being done, AFAIK.

Re:It's too bad

[Anonymous Coward]

IE won but not in a war. What war? IE was preinstalled and the majority of customers didn't care what it was as long as it displayed web pages. Netscape never stood a chance. And sigh... WMP will now repeat the story. In another non-war.

Re:It's too bad

[GooberToo]

You know, I've read this soo many times from MySQL users, talking about PostgreSQL. I'm constantly amazed. There is a readme file while walks you throught it. There is online documentation which walks you through it. There is an online administrators guide and WIKI documentation available which not only walks you though it, but adds insights and additional comments.

Can someone please tell me why so hard about reading 5 pages, or so, of instructions?!?!?

Re:It's too bad

[localman]

Actually, MySQL (with InnoDB) has this as well. Each page is checksummed and any error brings the DB down. I actually had a bad memory chip once that triggered this problem a few times before we figured it out. That sucked big time... but no data was lost that we can tell.

Cheers.

Re:It's too bad

[Anonymous Coward]

Plus, it was fast and everybody chose speed over the true purpose of a database that consist of the ACID test (Atomicity, Consistency, Isolation, and Durability,) which PostgreSQL had.

Re:It's too bad

[Anonymous Coward]

who gives a fuck
not everyone

oh and mysql isnt acid complient, you forgot that one,
cause a lot of people dont give a shit either.

it works for somethings, not everything.

but i guess postgresql is the save all right?

Re:It's too bad

[GooberToo]

Thanks. I didn't know that. I'm honestly not surprised as it is simply, just the right thing to do.

IIRC, Postgresql also has checksums (or whatever), elsewhere within various pages and data structures, but the page level is the only I mostly clearly recall.

Re:It's too bad

[mcbridematt]

I agree with the AC post above. I've used PostgreSQL before and I love it. Try reverse engineering (get lost billy) and replacing a [proprietary] MS SQL based app that uses cross-table relationships heavily with a MySQL based app. It was easier with PostgreSQL.

Re:It's too bad

[Anonymous Coward]

There's nothing theoretical about getting wrong answers (rather than errors) because you left a table name out of LOCK TABLES and started seeing incomplete updates, or used a FOREIGN KEY or CHECK constraint that your configuration silently fails to enforce. In practice most people don't notice because their amateur data models don't contain enough redundancy to tell them when their database is lying to them.

Re:It's too bad

[ab_iron]

Maybe he meant to say that MS did not install a TCP/IP stack by default. ab_iron

Re:It's too bad

[imroy]

Which is why Windows 95 came with a lot of MSN software and libraries (then based on proprietary protocols) and no TCP/IP stack at all.

Bzzt, wrong. This is getting offtopic, but Windows 95 was in fact the first version of Windows to be shipped with an IP stack from MS. Before then it was Trumpet Winsock or a number of other commercial stacks. You're right about the focus on the original MSN service though. And despite MS's attempts to rewrite history, IE only started shipping with Windows 95 OSR (OEM Second Release) in '96 or '97 (can't remember exactly).

Re:It's too bad

[Arcanix]

What rock have you been living under? Have you ever by chance heard of a program called Excel?!? Most robust DB product on the market as far as I know, and I know a lot.

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.

Re:It's too bad

[Lussarn]

Thats not PHPs fault. You can write database independent code in PHP to.

$shrtFunc = $DB->get_ctype() .'_query';
$res = @$shrtFunc($sql, $DB->get_conn());

This are a couple of lines from the database library I use. As you can see the value returned from $DB->get_ctype() can i our case be either mysql or sybase. And it's very easy to add support for more.

Re:It's too bad

[Anonymous Coward]

Yes, and fairly often the DB server goes down and you can only view static content. Slashdot isn't exactly the greatest marvel of software engineering in the world.

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:It's too bad

[twaltari]

You are absolutely right. However, what PostgreSQL really needs to become mainstream is a native win32 port with point-n-click installer. This has been scheduled for release 7.5 or 8.0.

Re:It's too bad

[ozbon]

For the gui DB design tool (partic with apache + php) the option of choice would be PhpMyAdmin.

Re:It's too bad

[ozbon]

Now _that's_ funny. I've worked with so many organisations who think that Excel is a database, and really don't see why they should change to something else, regardless of whether it's Access, MySQL, PostGre, Oracle, or whatever.

It's only once you've shown them a few things that are easier in anything other than Excel that they begin to understand - and in some ways that's why MySQL is useful, it provides a cheap/free alternative to investing in Oracle etc., and is useful for demonstrating the basics of why a DB is better than a spreadsheet.

Re:It's too bad

[lee7guy]

Get your facts straight.

The world wide web had already taken off, thanks to Mosaic and Netscape. The company making internet explorer was bought by microsoft when they realized they had missed the train due to Bill Gates' declaration of internet as irrelevant. (Remember, "microsoft network"?)

Re:It's too bad

[lee7guy]

I guess you arrived late in the game. Up until somewhere between version 4 or 5 of internet explore Netscape were generally considered the most stable and better browser of the two. The only reasonable use of internet explorer back then was as a tool for downloading Netscape.

Re:It's too bad

[Anonymous Coward]

That's very interesting, because given the choice I would use notepad. That's what 3 years of HTML, PHP, and MySQL editing will teach you. Simplicity is wonderful.

Re:It's too bad

[Betcour]

If you can do that then I would guess you don't do many queries, and they aren't very complicated either. Switching databases usually involve checking SQL query and rewriting some of them to fit the new database idiom, because no database seem to agree on how to interpret SQL.

Re:It's too bad

[cshark]

The day PostgreSQL is even half as well supported as mySQL, maybe I'll switch. My issue when adopting stuff like this is what tools are available for it? I could care less about how poorly designed it is. It works. It's well supported by the community and other software vendors. You people are bit snobs.

Re:It's too bad

[Metasquares]

Any language that allows includes to be passed both variables and URLs is not good in my book. I've seen many a cross site scripting exploit used against PHP scripts (most of them in free message board systems), and most of the time it results in the attacker executing arbitrary commands as whatever user PHP is running as. Yes, the script itself can verify that its information is correct, or at least exclude the malicious information, but that burden shouldn't rest on the scripter every time he wants to include something, IMO.

Re:It's too bad

[Eivind Eklund]

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?

In a way. The question is "What is good enough?" and "Solves what problems how well?". For me, the design problems in a lot of commonly used software (MySQL, perl, the pre 2.6 Linux VM, autoconf, the FreeBSD mergemaster tool, the Windows registry, the language SQL, ...) have bitten me, and in some cases keep biting me on a daily and weekly basis.

Of course, each of these have strengths that have made them be major players in their niche in the first place. This include

• Being available at the right time (here simplicity of design helps)
• Working stably according to documentation (here simplicity of design helps)
• Making the user feel (s)he can "grasp" the system completely (and here simplicity and obviousness of design choices helps immensely)
• Ease (or perceived ease) of starting to use the product
• It continutally being (perceived to be) cheaper to work around the flaws of the product than to switch to another product
• Having enough problems that people ask around about the product, thus creating "buzz", without these problems being fatal or being perceived to be the product itself.
• Having enough problems that people feel an emotinal investment in the product (and keeping each investment small enough) that the cost of switching is overestimated. ("I've spent hundreds of hours working out small quirks that are Linux-specific, and now you want me to switch to a different system? NO WAY!")
• Having legacy code available, so working around the flaws are still cheaper than re-implementing or migrating (at least with a day-to-day perspective)

Note that I do explictly NOT list being a more efficient or better system overall. People are generally reluctant to take any larger investment of time than they have to, even if that increase overall productivity. The gains have to be perceived as large and fairly immediate before taking the time investment.

So - in my evaluation, "poor design" is important. It gives continious costs *and those costs tend to emotionally bind the users to the product with the flaws*. However, poor or good design is not important to whether a system gets adopted or not. , if it isn't so horribly bad that this is immediately obvious.

And to get back on topic: With MySQL, what I really hate is that it make up data in order to make sure inserts pass. This has made much more trouble for me than any of the other issues - the other issues are easy (though a lot of work) to work around and keep in mind, but accepting bad data requires a ton of verification at the DB-interface layer of the application - and this is non-obvious, and have not been implemented in a lot of code I have had to work with :-(

Eivind.

Re:It's too bad

[Anonymous Coward]

lol

Re:It's too bad

[MagikSlinger]

This is why I chose MySQL or PostgreSQL. I needed it to run on Win32 natively without Cygwin. The problem was the install. Using PostgreSQL (which I used in another application) is not a problem. Hell, it's even easier than using MySQL. It's the install on Win32 that was the pain.

But if the installer is in the future, PostgreSQL will rightfully take its place at the head of the Open Source databases. I've used PostgreSQL, and I know how good it is. It has a lot of very nice features (not just the SQL syntax). What it came down to was the ability to install it and get it running quickly.

Re:It's too bad

[MyHair]

I'm not sure why nobody picked up on simple ISAM systems like Berkeley DB

Very good point. It was only recently when I was figuring out the backend to OpenLDAP that I realized the potential and suitability of what I called "hashing databases" for web content and much simple content. Heck, even MySQL realizes it because they simply put a SQL engine on top of a BDB database.

In retrospect, Perl hackers love hashes and I think they use them in a similar way to BDB.

My guess is that for most computer newbie types database was synonymous with SQL, and we wanted to work with the cool stuff.

Re:It's too bad

[Anonymous Coward]

I build Postgresql on my Linux 486 years ago. Not one bit of trouble doing installs on lots of computers since. I don't understand the complaints about install troubles.

MySQL however recommends not compiling, but using pre-built binaries because of limitations and compatability problems with compilers and libraries. This is very undesirable from my point of view.

Re:It's too bad

[chez69]

gee, in java I just use a different connection object that about 99.999% of the code won't change.

Re:It's too bad

[Anonymous Coward]

>> Windows 95 was in fact the first version of Windows to be shipped with an IP stack from MS

Your correction is incorrect. Windows For Workgroups 3.11 shipped with TCP/IP (no PPP dialer), as did every version of Windows NT.

Re:It's too bad

[Anonymous Coward]

> I guess you arrived late in the game

Sort of, my first browser was Netscape 0.9. Never used Mosaic much.

> between version 4 or 5 of internet explore

That's what I'm talking about -- circa 1998, when intranet and "b2b" applications started becoming really popular. No version of Netscape matched IE 4.01's stability until 7.x which shipped last year. Lots of people use web apps 8 hours a day for business critical tasks -- never would have happened with crappy NS3 and NS4.

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.

Re:It's too bad

[Anonymous Coward]

It never taught you that line numbers are a very important feature?

Re:It's too bad

[ttfkam]

Good post. I especially like the McDonalds analogy. Can I use it in the future?

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)

This is funny when viewed with your "cheap shot" earlier. When developing for yourself, you can use whatever is easier at the time whether you are an experienced DBA or not. When developing for others, the clients are saying, "We can't do good database work. That's what we want you for." They are paying you to be "aware of the logical capabilities of the relational model." Only they of course will never say this. They will simply say, "I want a reliable system." It's up to you as a developer to translate.

It's like when someone says, "I want my web page to be easy to use." They usually mean, "I want my web page to always stay up, have a logical, easy to navigate structure, be consistent, and yet deal with my color-blind sense of style and gaudy logo." Do you make a web site like someone who has never made one before (because the client doesn't know, why should you?), or do you follow all of their guidelines and yet still try to make one that can actually stand the test of time?

People pay you to be more knowledgeable, not as knowledgeable or less knowledgeable than they. This is not to say that you should disregard their desires. This is saying that when they can't tell the difference, use the better materials just because you know better.

Re:It's too bad

[Lussarn]

Thats exactly what you can do in PHP too you know. The code was taken from a database class.

Re:It's too bad

[ttfkam]

Of course it's incredibly useful. A horse and buggy are much better than trying to get around on foot all of the time.

But you are completely ignoring the fact that a wide variety of bicycles, motorcycles, cars, and trucks are available.

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.

Are they >95% select statements? That's probably why. If your projects were more insert/update oriented, you wouldn't be quite so glowing in your praise. It also raises the question: have you used anything else? Have you used another RDBMS package or are you simply assuming that because MySQL was easy to setup that all others must be harder?

Try out PosgreSQL for a short time. It's free as well. Get any one of the books for PostgreSQL and go through the exercises/examples. Trust me. If you are coming from only MySQL experience, you will have quite a few "I didn't know that was possible" moments.

The worst thing that'll happen is that you will choose MySQL based upon an informed decision related to the job at hand. The best thing is that you'll have expanded your toolkit and opened the door to a much greater variety of database engines. (PostgreSQL is more like all of the other popular RDBMSs than it is to MySQL.)

Re:It's too bad

[frodo from middle ea]

This reminds me of the days, I was learing java , before starting to use Linux.

I had win 95 + Office 95, and I wanted to learn JDBC programming, so here is what i did.

Create a Excel spreadsheet and used MS ODBC + MS Query to define a query on the spreadsheet. Juse Sun's JDBC-ODBC engine to query.

That was the best JDBC self training I ever got, after that Oracle, Sybase etc were piece of cake.

Re:It's too bad

[localman]

Well, that's why we were audited. And we passed with flying colors. Apparently we're not getting too many wrong answers.

Hmmm... redundancy in datamodeling? I thought normalizing data reduced redundancy?

In any case, we have cross checking scripts that monitor data integrity and they do more than could be reasonably done via SQL anyways.

Cheers.

Re:It's too bad

[localman]

I find your attitude (and most MySQL trashers) very interesting. My company is accomplishing amazing things with MySQL. Moreso than most companies ever accomplish regardless of the DB they use. But somehow you feel the need to save me. Anyways, I'll answer your direct questions:

We run about 75% selects. Our 24 hour average is bit over 200 queries per second.

I have used Oracle extensively. In fact, that's where I learned SQL and DB design. I know all about triggers, foreign keys, PL/SQL, etc. I prefer to build with MySQL.

I know it must really bug a lot of people that such a simple 80/20 database solution is proving to be the best tool for the job to so many people. Sorry.

Perl, ugly as it is, is more useful to me than Java, too.

Cheers.

Re:It's too bad

[ttfkam]

I'm glad the solution is working well for you. Best wishes to you.

It doesn't bug me that your solution works for you. The fact that you know the difference between databases and still find that MySQL is the best tool for the job speaks volumes. I am curious as to what aspects of MySQL you find to be superior to Firebird or PostgreSQL? I would honestly like to hear them.

I'm also curious about the 200 queries/sec. You have a setup with 50 inserts, updates, and deletes per second? I am indeed impressed.

Perl, ugly as it is, is more useful to me than Java, too.

Don't know where this came from, but good on you. If you're taking in values, running matches and substitutions, running queries against them, and sending off again, Perl is a great tool. I never would have thought ill of you for it.

Re:It's too bad

[fm6]

I realized the potential and suitability of what I called "hashing databases" for web content and much simple content.

Actually, there was a time when you didn't call something a database unless it had some serious data-management function. ISAM files weren't considered databases, just files that supported non-sequential access. Over the years, the term has been applied to just about everything that has any features of a DBMS. The most extreme case was Ashton-Tate's old FrameWork product, which included a "database" that was nothing but a specialized spreadsheet. And it had to fit in DOS memory, so your "database" couldn't be more than 500K!

In retrospect, Perl hackers love hashes and I think they use them in a similar way to BDB.

Not similar, exactly the same. Perl treats a BDB file as a kind of hash.

Re:It's too bad

[ttfkam]

But if the installer is in the future, PostgreSQL will rightfully take its place at the head of the Open Source databases.

Perhaps that used to be the case, but I don't believe that to be true anymore. Don't get me wrong, I prefer PostgreSQL over MySQL every day of the week and often on holidays (I run *nix servers). But the fact is that more ISPs and applications write directly to MySQL and its own peculiar set of bugs.

More apps means more MySQL installations. More MySQL installations means more apps. Wash. Rinse. Repeat. It's the same pattern followed by MS Windows years ago.

Inertia is not altered by better products; It's reversed by the introduction of "killer" features that are simply unavailable elsewhere. PostgreSQL has the features, but unless people know and understand what they are, they will not be determining factors, Win32 or no Win32.

Re:It's too bad

[localman]

I'm glad the solution is working well for you. Best wishes to you.

Okay -- sorry if I got a bit snippy there :)

I am curious as to what aspects of MySQL you find to be superior to Firebird or PostgreSQL?

I don't know enough about those RDBMSs inparticular to say. I like MySQL better than Oracle because it's cheaper and easier to administer. I am familiar with the more advanced features that MySQL lacks, but these have not been an issue in my experience. Since it meets my needs I haven't searched for something different. Note: I am using InnoDB tables which offer transactions, and more importantly to me row-level locking for higher concurrency.

My "best tool for the job" rating includes the fact that it is already doing the job; which is not trivial. I certainly wouldn't call anyone a fool for using PostgreSQL for a similar task, but I don't see any compelling reason to switch. If I had started with PostgreSQL (my research indicated it wasn't fast or stable enough at the time) I might be singing its praises today instead of MySQL.

My aim is not to convert anyone else to use MySQL, but rather to shoot down the meme that MySQL can't be used for real work, which comes up in every MySQL thread on slashdot.

I'm also curious about the 200 queries/sec. You have a setup with 50 inserts, updates, and deletes per second? I am indeed impressed.

Whoops - I made a mistake there: we average 81% selects -- I was looking at the current (2 second) number as opposed to the uptime average when I posted :/ Still, yes, our system often hits 50 writes per second (and higher), though that is not maintained.

This is for Zappos.com. I'm the lead engineer there, and it has grown faster than I could ever have imagined. We actually run two DB's, a primary and a replicated server. Both run on the beefiest Opteron systems you could buy last year. The primary averages 186 qps with 81% selects, and the replicated server averages 33 qps with 26% selects -- though that's misleading because it has to do all the same writes as the primary but handles only a portion of the slower reporting queries.

Most of our writes are for our recommendation engine, which tracks user viewing habits in real time to offer shoe suggestions. This feature is not promoted on the site yet, though it is live in testing.

Perl is a great tool. I never would have thought ill of you for it.

Heh... I just threw that in because I've often been told that Perl is a doomed language by the same people who tell my MySQL is a doomed database. As I said in an eariler post somewhere, I lean towards the "if it ain't broke don't fix it" school. It has served me pretty well thus far, but I still bump heads with the purists from time to time ;)

Thanks for you thoughtful replies.

Re:It's too bad

[ajs318]

You can run MySQL and Postgres on the same machine, as they use different ports by default; so the situation is not quite as dire as you're making out. I would also hazard a guess that somebody somewhere has a My-to-PG syntax translator or even a set of patches to the Postgres source that make it accept MySQL's slightly-unusual quoting syntax.

The points you raise are all valid, but only in a minority of cases. In probably 8 cases out of 10, MySQL provides everything the user is going to want. And if someone's application is beginning seriously to outgrow the capabilities of the database backend, isn't there a chance it might actually benefit from a complete, from-the-ground-up rewrite?

I feel the situation is best illustrated as follows.

PHP Programmer: Look at my new improved speedy potato peeler!
Java Programmer: Does it peel apples as well?
PHP Programmer: No, it only peels potatoes. Nobody in my family likes apples, so I didn't bother peeling apples.
Java programmer: Well, then; if it's hard-coded to work with only one kind of vegetable, it's obviously crap.
............... later ...............
PHP Programmer: I have redesigned my potato peeler to peel apples, although it now takes half as long again to peel a potato compared to the original version.
Java Programmer: Well, that's a start, I suppose. So does it cut up the potato into chips after peeling it?

If it makes you any happier, I'd ditch MySQL in a heartbeat if I thought something else would be better in a particular application. And that probably would be Postgres, since we have a "no source, no sale" software procurement policy around here.

Re:It's too bad

[ttfkam]

First off, where did Java vs. PHP come from in this thread? If you look at my list, it includes PHP ADO. My point here was that if database abstraction is just as easy and just as much code as hard-coding a particular database, why code for just one? I don't know what kinda crack you're smoking with that potato peeling analogy.

Pretty simple.

[Maul]

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

Re:Pretty simple.

[ciroknight]

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.

Re:Pretty simple.

[doktorjayd]

.. at first i read '1. MySQL can be insulted without cost... :)

Re:Pretty simple.

[Anonymous Coward]

we don't care. Next time keep your useless comments to yourself. It is idiotic comments like that which obviously got your karma down to 0 in the first place. Get it?

Re:Pretty simple.

[DAldredge]

3: MySQL SILENTLY ignores portions of the SQL spec and doesn't inform the applicaion that it doesn't do what the apps need it to.

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.

[doktorjayd]

.. gotcha.

stay anonymous.

seems to work for you.

Re:Pretty simple.

[Anonymous Coward]

Sorry, just doing a bit of trolling. Nothing personal

Re:Pretty simple.

[Anonymous Coward]

7. Slow on large data sets.

8. Known to corrupt data.

9. Unportable.

10. Lack of real features.

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.

[MP3Chuck]

3. ??? 4. Profit!

Re:Pretty simple.

[Anonymous Coward]

7. ???

8. Pwofit!

Re:Pretty simple.

[Chexum]

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.

[Anonymous Coward]

7. Lots of great support with PHP and Perl

Re:Pretty simple.

[Ignominious Cow Herd]

7. And it has the words "Don't Panic" on the cover.

Re:Pretty simple.

[Jack Porter]

Parent is dead right.

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.

Re:Pretty simple.

[Anonymous Coward]

7. Less Filling

Re:Pretty simple.

[LousyPhreak]

while i agree with you about mysql not having advanced sql features you missed one big point:

its absolute fool-proof (especially for web apps, which is imho its primary use).

anyone with basic understanding of php and a bit sql can throw together a working installation of mysql, add mysqladmin and he can develop web-apps in no time.

i've now done 4 months of writing asp and sql server stuff and i must say i dont understand anyone going this path. admittedly i (have to) use sql server 6.5 on a win2k server but besides .net i dont think that much has changed.

the problems range from simple things like having to close EVERY query done or you'll run out of connections (sure this really should be done but why gets php around this while ado does not),
needing to select the fields in order of use in the app (if you need blobs),
obviously no client side caching (if you use the blob 2 times with another field in between it fails too),
failing an sql query fails the whole app (you sure can change that but error handling is definately not the strength of ado/sql server),
having dates stored in the language of the server (means if you insert a date value from a string the resulting date depends on the language of the sql server --> 21.04.2004 only works on servers with non-english dates),
oh and last but not least an absolutely brain dead escaping pattern with no (atleast i know of none) built in function to do it automatically for you

so especially for web apps (and there especially with php) mysql surely blows most other combos (not sure on that but if you found something better i'd like to hear about that).

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.

[yulek]

and don't even get me started on table locking...

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.

[Betcour]

7. Has native, free, easy to install, Windows binaries (you know, that little niche Operating System that happens to just run on 95% of computers out there...)

Re:Pretty simple.

[joib]

I think one reason might be that before PgSQL 7.0, pgsql was known for being both unstable and extremely slow. And only with 7.1 did the extremely useful outer joins get included (yes, you can emulate outer joins with subqueries and unions but that leads to quite loooong queries, as anyone who used openacs 3.x can attest).

Anyways, by the time 7.1 was out, which beat mysql in almost every way possible, LAMP had already become the established player.

Re:Pretty simple.

[Anonymous Coward]

What, someone kicked your teeth out?

Bastards.

Re:Pretty simple.

[Anonymous Coward]

Wow, someone complaining about MSSQL 6.5. When you get into the 21st century, you'll find that ADO does have connection pooling (PHP does not), data caching (with disconnect/reconnect, which PHP does not have), and parameter escaping (MyShitequl doesn't even have parameters). But the thrust of your message is that MySQL is better because you are shitty programmer.

Re:Pretty simple.

[Eric Savage]

A tuned query set is pretty much unportable no matter what DBMS you are using. Once you start getting into maintenance tasks, vendor-specific performance boosting options (which are often the reason you've chosen a vendor), and basically anything beyound the most basic selects and inserts, you code becomes tied down to a server. Also the general disregard most servers have for ANSI means that it is only a standard in theory, not in practice.

Re:Pretty simple.

[Anonymous Coward]

Well, if you used the sensible dateformat (yyyymmdd) instead of rely on ones determined by connection settings you might do better... and judging sql2k by 6.5 ... well, since 6.5 they've discovered electricity and put a man on the moon ffs.

Re:Pretty simple.

[nuba]

On top of pg being unstable and slow, mysql was known to have easier admin tools once the install was done. I forget when this stopped actually being true, but in the beginning it was... and the reputation stuck.

Re:Pretty simple.

[sebster]

Have a look at Servoy (http://www.servoy.com) if you want to develop in a FileMaker like environment under Linux. In fact, Servoy works under Windows, Linux, Mac OSX, and many other UNIX operating systems, works with SQL databases, and is much more powerful than Filemaker. Fully functional trial is available too. See http://forum.servoy.com for support.

Re:Pretty simple.

[Anonymous Coward]

7. dunno
8. don't we all?
9. available on tons of platforms
10. OK, we'll use PostgreSQL instead. I could never take something whose name begins with 'my' seriously, anyway...

Re:Pretty simple.

[stridebird]

mysql was known to have easier admin tools once the install was done

What happened to them?

it's 11:59...

Re:Pretty simple.

[Anonymous Coward]

7. Tastes Great, you insensitive clod!

Re:Pretty simple.

[Anonymous Coward]

> This is what schemes like row-level locking and multi-version concurrency are for.

Are you japanese? :-)

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.

[kpharmer]

> A tuned query set is pretty much unportable no matter what DBMS you are using.

As long as you avoid premature optimization, this usually doesn't happen. Sure, at the extreme edges it does, but I'm usually only there for data warehouse aggregation or recursion.

Re:Pretty simple.

[nojomofo]

Uhh, I'll assume that you mean free databases, because Oracle was certainly usable then....

Re:Pretty simple.

[shakah]

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.

How does locking the whole database help if your UPDATE or DELETE statement affects multiple rows and fails somewhere in the middle?

Re:Pretty simple.

[ajs]

Additionally, since it's the only entry on the above list that is missing or has limited support for transactions

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.

[Fulcrum of Evil]

How does locking the whole database help if your UPDATE or DELETE statement affects multiple rows and fails somewhere in the middle?

Support for aborting transactions is mostly orthogonal from concurrency management. The place they intersect is when a transaction wants to read or write a row that's locked by another transaction. Typical semantics are to allow reading of the old data and block writing of the new data until the transaction completes. This is usually handled by keeping a copy of the old row and the new row util commit time.

Re:Pretty simple.

[Anonymous Coward]

Yikes! It's all I can do to prod the folks where I work to move our data out of FileMaker Pro and into ANY modern SQL server. ANY. Our web team has to do cartwheels to get stuff out of FileMaker and into something we can use (MS-SQL and MySQL). But those FileMaker wonks (there are two) are grittier and tougher than anyone you've ever known.

In their defense, there's quite a bit of data and it is used by others. Not just our web team. If I were a legacy app, I'd live forever. I swear.

Re:Pretty simple.

[110010001000]

Actually:

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

Re:Pretty simple.

[Anonymous Coward]

Stuart, I like you.

Re:Pretty simple.

[Anonymous Coward]

php automatically closes connections at the end of each script. php is stupid.

Re:Pretty simple.

[Anonymous Coward]

Stored procedures are just bloat? Congratulations, that's the stupidest thing I've read all day.

Re:Pretty simple.

[fitten]

Exactly... I read this in 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. In particular, MySQL proved ideal for the exploding area of dynamic Web content.

and immediately thought that the people this is talking about don't know WHY they need those features either. For example, they won't use transactions because they don't know WHY they should, until after something horrible happens and someone comes in to clean up their mess and explain WHY they needed to do what they didn't.

We use MySQL at work for many things, but I never forget for an instant what a house of cards it can be if we use it somewhere we shouldn't (where we need some of those "fancy" features).

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.

[slittle]

Well, I dev on Windows (coz working in X shits me to tears) and deploy on Linux, so anything that doesn't do both seamlessly gets the red card.

Re:Pretty simple.

[akuzi]

> 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.

It's a bit more complex than that.

Transactions have to satisfy all four ACID properties.

What you mention will implement the 'I' (isolatation) and 'C' (consistency), but won't make them atomic (all or nothing, ie. support for rollback), or durable (fault tolerant).

Re:Pretty simple.

[quasimodal]

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.

Postgresql's always been a much better dbms than mysql. It's reputation always been based on query speed (for simple queries) and ease of installation.

Re:Pretty simple.

[Ed Avis]

You're right; I was thinking only of serializability (isolation of transactions) and not about durability or atomicity. Okay then: you could easily keep two copies of the database, perform the transaction on one, and if it succeeds copy all the data over to the other copy. If the transaction fails part way through you roll back from the other copy. You make sure disk buffers are flushed before performing the copy over. This takes care of both atomicity and durability. Again, it's not difficult to do: the challenge is in doing it and keeping performance.

A database benchmark of how fast you can perform a million insert statements is pretty meaningless: what matters (for large applications, anyway) is how fast you can serve a large number of clients in parallel, all performing updates, while preserving transactional integrity and never losing any data if the power cord is yanked. Any claim that database A is faster than database B should be taken with a pinch of salt unless you can be sure they are providing the same level of ACID guarantees.

Re:Pretty simple.

[Ed Avis]

You may be right that the people who want features like subqueries, views or stored procedures are not providing reasons but only whining. On the other hand, you are guilty of the same thing with blanket statements like 'Stored procedures and similar features are just bloat, and gain you no real advantage' - many programmers working with more advanced RDBMSes know otherwise. BTW, I also disagree with your assertion about subqueries being slow: you have to take care, yes, but like most things they are a very useful tool in the right hands and most simple uses of subqueries are not slow (at least on the databases I have most used, Oracle and Sybase).

I'm interested to know: have you ever run into the problems mentioned in MySQL Gotchas? Or are these deviations from the ANSI standards something that doesn't matter in practice.

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:Pretty simple.

[Anonymous Coward]

no, he really meant locking a single row. other rows can be modified, just not he locked row.

Re:Pretty simple.

[LoneRanger]

MySQL is stupid and useless and I'll prove it:

CREATE TABLE float_test (one float, two float);
INSERT INTO float_test VALUES (2.3242, 3.2223232);

now:

SELECT * FROM float_test WHERE one = 2.3242;
Empty set (0.00 sec)

Then according to MySQL 2.3242 != 2.3242. Where the hell did they take their Math classes?!

Re:Pretty simple.

[rothic]

Here's the problem

Re:Pretty simple.

[LoneRanger]

So they know about the problem, but don't take any steps to solve it. That's comforting. The exact same SQL works without any odd ROUND() usage or any modification in PostgreSQL.

Re:Pretty simple.

[Shurhaian]

If you're comparing floats for equality, MySQL is the least of your worries.

Re:Pretty simple.

[iammaxus]

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.


Just wait a second, i have the distinct feeling this post is a sarcastic jab at MS software, and how much worse it is than open source/free software whatever you like. Yet, is it me, or is this not by far the largest, if not sole source of income for every single company that embraces open source?

Re:Pretty simple.

[FryGuy1013]

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

Can you help me solve this problem that I easily solved with a subquery?

Given a table consisting of user, tasktype, priority; a table of tasks, tasktypes, and requestdate; return a list of users and the associated task with the lowest (highest) priority and earliest date. Currently I'm implementing this by doing a join on the two tables and filtering out the duplicates in php. The task list has an upper bound of around 10-15 rows, so this isn't that great of a problem but it hopefully illustrates my point. If you have a better way to do this, then please explain. I'm not trying to bash mysql. I use it, but it certainly isn't quite as sophisticated as Postgres or other databases that support more features than it.

Re:Pretty simple.

[Betcour]

you'll find that ADO does have connection pooling (PHP does not)

mysql_pconnect()

Re:Pretty simple.

[ajs]

you are guilty of the same thing with blanket statements like 'Stored procedures and similar features are just bloat, and gain you no real advantage' - many programmers working with more advanced RDBMSes know otherwise

And yet, correctly, the MySQL folks have prioritized features based on real need rather than what "people know" on Slashdot. When a customer needs something, it's added to or moved up the list. When a customer says "eh, I guess we can live without", something else ends up getting done first. Subqueries and stored procedures turn out to be the most widely gotten around features of modern RDBMSs

I also disagree with your assertion about subqueries being slow

Good, because I would too. That's why I never said that.

you have to take care, yes

THAT is what I said. You have to take care. Worse, you have to take care to avoid the really non-obvious penalties that fall out of how the optimizer decides to strategize the sub-queries. Oracle manuals just give up and suggest that you run the query with the option that prints out the optimization strategy and try to hand-tune around the optimizer retro-actively! That seems to me a like a feature you're better off leaving to people who have thought enough about it to create the temporary tables themselves.

BUT, the MySQL folks disagree with me here, and ARE adding this feature. They're just taking their time about it, and doing more important things first.

I'm interested to know: have you ever run into the problems mentioned in MySQL Gotchas? Or are these deviations from the ANSI standards something that doesn't matter in practice.

Most of what's listed there are the same sorts of things you could list for any modern RDBMS. They are places where MySQL's feature-set is richer than the standard (just as Oracle's is, or PostgreSQL's, etc) and where the assumptions that YOU might make about how such extensions behave could be incorrect.

I would suggest that anyone using MySQL on a regular basis read that document, as it's quite helpful, but to point out a database's extensions as some kind of reason not to use it seems... a tad overzealous.

Re:Pretty simple.

[ajs]

Given a table consisting of user, tasktype, priority;

create table foo (user int, tasktype int, priority int)

a table of tasks, tasktypes, and requestdate;

create table bar (task int, tasktype int, requestdate int)

return a list of users and the associated task with the lowest (highest) priority and earliest date

Well, not that I like to do people's homework for them, but, here's what any database that supports subqueries will end up doing anyway:

select user, task, priority, requestdate
from foo join bar on foo.tasktype = bar.tasktype
group by user, task
order by priority desc, requestdata asc
limit 10

Sorry, but that one doesn't require a sub-query at all. Did you, perhaps mis-state the problem?

Re:Pretty simple.

[ajs]

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

Battle-cry of the troll.

Later on, confirming the wisdom (or lack thereof) of this post:

Bull. FUCKING. Shit.

I'm reminded of the old "This is the title of the story, which is also found several times in the story itself", which I loosly quote from memory:

"Sentence fragment. Good device."

Nuff said.

Re:Pretty simple.

[ajs]

As others have pointed out, this is a classic blunder. Oracle, for example, has type constraints (it's been a while, that's not the actual term) that you have to twiddle if you want to get this kind of thing the way you want. Please note that I said "the way you want"... there is no "right" here, because you're dealing with real numbers in finite precision. If you feel that your number is in a cononical form when you enter it, you should be using a fixed-point type, which all modern RDBMSs (including MySQL) support.

Re:Pretty simple.

[FryGuy1013]

Actually, your query is wrong for what I said. It should be:

select user, task, priority, requestdate
from foo join bar on foo.tasktype = bar.tasktype
group by user
order by priority desc, requestdate asc

I wanted every user, and just the single task with the lowest priority. Sorry. I didn't realize you could do group bys and select non-aggregated data. MSSQL forbids this. You aren't allowed to select task, priority or requestdate in that query, only aggregate data like max(task), but not task with min(priority). In essence this is the subquery:

select cage, parrot, aliveness
from petstore p
where parrot in (select max(p2.aliveness) from petstore p2 where p.cage = p2.cage)

I guess my point is shown wrong. It's not really my homework though -- the query I have works well enough and I'm not looking to replace it, and this is a discussion about the deficiencies of sql systems. My being unable to come up with a single SQL query shows a deficiency somewhere. Admittedly, it's because I've been using MSSQL lately and not knowing MySQL as well as I should, but subqueries do make things easier if you think that way. Maybe it's a deficiency in MSSQL that's forced me to. I think I remember myself trying to do something similar to this using group by in the manner you did but couldn't get MSSQL to do what I want that way and having to use subqueries. However, subqueries also tend to slow things down a lot because joins can be optimized a lot better. I've spend a bit of time fixing my coworker's queries because he used subqueries and some of his reports got really slow (minutes when the joins were sub-second). However, for simple one-time tasks, subqueries can be useful because the implications of joins don't have to be thought of.

select username, email
from users
where username in (select username from userstospam where lastspammed < '4/1/2004')

versus

select username, email
from users join userstospam on userstospam.username = users.username
where userstospam.lastspammed < '4/1/2004'

Do I need to make sure that some fields are not null? which fields should be null or not null? These questions usually not as easy to answer as just typing the first query out, especially when you get 3-4 joins deep.

Re:Pretty simple.

[JamieF]

msql also had some very serious bugs. It would periodically get confused and stop writing data to the disk, but would continue to process new SQL statements (including ones that changed data) as though it was working. Insert a row, it says OK, 1 row inserted. Select that row, 0 rows found. Kill and restart it and it's OK again, except you lost all the data that it forgot to write to disk. Duh.

After that I started caring about the quality of the RDBMS engine that I was using. The idea that it's better to write inherently complex and hard-to-test logic like transactions into each of your applications is very very weak. Concurrent programming is hard, period. Just because you haven't run across that race condition or corner case in your initial single-user development with your "QA lite" testing doesn't mean that it isn't going to happen when your app goes into production. Pretending that the problem doesn't exist doesn't make it go away.

Open source fans should be the first folks to say that the problem has already been well solved by package XYZ, so we should just use that instead of rolling our own.

Re:Pretty simple.

[Tukla]

"it quickly won over small users"?

MySQL is popular with children and dwarves?

Re:Pretty simple.

[Tukla]

Thank goodness I only use COBOL and flat files.

(Somebody...please kill me.)

Re:Pretty simple.

[Anonymous Coward]

Just because you don't know anything about databases doesnt mean you should resort to ad-homeniem attacks.

Oh wait... it IS because you don't know anything about databases.

Re:Pretty simple.

[Ed Avis]

When a customer needs something, it's added to or moved up the list. When a customer says "eh, I guess we can live without", something else ends up getting done first.

The fact that MySQL customers have not felt the need for stored procedures does not imply that they are 'just bloat' which is what you said. Note that MySQL customers are a self-selecting set of people who don't consider stored procedures (and to some extent, other RDBMS features like views, subqueries and high-concurrency ACID) to be that important. On the other hand, Oracle customers are self-selecting as people who do want that kind of thing and are willing to pay for it, so it might not be fair to judge MySQL on the basis of what Oracle customers want either. But when almost every other RDBMS supports these things, you see that MySQL is the odd one out, not Oracle.

DOS (specifically DR-DOS) still sells in some numbers, and DOS customers are not (on the whole) clamouring for lightweight threads or a journalling filesystem or multiuser security. This does not mean that these are not important features for most other operating systems. To say that DOS customers haven't been asking for them does not change the fact that they are highly valued for most of the rest of the world.

About the 'gotchas' - extensions to ANSI SQL is fine, but my feeling is that if I declare a column as 'int not null', I expect the value to be not null. No ifs, no buts. An 'extension' to the SQL semantics where the column can get populated with null values in certain odd cases and with no warning is very surprising and even dangerous. Almost any deviation from standards (indeed, any bug) can be counted as an 'extension' if you look at it the right way. The best thing would be for MySQL to have a standard mode and an extended mode; then these extra 'features' could be turned on or off depending on their merits.

Re:Pretty simple.

[Eric Savage]

Avoiding premature optimization is wise, but taking advantage of the vendor specific options becomes part of your system and code design. I'm thinking of mysql's REPLACE and DELAYED keywords as examples of this.

Re:Pretty simple.

[Anonymous Coward]

Eh, doesn't it not prove anything when your example cites keywords you need to use to tune MySQL for decent performance in certain cases? The whole complaint was that MySQL requires you to go through hoops that other DB vendors don't. It'd be more relevant to cite optimizations required in other DB packages. In my experience, the query optimizer should be allowed to do most of the work; trying to optimize except for very performance critical parts is a waste of time, because the DB vendor is going to keep improving their product and changing the internals anyway.

Free

[Anonymous Coward]

The best things in life are free.

Re:Free

[Anonymous Coward]

... but you can give them to the birds and bees.

Re:Free

[smack_attack]

I need money.

The Truth About MySQL

[MikeBouma2]

I'm here to tell you about MySQL and why MS-SQL2000 is so much better (I had to repost as a new account b/c for some reason my karma is "bad"???)

I am what most people would consider a highly trained technical professional. Unlike most people who spout off at this site, I have the certificates to prove this, and furthermore they're issued by the biggest software company in existence.

I know how to tell facts from marketing fluff. Now, here are the facts as they're found by SEVERAL INDEPENDENT RESEARCH INSTITUTES:

Expenses for file-server workloads under Windows, compared to LinuxOS:

• Staffing expenses were 33.5% better.
  
• Training costs were 32.3% better.
  

They compared Microsofts IIS to the Linux 7.0 webserver. For Windows, the cost was only:

• $40.25 per megabit of throughput per second.
  
• $1.79 per peak request per second.
  

Application development and support costs for Windows compared to an opensores solution like J2EE:

• 28.2% less for large enterprises.
  
• 25.0% less for medium organizations.
  

A full Windows installation, compared to installing Linux, on an Enterprise Server boxen:

• Is nearly three hours faster.
  
• Requires 77% fewer steps.
  

Compared to the best known opensores webserver "Red Hat", Microsoft IIS:

• Has 276% better peak performance for static transactions.
  
• Has 63% better peak performance for dynamic content.
  

These are hard numbers and 100% FACTS! There are several more where these came from.

Who do you think we professionals trust more?

--
Copyright (c) 2004 Mike Bouma, MCSE, MCDST, MS Office Specialist, widely respected Amigan

Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.2 or any later version published by the Free Software Foundation; with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts. A copy of the license is included in the section entitled "GNU Free Documentation License".

Re:The Truth About MySQL

[Anonymous Coward]

You posted this in the wrong place, buddy.

Re:The Truth About MySQL

[Anonymous Coward]

Those "Certificates" are a dime a dozen- try a college degree.

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.

Re:The Truth About MySQL

[not_a_product_id]

>webserver "Red Hat" >Who do you think we professionals trust more? Professional what? Since you don't even know what Red Hat actually is can we assume your profession is something along the lines of lavatory attendant?

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.

Re:The Truth About MySQL

[Paradise Pete]

You are what most people consider to be a ignorant, propaganda-influenced bloke

I'd say you are what most people consider to be a gullible bloke, if you think the parent was not pulling your leg.

Re:The Truth About MySQL

[luferbu]

Come on, I'm sure you're Bill Gates, don't hide behind that Mike Bouma name :)

FP

[Anonymous Coward]

FPROST0r PIZZT!!!

TAKE BACK YOUR FREEDUMB

[Anonymous Coward]

Use open source and get back your free dumb once and for all! You get to work for free for a dumb project! Yes that's right kids while all the jobs are off to India and Chinkland you get to write lunix for IBM and Oracle to profit from but get this : YOU DO IT FOR FREE!!!

PS : (FP dipshits)

4/20 is almost over beat up all kikes you see ASAP

[Anonymous Coward]

n/t

mysql grew fast because

[Anonymous Coward]

of shitty fucking movabletype blogs. fuck off you conceited assholes

Why?

[Daengbo]

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. In particular, MySQL proved ideal for the exploding area of dynamic Web content.

We can talk about this one for hours.
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?

[jaaron]

Please don't mention MS Access again. It makes my skin crawl just thinking about it. MS Access lets everyone think they can be a DBA. And then they want to run enterprise level warehouse tracking, inventory, payroll and who knows what else from the little database they whipped up between Solitare games.

Sorry if I sound bitter but MS Access has given me more than one headache.

Re:Why?

[Vellmont]

I think Access is great, for what it's intended for. It's perfect for small databases where someone would otherwise use flat files. It's also a great reporting tool to use to connect to other databases via ODBC.

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?

[Daengbo]

I know... I am not supporting MS Access, just stating the fact that, for very small businesses, it fills a niche. I would rather have an interface to an SQL DB, but that's not my original point.

Re:Why?

[leperkuhn]

The new filemaker finally supports the relational model. I haven't personally tried it yet however.

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:Why?

[Anonymous Coward]

access is a nice database tool

the problem i have is with the users of it. people thinking they are database experts cause they can use access.

but access is like a hammer, a pro can use it with great results.

Re:Why?

[GooberToo]

Personally, I like the fact that Access was brought up. Simple fact is, if you wouldn't use Access for your project, you shouldn't be using MySQL either.

Access = MySQL with more features, but slower.
MySQL = Access, but is faster and has far fewer features.

In a nut shell, don't use MySQL if you wouldn't use Access!!

Re:Why?

[Anonymous Coward]

Hi. U R dum. Thanks.

Re:Why?

[Anonymous Coward]

that is EXACTLY what these guys did. you meant .mde, but they don't even use it - the source code is provided w/ the app (for $1,000).

Re:Why?

[Mattster P.]

MySQL isn't really comparable to MS Access, it is more on the same level as the MS SQLServer product. I've even seen reports that MySQL has better performance under a load of 100 users or more than SQLServer and close to the performance of the bigger DB Vendors like Oracle.

Re:Why?

[darnok]

Although I personally prefer Postgres, I wouldn't say MySQL is only "useful enough for small businesses".

Compared to "big" database products such as Oracle, DB2, Postgres and SQL Server, MySQL is:
- very fast (i.e. will run on less hardware)
- costs nothing, for most purposes
- is very well supported
- has very good tools
- runs on multiple platforms
- doesn't support full SQL or ACID-compliance

I'd happily use it for a read-only Web-based database application; I know it'd be fast and a good fit for purpose. Those apps are often built by very large companies, not just small business.

Yep, I know MySQL can do a lot more than that, but I personally would generally lean towards using a "big" database product for things such as transaction-based sites. I'd rather rely on e.g. Postgres to give me transactional integrity than rely on coding it myself into my application, but YMMV.

Re:Why?

[Daengbo]

I think that you added the "only" part yourself. I cetainly didn't mean it.

Re:Why?

[arkanes]

They're totally different things - Access is a file-based system, MySQL is server based. Use SQLite where you'd use an access back end. In fact, SQLite would be an acceptable back-end replacement for a whole lot of the MySQL based projects out there.

Re:Why?

[MattRog]

No DBMS currently 'supports the relational model'. They implement parts that are easy and ignore the rest.

Re:Why?

[GooberToo]

While I agree with everything you said, I did not mean to make it sound like they were exactly the same technologies. Hopefully, most everyone will understand my angle on it.

As many (most?) people that would be looking to use MySQL probably have some Access experience, it becomes a good matrix for comparison. Your average MySQL or Access user is not going to know what SQLite is. Thusly, if people can determine that Access may or may not be a good match for their project then I argue that they now have an answer to the question, "will MySQL be a good match for their project."

After all that is said and done, Access still is far more feature rich than is MySQL even though it is much slower. Just the same, they both scale rather poorly. Again, this allows those that don't know much about databases to have some basis for comparison. The result of that comparison should indicate if MySQL is a good choice for their project.

Re:Why?

[arkanes]

Disclaimer: I work alot with Access.

That said, I think Access is basically never the correct choice for an application. It's just barely tolerable as something that suits use to slap stuff together, but as one of the guys who gets to re-work all that slapped together crap into real applications, I'm leery of even allowing that.

The most common application level use of Access is as a datastore for VB apps. This is a crummy use of Access because it's slow and feature-poor and ugly. SQLite is a much better choice in this case. MySQL is often used in the OSS world for this purpose, and it's a crummy choice because it's server based and not file based and adds alot of complexity to installing/configuring the application.

MySQL is excellent for low-end shared data apps, however. Anywhere you need concurrency, Access is out.

Note that I'm talking about Access as a database, not Access as an application - Access as an application is still totally crap, but there's no viable alternatives so I have to put up with that.

Re:Why?

[GooberToo]

I don't believe I can take you task on any of your points.

MySQL, like Access, has it's place. Sadly, both tend to be used far outside their comfort zones.

Bluntly, I am pro-PostgreSQL. That's not hard to see. I really am not anti-MySQL. As I said, it has its place. Just the same, as you too have identified, just about any project where you want to use MySQL, there is always a better solution available. In your case, SQLite appears to be your favorite hammer. With others, it's Firebird...etc... Personally, I don't care which you choose, I just like to remind people that there are almost always much better options available than MySQL.

Re:Why?

[the_mad_poster]

The problem with Access is that it's built for morons. Morons in the context of database management, anyway, if not morons in general.

For example, here are some irritating things my boss does with Access because Access is for people who don't need a database, or don't know how to use one:

• He uses autonumbers for primary keys EVERYWHERE instead of tying the keys to relevant data.
• He creates databases of one table with no relationships or constraints what-so-ever. That is, he puts a flat text file / spreadsheet into the database and then makes a form for it rather than just putting it in a damn spreadsheet to begin with.
• He creates databases of a dozen tables with no relationships or constraints what-so-ever. Ah yes, trying to figure out what's in them, what should be in them, and what shouldn't be in them is always GREAT fun. There's nothing like using a relational system to store data in a non-relational way.
• He tells me to create spreadsheets to track large amounts of related data and then argues with me when I say that if the information is related, it should go in a database for easier manipulation and reporting.

Access must die. Evil wanking POS.

Re:Why?

[GooberToo]

It's so funny how scared and upset the MySQL guys become when you offer valid advice to people that accurately reflects the state of MySQL.

Every time I offer this, it gets modded up by neutral and pro-whatever database and then gets modded back down out of ignorance and fear from the MySQL camp.

I think that's pretty friggen funny. Are MySQL users, as a whole, really so scared to find out that better solutions exist? Or worse, find learn why, when and how it makes sense to even use a MySQL database?

Re:Why?

[E_elven]

>No DBMS currently 'supports the relational model'.

You have to think of it as a 'crutch.'

Re:Why?

[chris_mahan]

I use Access because I support very small soho, and I can use ado to point to it.

I'll be the first to agree that it sucks horribly, yet it has ultimate portability. Just drop the file in the folder and you're good to go.

Ultimately, though, I design my database right, and just put all the tables in access. Everything else goes in the server code (asp/python+com+ado).

I also wrap all the database calls in-code with wrappers that handle everything. This way, it's real easy to move from one DB backend to another.

Re:Why?

[Vellmont]

I feel the same way about hammers. The damn things are so easy to use a moron can use them. My idiot boss fixes everything with hammers.

• He fixes his TV with a hammer. Every time the picture goes on the fritz the idiot hits the damn thing with a hammer until the TV is either completely broken, or works again.
  
• He pounds in screws and bolts with with a hammer, he pulls the out with a hammer. Geez buddy, ever heard of a screwdriver or wrench?
  
• When he does pound nails with the hammer, he keeps pounding until the hammer is halfway through the wood, then expect me to fill it with wood putty!!
  

Stupid wanking hammers! They should all die!

Re:Why?

[the_mad_poster]

While the mocking is humorous, it's not really in response to my post. My post is about a guy using a tool that's intentionally built to let people who don't know how to use it, use it. Your post is about a guy who continually uses the wrong tool for the job (only one of my bullet points covered that subject).

You can't prevent stupid people from misusing tools. However, Microsoft Access actively engages in encouraging them to abuse DBMSs. That's the problem. A DBMS is not a simple tool, yet Microsoft tries to make it one in Access. Result? A bunch of folks who get sucked into a simple, pretty pointy-clicky system that they don't really understand when it comes right down to it. Not a problem if they restrict themselves to that messed up situation, but how often does that happen?

Re:Why?

[jesterzog]

I think Access is great, for what it's intended for. It's perfect for small databases where someone would otherwise use flat files.

I completely agree. Access isn't a great database, but it makes up for that by the front end features that it provides for building small data-centric applications quickly and conveniently, with all of the forms and reports that you might want. Most other databases are essentially servers, and at best provide a command line access utility and an API so you can write programs to connect and get at the data.

I used Access for several years to store a relatively small membership database for an organisation I was helping out. (On the order of a few hundred members.) By it's nature the data integrity wasn't absolutely critical, but the relationships were complex enough that I really didn't want to munge it into a flat file. On the other hand it wasn't worth going to the effort of having a complete relational database server back-end and writing entire separate applications to access and format the data.

Access offers a nice miniature encapsulated package that lets you do things like this. You can store several tables and join them via SQL queries to get at your data. As importantly, you can also design the data entry forms and reports, package it all up inside a single database file and give it to someone else who can open and edit it directly as if it's any other document.

Since I've switched completely to Linux I've been looking for something that offers a similar accessible front end, but I haven't really found one. From what I understand StarOffice has something very similar to Access, but I haven't meen motivated enough to check that yet.

The only open source project I can find that does anything similar seems to be PgAccess, which is a Tcl/Tk front end for a postgres database. It's interesting, but still nowhere near as complete from a quick-and-dirty data-centric application interface perspective as MS Access.

Re:Why?

[Vellmont]

I hate cars too because of the simple interface. The morons at Ford made the thing so simple that dumb people can easily miss-use them! Damnit, if cars were just harder to use then all the problems would be solved.

The point is your blame is missdirected at Microsoft and Access where it should be applied towards your boss. Microsoft Word make typing and editing documents easy too.. do you blame Word for people that can't write? Dumb bastards, if only you had to hit 5 keys at once to type a character only the professionals would be writing, and these amatures that think it's easy would realize just how hard it is because of the difficulty of the inteface!

Re:Why?

[the_mad_poster]

No, that's not my point. You're missing my point.

Cars are not, inherently, that complicated to use. The hard part is concentrating on what you need to do and making the right decisions, not doing it. Even back before the days of power steering and power braking and whatnot, they weren't THAT much harder to use, you just had to be stronger and have better reaction times.

Perhaps, however, I did not adequately and clearly explain what I'm getting at. Access is a worthless piece of junk. Microsoft took an incredibly complicated concept and simplified it. Unfortunately, unlike the mechanical engineering example of cars, they did it very poorly. The shortcomings of Access are frequently marketed as "benefits" to people who don't know how to use a DBMS. From limited, oversimplified data types to a grossly inadequate set of features (how can it be a DBMS, much less a relational one, if it's not even ACID compliant?), Access is little more than a glorified version of Excel. I may be mistaken, but I'm pretty sure that I even saw an entry in the help file somewhere that suggested that serial fields make good primary keys.

Unfortunately, in typical Microsoft fashion, the quality of the product is not nearly important as the quality of its marketing, so it sells. My boss bought Access on Microsoft's marketing, and now he thinks he knows how to use relational database systems when, in fact, he only knows how to use Access.

Re:Why?

[JamieF]

FileMaker Pro has had relational functionality since version 3.0, released in early 1996. I used it for a one-to-many relation in my wedding invitation database way back in 1996. One table was for invitations, the other was for invitees on each invitation (a one-to-many relation).

FileMaker Pro 3.0 from Claris Goes Relational
FileMaker Pro 3.0 Has Relational Powers

Re:Why?

[JamieF]

FileMaker Pro first had relational functionality in version 3.0, way back in 1996. According to your linked article, in 7.0 they changed it so you can put multiple related tables into a single file.

Re:Why?

[Rick BigNail]

It is your atitude.

If you were more polite, those mysql-loving guys might not down-mod you.

Re:Why?

[Kardamon]

Correct. Have a look at database debunkings to see why SQL DBMS != relational DBMS.

Re:Why?

[GooberToo]

The original message was very polite. Thusly, showing the fear of those mysql-loving guys.

Which was exactly my whole point.

My Theory?

[Anonymous Coward]

People recognize what "MySql" is.

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.

Re:My Theory?

[Paul d'Aoust]

hell, people can't figure out how to spell MySQL either. Is it 'my ess-cue-ell' or is it 'my sequel'? I think it's properly the first one, but nobody ever bothers calling it that.

Re:My Theory?

[Anonymous Coward]

I haven't used it in years, but if you read the docs, you'd know it was the latter no the former.

Re:My Theory?

[Anonymous Coward]

So, how do you pronounce MySQL? Given that My is Monthys daughter.

(can't as in my (opposed to your)). /M.

Re:My Theory?

[next1]

the postgres origins are actually quite interesting - it's here.

Re:My Theory?

[caluml]

It sounds vaguely French and therefore un-American

Great. Sounds like a good reason to use it for me.

Re:My Theory?

[MadChicken]

Actually, it's Canadian, and therefore un-American :)

I tried using that as a selling point where I work - "The HQ is only about an hour and a half from here!". Still chewing on that.

Re:My Theory?

[Paul d'Aoust]

Docs. Feh. Who reads those? ^_^

Actually, I do read the MySQL docs. I wish Firebird's documentation was as easy to read as MySQL's. I still have no clue what sort of data types are available to me in Firebird. Are there enumerators? I don't know!!!

Re:My Theory?

[persaud]

Postgres is a descendant of Ingres and thus stepchild to Sybase, SQL Server, Oracle, Informix and Oracle. From a history of RDBMSes:

Ingres technology diffused into the commercial sector through three major channels: code, people, and publications. Unlike the technical details of the IBM project, Ingres source code was publicly available, and about 1,000 copies were distributed around the world so that computer scientists and programmers could experiment with the system and adjust it to their own needs. Michael Stonebraker founded Ingres Corporation (purchased by Computer Associates in 1994) to commercialize the Berkeley code directly. Robert Epstein, the chief programmer at Ingres in the 1970s, went on to co-found Britton-Lee Incorporated and then Sybase. Both Britton-Lee and Sybase used ideas and experience from the original Ingres, and government agencies were early customers of both companies. Computer Associates released a commercial version of the Ingres code in the 1980s.

Continued movement of Ingres researchers throughout the database community spread the technology even farther. Jerry Held and Carol Youseffi moved from UC-Berkeley to Tandem Computers Incorporated, where they built a relational system, the predecessor to NonStop SQL. Until joining Kleiner, Perkins, Caufield & Byers in 1998, Held was senior vice-president of engineering at Oracle, where he headed that company's database efforts. Paula Hawthorn moved from Ingres to Britton-Lee (as did Michael Ubell) and eventually became a co-founder of Illustra Information Technologies Incorporated, now part of Informix. Stonebraker himself worked with Ingres Corporation, Illustra, and Informix. Other Ingres alumni went to AT&T, Hewlett-Packard Company (HP), IBM, and Oracle, bringing with them the lessons learned from Ingres. As Robert Epstein observed, "What came from Ingres was the experience of having built a prototype . . . to say what parts need to be done differently."

Re:My Theory?

[pr0c]

Google => prounounce mysql

First result:
The official way to pronounce MySQL is ``My Ess Que Ell'' (not ``my sequel''), but we don't mind if you pronounce it as ``my sequel'' or in some other localized way.

Google is your friend (until they turn evil).

YOU FAIL IT!!

[Anonymous Coward]

Mysql is teh rulez and you are teh sux! AND YOU FAIL IT!!!!! GO MTL...BLOW LEAFS BLOW!!! bsd IS dead...Wind0ws are teh GAY!...uhhh....ITS A TRAP!!! oh shit wrong site....

inmy opinion

[minus_273]

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

Why Mysql will beat still beat MS SQL Server

[martin cortez]

Because Microsoft SQL Server is not competing with yet another database system, they are competing with a new development paradigm. Microsoft SQL Server & Microsoft may have a market cap of half a trillion dollars, but the US economy puts out 12 trillion per year alone. If push comes to shove, it won't matter how big Microsoft SQL Serveris - they will get squished like a bug. I renember when IBM spent billions back in the 80's to push the their database system on the market place to try and squeese out the systems's already out there. It didn't matter how big they were either, they got hammered.

Re:Why Mysql will beat still beat MS SQL Server

[Anonymous Coward]

Wow. Almost the same post I saw earlier today. Please stop posting the same stuff, replacing only the topic of the paragraph.

Why MySQL Grew So Fast?

[Anonymous Coward]

Because PostgreSQL takes longer to type :-)

Re:Why MySQL Grew So Fast?

[gtaluvit]

You laugh, but it may be true. On a *nix system, you have dir or ls. Which do you use?

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

[Anonymous Coward]

The issue isn't really the lack of the feature set, but the fact that the MySQL devs used to be clueless idiots about it. Here's what the docs used to say about foreign keys:

5.4.5.1 Reasons NOT to Use Foreign Keys constraints

There are so many problems with foreign key constraints that we don't know where to start:

Foreign key constraints make life very complicated, because the foreign key definitions must be stored in a database and implementing them would destroy the whole ``nice approach'' of using files that can be moved, copied, and removed.

The speed impact is terrible for INSERT and UPDATE statements, and in this case almost all FOREIGN KEY constraint checks are useless because you usually insert records in the right tables in the right order, anyway.

There is also a need to hold locks on many more tables when updating one table, because the side effects can cascade through the entire database. It's MUCH faster to delete records from one table first and subsequently delete them from the other tables.

You can no longer restore a table by doing a full delete from the table and then restoring all records (from a new source or from a backup).

If you use foreign key constraints you can't dump and restore tables unless you do so in a very specific order.

It's very easy to do ``allowed'' circular definitions that make the tables impossible to re-create each table with a single create statement, even if the definition works and is usable.

It's very easy to overlook FOREIGN KEY ... ON DELETE rules when one codes an application. It's not unusual that one loses a lot of important information just because a wrong or misused ON DELETE rule.

The only nice aspect of FOREIGN KEY is that it gives ODBC and some other client programs the ability to see how a table is connected and to use this to show connection diagrams and to help in building applications.

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

[c4Ff3In3 4ddiC]

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.

Is this your A.D.D. showing?

Re:Picking the right tool for the job

[Anonymous Coward]

Parent is a TROLL! Gives away the endings to a bunch of movies:

• Rosebud is the name of Citizen Kane's sled.
• Verbal Kint is Keyser Soze.
• Darth Vader is Luke's father.
• The child in Sixth Sense is revealed to be dead.
• The Planet of the Apes is actually Earth.
• Rocky loses.

Mod parent down.

Re:Picking the right tool for the job

[jesterzog]

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.

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 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.

Re:Picking the right tool for the job

[Anonymous Coward]

Wow, it's like they never even heard of SET TRANSACTION ISOLATION LEVEL or SET CONSTRAINTS ... DEFERRED.

The most frustrating thing about MySQL is that most deployments only really support the "read uncommitted" isolation level, in which nonsensical data is so likely that ANSI SQL actually forbids such transactions from doing any updates!

Re:Picking the right tool for the job

[Anonymous Coward]

That has got to be the best in-context spoiler excuse I've ever seen.

6 movies, in the space of 3 sentences. In an article about a database system...

Re:Picking the right tool for the job

[jcdlondon]

This was changed in 2001, Here is the commit.

I think the changes are pretty entertaining...

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.

Re:Picking the right tool for the job

[Mike McTernan]

If you use foreign key constraints you can't dump and restore tables unless you do so in a very specific order.

This has been fixed. You can dump them in any order using mysqldump, and then when re-importing them temporarily disable the foreign key checks with SET FOREIGN_KEY_CHECKS=0;. It's documented here

Re:Picking the right tool for the job

[CaptainZapp]

There is also a need to hold locks on many more tables when updating one table, because the side effects can cascade through the entire database. It's MUCH faster to delete records from one table first and subsequently delete them from the other tables.

Yeah, I'll tell that to my forex traders, i can really see it now:

Guys, everythings faster, simpler and with far better concurrency effective immediately, beecause now when you delete a customer we just go ahead and delete all his deals. This will also be a blast to the auditors and folks... FOOOLLLKKKSSS!!!

(If you have any more specific questions: I'm the guy sitting on a rock at the edge of the city in this tar and feather coating...)

Re:Picking the right tool for the job

[tehcyder]

"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"

Spoilsport!

Re:Picking the right tool for the job

[MyHair]

what is it exactly that MySQL is good at that Postgresql isn't at least equal or better at?

The only things I can think of are the familiarity aspect, for people who are already very knowledgable about MySQL not wanting to switch

My first thought was a company that had one or more people with MySQL experience, or perhaps an existing MySQL server. It's not necessarily the want/preference, but the existing people who will use or support the product.

Also, many packaged products are built for MySQL. I'm a PostgreSQL fan with PostgreSQL installed and running, but for small home project a while back I installed MySQL because the application I downloaded was MySQL-only and I decided it was easier to install MySQL than reconfigure the application for PostgreSQL.

All that said,I still don't get why MySQL is the bee's knees. I don't see how it's easier. Perhaps it's the marketing or the shiny pretty documentation?

Re:Picking the right tool for the job

[Louis Guerin]

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.

Best... sig... ever!

L

Re:Picking the right tool for the job

[joshv]

Actually, every one of those is a valid reason not to use foreign key constraints.

I work with very large, commercially successful applications that code all constraints in the application layer. It works, works well, and allows you a lot more leeway when creating processes that do mass updates and inserts.

-josh

Re:Picking the right tool for the job

[Openstandards.net]

Yeah, I laughed when I first read that years ago. Fortunately, you can use foreign constraints today, and I do. InnoDB rocks!

It's worth noting that I love not having triggers or stored procedures. I develop J2EE applications, and want them to be highly portable, and have high rip-and-replace between database vendors. Sticking to DML without a procedural language helps a lot. I use the EJB tier to enforce business rules.

But foreign constraints are a blesing for data integrity. I remember well the day before they existed, having been doing RDMS development since dBase II in the early 80s. You spent 1/3 of your coding time just to ensure that your data integrity was intact, and still wound up with integrity errors in production.

Database contraints are one of the reasons central database servers took off (e.g., Oracle). It reduced development time and increased data integrity by a large magnitude, not to mention performance that improved over time as the database servers learned to optimize.

As a J2EE advocate today, what I really want to see is EJB-QL evolve. This may require some of the new additions to MySQL, such as subqueries. However, I could care less about having stored procedures in MySQL. MySQL will reach a point where it will have all I'll ever need, as I'll be doing most in EJBs.

It's worth noting the CMR is supposed to handle foreign constraints. Thus, in theory, even foreign constraints in the database are losing value. But unlike the arguement you copied from the MySQL docs, you aren't getting rid of them. You are simply moving them to the data object tier.

By the way, one last note: people confuse a foreign key with a foreign contraint. If you develop a relational model, then you have foreign keys. They are conceptual. It says that some valies in a child table correspond to values in a parent table. Nothing more. You have foreign keys even if you don't use foreign constraints. The contraints, however, are what some people are actually referring to when they mistakenly say foreign keys. The only role of the database in this area is to enforce constraints. Sure, in some syntax you do this by declaring a key, but you are really creating an insert/update contraint on a child table, as well as a delete constraint on the parent.

Re:Picking the right tool for the job

[sootman]

3 words: You. Aren't. Everyone. I use MySQL for a million and one lightweight apps: calendars, phone lists, surveys, etc. I haven't run into a single problem. For me, MySQL and Postgres are *equally* the right tool, so we go to the next phase: what do we already have installed? and the biggie at my current job: what runs natively under Windows, which our Intranet already is running on? Maybe PGSQL is better than MySQL in the grand scheme of things, but for the tiny little apps I write, there is *no difference whatsoever.* Period. And this applies to about a million other small developers.

Re:Picking the right tool for the job

[Anonymous Coward]

MySQL is easier to install and maintain than Postgres, IMO. Even though it is just a toy database program, it is good enough for most people who just want to use it to store grandma's recipes, a diary, or other things like that.

Re:Picking the right tool for the job

[Anonymous Coward]

MySQL is easier to install and maintain than Postgres

I disagree.

I first started with MySQL, and it was a pain to get running. I finally gave up, and tried Postgres. (This was just after Postgres 7.0 was released.)

Postgres install was smooth as silk. It's never given me a lick of trouble.

Re:Picking the right tool for the job

[Anonymous Coward]

Who's the fucking retard at MySQL AB that invented proprietary syntax for "SET CONSTRAINTS...DEFERRED"?

Re:Picking the right tool for the job

[JamieF]

That's pretty funny. Essentially, this is a feature that he's saying can potentially be used incorrectly, so they not only won't include it, but they say that a properly written application will include that feature itself.

---

I've just released my new RDBMS product, JamieSQL. It has no features whatsoever, takes no time to install, and is accessed via /dev/null for writes and /dev/random for reads. Application developers have no valid reason for storing data on a medium as unreliable as a disk, and if they need to do so, they should implement that functionality in the application itself. But if they need a quick way to store and retrieve that data, and reliability isn't an issue, then JamieSQL is perfect because it's faster than any other RDBMS in the world. If you'd like to pay our services group to run benchmarks for you to prove this, we'd be happy to. Just don't count on the data you wrote to /dev/null being returned from /dev/random in every case. (For boolean values we have seen up to 50% data corruption in internal tests, even when accompanied with a parity bit.) BTW, in this way JamieSQL also deviates from the ANSI SQL standard, but because I just admitted that, and that your data isn't necessarily going to be retrievable, these are features rather than bugs.

JamieSQL 2.0 now supports transactions. However, you must use the new /dev/zero API for reads, and be warned that in all cases, transactions will be rolled back and you will get zeroes (or null strings, if you prefer) for all column values upon subsequent reads. Again, this is not compatible with ANSI SQL behavior, but it's much faster, it's what we say people truly need, and because it's documented, it's a feature.

New! JamieSQL 3.0! With even better read performance for non-transactional workloads. The new /dev/urandom API has proven to be much more reliable and faster. Be aware that your data will not be returned in the same order as in the old 1.0 /dev/random API. It's a feature.

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

[maugt]

I'm sorry, thats one of the most concise and to the point comments I've read on slashdot, but I don't have mod points today.

I think this is by far the better analysis of the issue.

Re:I strongly disagree

[Anonymous Coward]

It doesn't matter how mission critical your data is, proper transaction support is essential.

programing around the lack of transaction support is a complete pain in the neck.

Re:I strongly disagree

[GoofyBoy]

>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.

This is like saying that most applications that use Linux/Unix/AIX are not so "mission critical" as they like to believe. Why not just use Windows95 or Linux 1.0? Is anything you do on your desktop "mission critical"?

>I mean really, how many people out there can truely justify the cost of a full featured, robust database like MS-Sql? 10%? 5%?

Oracle is insanely expensive to support. MS-SQL less so, just because tech support is more widely available. You don't throw around big $$ without being able to justify it, the bean-counters are there for a reason.

Re:I strongly disagree

[Daniel Dvorkin]

What doesn't MySql do well? For starters, it's much slower than Oracle, MS-Sql, and even Foxpro.

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

[Anonymous Coward]

MS-SQL is in no way a robust database, it's a glorified Sybase DB which was and still is an utter piece of crap.

Re:I strongly disagree

[mcrbids]

I mean really, how many people out there can truely justify the cost of a full featured, robust database like MS-Sql? 10%? 5%?

How about 100%!?!? Ever hear of PostgreSQL?

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

[GoofyBoy]

>I can tell you that identical queries on identical schemata with identical data are provably faster with MySQL than with Oracle.

It depends on what is being queried, the volume of data and network drivers.

So I doubt that your statement proves anything.

Re:I strongly disagree

[Osty]

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

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 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

[Daniel Dvorkin]

If you're interested: the database in question comprises about thirty tables, ranging in size from ~500 rows to over 200,000, with a fair number of three- to five-table joins. I'm not saying by any means that this proves MySQL is faster than Oracle for every query, but I think this is a fair-size test. Granted, it doesn't cover the multi-TB data warehouses which seem to be Oracle's primary claim to fame, but I know that some organizations with much, much larger data needs than my company use MySQL and seem to be happy with it.

Re:I strongly disagree

[TheRealSlimShady]

I can tell you that identical queries on identical schemata with identical data are provably faster with MySQL than with Oracle

...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

[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

[Anonymous Coward]

Going from a single Xeon (~$2500) to a Quad whatever (~$25000) would be more expensive than buying Oracle or MSSQL in most cases.

Re:I strongly disagree

[killjoe]

Postgres is great. Too bad it's not multi threaded though. That kind of limits it's performance.

Re:I strongly disagree

[dtfinch]

As far as speed, both MySQL and Oracle have their place. MySQL has a good price/performance ratio, while Oracle scales well. There are situations where one server with a couple processors and lots of ram just isn't enough for a single database. It could take hundreds of servers, several processors each, sharing hundreds of disks, and Oracle can supposedly handle that fine while still guaranteeing transactional consistency and zero down-time when one of them bites the dust.

Re:I strongly disagree

[Anonymous Coward]

"over 200,000" rows is nothing.

Re:I strongly disagree

[GooberToo]

This fact is often lost on MySQL guys. Simple fact is, more often than not, MySQL simply fails to scale.

Re:I strongly disagree

[GooberToo]

And this is exactly why, time and time again, people point MySQL users to PostgreSQL. It's for all the reasons you can use MySQL and look up to Oracle, yet, it's free and fast.

Re:I strongly disagree

[Fweeky]

Not just the license; the DBA to manage it, the devtime moving existing data to it, the support contracts etc. That could buy a lot of hardware :)

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

[GooberToo]

Opps. Sorry, I missed this in my first post. Actually, PostgreSQL is the best bang for your buck. It's free, scalable and feature rich.

Re:I strongly disagree

[GooberToo]

Man, I sure hope someone mods you up!

You're hitting 100%!

Re:I strongly disagree

[Anonymous Coward]

Yes, but you factor in the negative productivity and damage caused by opensores "LAMP" fuckheads, you'll nearly always find it's worth it to buy a real database package. See the latest issue of CIO magazine, the study is quite detailed.

Re:I strongly disagree

[LostCluster]

And this is exactly why, time and time again, people point MySQL users to PostgreSQL. It's for all the reasons you can use MySQL and look up to Oracle, yet, it's free and fast.

Most us MySQL users have a lot of things we have to get done first before we have need for any of those advanced features from any database. A database conversion project just isn't something we have time for...

Come to think about it, isn't that the reason why Slashdot's still on perl rather than PHP? PHP wasn't quite yet mature at the time Slashdot started... and even today there's just not that much of an advantage to justify recoding major parts of this site. The inertia of staying with the tool you selected the first time is pretty strong.

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

[Anonymous Coward]

Are you running the same queries on both MySQL and Oracle (ie, not using subselects, not using precompiled procedures, etc)?

Re:I strongly disagree

[mcrbids]

Wouldn't that only limit performance on a Windows system, which relies so heavily on threads due to the incredibly inefficient forking process?

Since I use Linux servers, this shouldn't be much of an issue.

Also, I tend to focus on providing services where performance isn't the issue that data integrity is. Workflow automation, Contact Management, large-ish databases, often with 50 or more tables, often dynamically allocated.

Thus performance isn't the issue - features, stability, ACID compliance, etc. is.

Re:I strongly disagree

[Baki]

Throwing more CPU doesn't help much. The problem is the disks and how these are handled. With concurrent transactions the efficiency of locking, caching and parsing of queries is crucial.

Of course, one could "reengineer" mysql into a kind of oracle or postgresql. However, given that these two already exist, why waste time and money to do that?

As for those that need/want something cheap/open/simple, postgresql already does all you want. It is irritating to see how plain and simple inertia leads to rebuilding a second postgresql instead of directly using what is better and available.

Re:I strongly disagree

[slamb]

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.

It's not that simple. If it were, everyone would be using PostgreSQL instead of MySQL. It's free, and it's better.

I think the big reason is marketing. MySQL people are really good at that. The PostgreSQL people are learning, but there's still a ways to go. Also, PostgreSQL doesn't work natively on Windows; that's one of the features they hope to have Real Soon Now.

There's also a huge misconception that MySQL is much easier to install than PostgreSQL. The process seems exactly the same to me. I don't understand at all why people think this.

Re:I strongly disagree

[GooberToo]

But that's the point isn't it? That is, if people would stop buying into the MySQL myth and picked a better solution from the start, you wouldn't need to think about a migration now, or in the future. It's features and scalability would grow with your project. In the odd event that you do need to migrate off of PostgreSQL, you'll have a far, far easier time migrating from PostgreSQL to Oracle or other RDBMS than you will migrating from MySQL to ANY platform. Their non-standard, non ANSI-SQL implementation assures that!

That's the point. Pick the right tool, the first time!

Re:I strongly disagree

[Anonymous Coward]

"I can tell you that identical queries on identical schemata with identical data are provably faster with MySQL than with Oracle."

Let's see... yesterday, I was asked to tune a SQL statement written by one of our programmers. It involved two tables, self-joined and joined to each other, with an EXISTS subquery. Table 1 had 8 million rows; table 2 had 9 million. Each row averaged about 10K of data.

Are you telling me that MySQL would have executed this in under the two hours that Oracle 9i took? If so, just how gullible do you think we are?

Re:I strongly disagree

[Splezunk]

"Free"... You mention this as your strongest reason for MySQL.... Nope, doesn't hold water... Linux is free, and I don't see a rapid adoption of Linux as a desktop over other OS's. Yes, geeks adopt it, and MySQL is adopted by geeks too... Geeks run WebHosters, and provide a "free" database for the users... Basically, when getting hosting, MySQL is bundled. I hate to say this, but when getting a computer 'XP' is bundled too... There-in lies its popularity.

Re:I strongly disagree

[Anonymous Coward]

I've done the conversion, and it's not been that painful for me. However, I tend to use Perl and DBI so that makes it relatively easy, if you stick with SQL rather than trying to do MySQL specific stuff.

Re:I strongly disagree

[RAMMS EIN]

``In a nutshell, MySql is free. Is it great? Hell no, but it's free.''

PostgreSQL can be considered more free than MySQL, and is definitely more mature (i.e. greater). I think it was also there before MySQL. At any rate, it had transactions, good locking, triggers, etc. etc. long before MySQL. I don't quite get why all the world seems to be using MySQL, even though Postgres seems so much better. Could it be marketing?

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

[Anthony Boyd]

With Oracle, watch the queries perform the same under a far greater load than MySQL will handle.

I call bullshit. In e-Week's tests, Oracle and MySQL were dead even under load.

Re:I strongly disagree

[Fweeky]

Except PHP isn't a whole lot better than Perl; much of it's even worse. Thus there's little pressure to make such a change, aside from perhaps a few PHP converts who will be easily drowned out by the vast majority of developers still happy with Perl.

This is closer to why SlashDot's still on Perl and not using Python, Ruby or Java; the latter three languages are better featured than Perl, and perform similarly or even much better, but even that's not enough to actually insitgate a change. Perl's simply Good Enough.

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

[AhBeeDoi]

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. :)

Is that a dream or a nightmare?

Re:I strongly disagree

[GooberToo]

In what way? Or is that something you read from a pro-MySQL site? Being multithreaded does not always translate into scalability or even better performance.

The flip side of that is, a single bug will not corrupt or take down your entire database. Being multithreaded means your are prone to data corruption and total RDBMS failure.

Now then, back to the performance issue. How does PostgreSQL's implementation limit its performance? The only argument I can come up with is the overhead associated with each new connection, as a new backend process is spawned.

Re:I strongly disagree

[JC-Coynel]

I mean really, how many people out there can truely justify the cost of a full featured, robust database like MS-Sql? 10%? 5%?
Er.... You meant Oracle, right?

Re:I strongly disagree

[GooberToo]

Ah! I knew something fishy was going on here (see my reply to grandparent). That explains it. I guess if the other platforms are using prepared queries properly optimized queries as needed, I'd be willing to accept those results even with caching enabled. Just the same, we both know, once that DB gets much contention, it's going to seriously crawl, especially with that cache enabled.

It's worth noting that to really be a fair comparison with the cache enabled, some additional code needs to be written for the other RDBMS to emulate the cached behavior. At any rate, assuming such an optimization would be allowed (no reason not to allow it), MySQL would quickly start falling behind even with its cache enabled and it's select only environment.

Re:I strongly disagree

[Betcour]

A single Xeon is the same as a Pentium 4, so for a single CPU you are looking at a ~$1500 system.

2 CPU systems are affordable, beyond that you should look into replication with several systems (if possible), as it will be a lot cheaper to have 2x2 CPUs rather than 1x4 CPU.

Re:I strongly disagree

[ErikZ]

The disks?

The article mentioned clustering PCs and having the entire database stored in memory. With 12 PCs running Opterons, that's 192GB of memory storage space.

And if that's not enough, then you need to consider a serious upgrade.

Re:I strongly disagree

[darnok]

> 200,000 rows is still considered to be a small
> database

No, that depends on the application.

For e.g. a football-tipping competition, 200k rows would be plenty.

Even for a company the size of IBM, 200k rows would be fine for something like an internal phone directory database. Now I'm not suggesting that IBM would even consider using MySQL for that purpose, but a 200k row limit is plenty for many many applications in both small and large organisations.

Topic for discussion: Is it more silly to try to use MySQL for your 1000 transactions/second purchasing site, or to spend $zillions using Oracle/SQL Server/DB2 for every tiny database app in a large corporation? Either way, you'd be an idiot

Re:I strongly disagree

[killjoe]

"Thus performance isn't the issue - features, stability, ACID compliance, etc. is."

It depends on your application I guess. For most applications performance is very important.

Re:I strongly disagree

[killjoe]

"Being multithreaded means your are prone to data corruption and total RDBMS failure."

Please. Postgres is the only database in wide use which is not multithreaded. Don't make up lame excuses as to why it's better then multi threaded databases.

If pre-forking was so great then all the databases would be written that way.

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

[Anonymous Coward]

This is an area that I am learning right now.
I am in fact developing a DB backend to a webified
application. Yes, I chose MySQL.
Let's see,
1) I don't give a sh*t about M$ SQL
2) I realize that MySQL has some non-standard SQL '92 syntax, but really I don't care - SQL is a pretty simple, straitforward language contrasted to
shell/perl/php. Whatever doesn't translate from MySQL to postgresSQL I'll fix when that time comes,
like most OSS developers here.
3) support - a good manager of mine once said - at Macromedia in about 1997 - if I can't find information about it to support it there's no sense in deploying it (paraphrasing). Guess what? He's right. Get online, go to Amazon/Borders, etc. There is much more info on building/configuring and coding for MySQL than postgresSQL. Maybe PGSQL
is more mature (I am still interested in it...) but if I cannot go get *all* the info I need to create a DB from scratch, right now, in this neck of the woods, it's MySQL.
If there are PGSQL fans who feel I should have chosen their DB then do like J. Zawodny and go put your OSS-style support info online so I can make a different choice. *Make a compelling arguement*

- this from an OSS zealot.
- and no, I don't care if my MySQL DB is really a
filesystem - I almost did it completely in perl...

Re:I strongly disagree

[GooberToo]

I fail to see your point. It's considered to be a small database by any real DBA with database experience. It's not domain specific (football or whatever)...it's a comparison across all RDBMs and their ability to effectively manage datasets. In this case, while 200,000 rows may seem like a lot to you, in database terms, it's a faily small dataset.

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.

Re:I strongly disagree

[Anonymous Coward]

Apache likely defauts in order to processes to preserve existing behavior and not break non-threadsafe modules (PHP etc). If there was no advantage to threads, why would have they bothered with 2.x?

For older Unix programs, threading was not often used because the APIs weren't portable and many *nixes had rather bad support. (Microsoft has been pushing threaded programming going back to the 80s with OS/2.) Postgres has a 20 year old code base, so that might be why.

Re:I strongly disagree

[mshiltonj]

MySQL has other problems 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.

At my workplace, we've recently started using MySQL much more often, and have stumbled onto these "gotchas" you mention. Each time we hit a new one, someone spends up to several hours or more, trying to determine if it's our problem or mysql's problem. The natural inclination is to ask "what am I doing wrong?" and spend unneeded time staring at our code.

So, thanks for the the "gotchas" link. That will save us some time in the future, I'm sure.

Re:I strongly disagree

[GooberToo]

If there was no advantage to threads, why would have they bothered with 2.x?

Because Apache on Windows was getting stomped by IIS on Windows in performance benchmarks. At that time, IIRC, Linux also has some process fork performance issues. The only way to address the issue on the Windows platform was to add support for threads. Since threads vs processes differs from unix platform to unix platform, the process model was kept as well. As a result, a hybrid process/thread model was created. The really cool thing is, it works well on all platforms and even allows for targeted tuning on various unix platforms to better suit the ideals for each. This allowed better tuning on Linux to account for kernel issues, which no longer exist.

In other words, it was a win-win for everyone, save only IIS.

Re:I strongly disagree

[Anonymous Coward]

If you need two or more connections to the same database, MySQL is probably not what you want. It's quite difficult to think out the explicit table locking needed to make updates and other accesses safe to do concurrently, and the engine will not detect any mistakes you might make--instead you'll simply get wrong answers. (The one exception is when all connections are read only, and you're willing to kick all users off whenever you need to update any table.)

Re:I strongly disagree

[stridebird]

Ahem, PHP and Perl cannot be so glibly compared.

PHP is almost certainly less featured than Perl - I think you could say that about any computer language, Perl is THE ultimate featured language. And that is its strength, and downfall. The huge featureset and, more importantly, the range of programming style that this allows make it a bitch to take over working on other people's code.

PHP is primarily an inline web page scripting engine and it fits logically in an HTML document. By comparison, Perl does it inside out and the HTML fits in the Perl program, making it harder to follow the flow of the page. OK, Perl::Mason and other similar modules improve the situation - this illustrates the utility of Perl, of course: the likelihood that there is already a functional module that you can simply bolt on to your Perl engine to get the job done. The module library that has grown up alongside Perl is its greatest strength, I think.

That said, PHP has a superb set of functions and I found when I started to use it a couple of years ago that I could guess function names and would be spot on about half the time.

Slashdot is still on Perl because it was written in Perl and it works. Rewriting the codebase to suit some notion of a better featured language is a waste of time. Improving performance - maybe...but that's a mod_perl issue not a language issue per se.

it's 11:59, I wanna stay alive

Re:I strongly disagree

[Anonymous Coward]

mysql> create table test (foo int) type = innodb;
mysql> begin;
mysql> alter table test modify foo smallint;
mysql> rollback;
mysql> desc test;
| foo | smallint(6) | YES | | NULL |

Transactions, eh?

Re:I strongly disagree

[Anonymous Coward]

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

Dont be stupid. Your database is your foundation. Dodgy is not acceptable! Dont ever ever work for my company, thinking like that will put us out of business - idiot!

Re:I strongly disagree

[Anonymous Coward]

Your post attempts to formulate a

( ) technical (*) elitist ( ) copy/pasted ( ) well thought out

reason why MySQL should not be used. Unfortunately your post is flawed for the following reasons:

(*) InnoDB (and therefore transaction support) has been there since 2001.
(*) It's used mainly by websites with no clue what the R in RDBMS stands for.
(*) It performs fine for 100% of its users and has done for years.
(*) It scales just as well as its commercial rivals as real world testing has shown.
( ) Most people don't need/can't write stored procedures.
(*) Typing "auto_increment" is easier than writing a trigger.
( ) Its silly to insert numbers into an "auto_increment" column and expects it to work.
( ) You didn't RTFM.
( ) ENUM only works for character values, as stated clearly in TFM.
( ) Case insensitive character fields went out in 1980.
( ) Table-types not supporting foreign keys suprisingly won't support foreign keys.
( ) Same goes for transactions.
( ) If you don't define a table type MySQL cannot guess what you want.
( ) A few clueless devs does not kill the project.
( ) It works fine cross-platform, and has done since about 1942.

Specifically your complaint fails to account for

( ) The fact that millions of people are quite happy with this system.
( ) The fact that you don't know better than those millions of people.
( ) The fact your head is stuck up your ass.
( ) Elitest RDBMS pricks.
( ) PostgreSQL not supporting clustering or replication.
( ) PostgreSQL for years having an appalling installation method while MySQL was easy.
( ) Row locking having been in MySQL since 2001.
(*) ACID Transactions having been in MySQL since 2001.
( ) MySQL being able to handle as many concurrent users as the hardware supports with InnoDB with NO data loss or corruption.
(*) Many applications working fine when ported between databases by someone not you.
(*) The fact that most RDBMS have their specific quirks.
( ) VHS / Betamax.

However had you mentioned the following your argument would have been more valid

(*) MySQL devs pushing users towards working around limitations in their database.
(*) The non-standard DATE/TIMESTAMP columns.
(-) The lack of stored procedures (and how they will be here soon in MySQL 5).
( ) The lack of >255 character VARCHAR fields and a good reason why you cannot use TEXT.
( ) The fact that loads of crappy old web apps use MyIsam tables which are basically an SQL interface to flat text files.
(-) The lack of subqueries.
(-) The lack of views.
( ) MySQL bigots.
(*) Some valid reason why you need the above, that isn't just you trying to be an elitist RDBM prick.

And the following philosophical objections may apply

(*) Get over yourself.
( ) Do you think it would be where it is today if you were right?
( ) Don't you think the fact it is so popular means you're wrong?
( ) Haven't you got anything better to do than whine?
( ) Aren't access users an easier target?
( ) Violence is the last refuge of the incompetant.
(*) Please take a shower.

Furthermore, I would just like to add

( ) You almost made a good argument, but just fell short.
( ) Your arguments have been made and refuted a million times already.
(*) You are blatently an elitist with no real world experience of what you spout opinions on.
(*) You smell, go wash.

Furthermore:
wtf are you on about, indexing is done as part of table design, if you can't design tables then you're screwed on any database. I wish people would stop randomly modding up MySQL Flamebait.

Re:I strongly disagree

[period3]

If you want to learn, get yourself a real RDBMS

I hate to be picky, but real RDBMS's don't exist. Nobody has made one that satisfies Codd's 12 rules.

You must be talking about SQL DBMS's. See http://en.wikipedia.org/wiki/RDBMS

Re:I strongly disagree

[tuffy]

I don't quite get why all the world seems to be using MySQL, even though Postgres seems so much better. Could it be marketing?

Historically, it's been documentation. MySQL is very well documented from the installation to the DBMS administration to the API. So, even though it may not be as feature-complete as PostgreSQL, people gravitate to it because they can figure it out faster. That's not to say PostgreSQL doesn't have plenty of docs, but MySQL has a lot more of them.

Re:I strongly disagree

[WuphonsReach]

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.

It also lacks (the last time I looked) the ability to run on a Win32 box.

Which means that as much as I'd like to take it for a test drive, I can't do so without building a unix/linux box. (Another thing on my wish list.)

Re:I strongly disagree

[Daniel Dvorkin]

I fail to see your point. It's considered to be a small database by any real DBA with database experience. It's not domain specific (football or whatever)...it's a comparison across all RDBMs and their ability to effectively manage datasets. In this case, while 200,000 rows may seem like a lot to you, in database terms, it's a faily small dataset.

I've been working as a DBA for over five years, with DB applications ranging from supply-chain management to e-commerce to bioinformatics; are you going to tell me I'm not a "real DBA with database experience?"

200,000 rows by itself is not a whole lot of data, I agree. But when you start doing lots of multi-table joins, the effective amount of data grows very, very quickly; 200,000 * 5,000 * 1,000 * 500 (a fairly common situation for this particular app) puts you in some serious territory. And my point is, once again, that for this application, which I -- with considerable education (CS grad student specializing in computational biology and data mining) and experience (the aforementioned five years in the field) consider a reasonable test of any DBMS -- MySQL performs very well, and in fact outperforms Oracle. That's all.

Re:I strongly disagree

[duffbeer703]

Your statements are highly misleading.

mySql lets you pick one:

1. Fast query performance
2. Row locking & transaction support

And when people talk about "minimal cross-platform compatibility" wrt MySql, they are talking about migrating from Oracle/Informix/DB2 to MySQL.

MySQL is cheap and delivers common functionality required by relatively simple database appliations. That's the only advantage.

Re:I strongly disagree

[MattRog]

It may or may not outperform Oracle. I'm sure Oracle would be plenty fast in that situation, too, so it really doesn't matter. Is "faster" better than "acceptably fast"? I'm sure you'd get much better performance from writing your own DBMS without going through MySQL.

Of course, what about the costs that are incurred when you have to procedurally implement your own constraints in your application logic? Or the cost of cleaning up your data when something goes wrong? These kind of costs are hidden in the single-query performance test.

Re:I strongly disagree

[quantum bit]

The article mentioned clustering PCs and having the entire database stored in memory.

Hope you have a REALLY good UPS. Might want to throw in a diesel generator too just for good measure...

Re:I strongly disagree

[arkanes]

Oracle is insanely expensive to support. MS-SQL less so, just because tech support is more widely available. You don't throw around big $$ without being able to justify it, the bean-counters are there for a reason.

Sadly, this is totally untrue, in both my professional and private experience. I've see a LOT of money wasted on Oracle solutions when SQL Server would have been fine. Or Postgre, or even MySQL. In fact, using insanely expensive "enterprise" applications when they don't need to is a major waste of money in my experience, but you've got the buzzwords to "justify" the cost.

Re:I strongly disagree

[GooberToo]

Dang it. I guess someone did what I asked. My fault. At any rate, I was actually hoping the original parent (this post's grandparent), would be modded up. While I thank you for the karma, it was not my intention to have the parent post modded up.

LOL.

Re:I strongly disagree

[arkanes]

Everything you're saying is based on process-level protection provided by the OS. If the OS provides sufficently correct thread-level protection, then almost all of those issues become a dead heat. Also, it's not just spinning a thread vrs forking a process - process-level IPC is slower than thread-level IPC as well. And process-level IPC is no safer than thread-level. If one of your processes corrupts and crashes during a write, your database is just as hosed as if a thread did it.

On Windows, at least, threading is far more performant - you _must_ thread for very fast IO, network or disk. Now that linux has a non-crap thread library, that may end up being true there also. The most reliable and performant databases in the world are threaded - thats a logical argument in favor of threading.

Re:I strongly disagree

[TheSquareRoom]

"Minimal cross-platform compatibility"? I have to disagree.

True story: I worked for a small company that did both IVR and hosted an e-commerce web site. Our infrastructure was a fairly typical pile of garbarge: mostly SCO and Windows, with a few Red Hat boxes thrown into the mix. The kid in charge of things didn't want to hear about anything other than Postgres. Well, maybe Postgres is better - I didn't work with it long enough to form a definite opinion. (My first impression was that the whole business with keywords was kind of daffy and why was it so hard to figure out how to write the current datetime to a table? What's wrong with "2004042132417"?)

It may be that in theory, Postgres is better. In practice, it sat there and did nothing because the postgress client "pg" (insert obvious joke here) didn't want to run on SCO. Well, obviously , the solution was to get rid of SCO, but easier said than done, we had it. What we didn't have was the money and people necessary to get rid of it.

I used Oyama Hiroyuki's Pure Perl MySQL module on SCO. It worked fine and required no libraries or dependencies beyond the usual stuff like IO::Socket. My system was up and running for months while the Postgres kid and the system administrator sparred over who was responsible.

"Slower than Oracle, MS-SQL, or Foxpro"? By hearsay, everyone I know who runs Oracle on an x86 says it's a dog. I can't speak for MS-SQL. And I can't believe you threw Foxpro in there. I programmed in that stuff for ten years. Unless your idea of joy is race conditions, file corruption, locking problems, and pinning the Disk IO meter I would recommend that you use something else.

I benchmarked some queries, and for what we used it for MySQL was faster as Postgres. I advocate it for the the following reasons:

Speed.
Reliability.
Simplicity, because to be honest with you, we weren't that smart and needed all the help we could get.

Also database replication works better in MySQL than with those Perl scripts bundled with Postgress.

Maybe Postgres is better if you're doing some kind of data mining stuff that really requires brains - I couldn't tell you.

Re:I strongly disagree

[Fulcrum of Evil]

Topic for discussion: Is it more silly to try to use MySQL for your 1000 transactions/second purchasing site, or to spend $zillions using Oracle/SQL Server/DB2 for every tiny database app in a large corporation? Either way, you'd be an idiot

You do know that Oracle (and most DBs out there) can host multiple DBs in a single instance, right? All of your tiny apps can be hosted on one server with one license fee.

Re:I strongly disagree

[GooberToo]

Well, without digging deeper, we're faced to look at it rather standoffish terms and compare based on common standards.

Now then, add a couple of tables with million of rows and maybe some with 10s of millions, with joins, and I think you'll find that you're in serious territory of what people commonly consider to be large databases.

Based on that, your database, no matter how proud you are of it, is still generally considered to be a small database (medium at best).

Re:I strongly disagree

[GooberToo]

Everything you're saying is based on process-level protection provided by the OS.

Exactly. The OS is helping to ensure database integrity.

If the OS provides sufficently correct thread-level protection, then almost all of those issues become a dead heat.

Except, it's impossible for the OS to sufficently correct for thread-level protection while still providing the advantages in speed of thread level IPCs. So, it's safe to say, it's impossible for a threaded implementation to, "become a dead heat", with a process based implementation in that regard.

Also, it's not just spinning a thread vrs forking a process - process-level IPC is slower than thread-level IPC as well.

This is true. Just the same, thread level implementations tend to require more IPC synchronization, thusly, on average, the threading IPC speed advantages tend to become noise. This is because threaded implementations tend to have much higher levels of contention on shared resources. On average, I'd consider it to be a wash.

And process-level IPC is no safer than thread-level.

I'm sorry, but this doesn't entirely address the issues. Have a shared resource on the stack? IF you walk on the stack, you potentially just corrupted state for ALL threads and brought the state of the entire process into question. The same holds true for shared data segments.

If one of your processes corrupts and crashes during a write, your database is just as hosed as if a thread did it.

Maybe, maybe not. This is why we have transactions. If the corrupt transaction fails to complete, it's rolled back. Conistency is maintained. On the other hand, it's very possible, in a threaded implementation, to actually have the field value corrupted (it's length or value, etc), and have it still be written to disk. In otherwords, as I originally stated, a process implementation greatly reduces the chances for corruption. I did not state it eliminated it.

On Windows, at least, threading is far more performant - you _must_ thread for very fast IO, network or disk.

Because MS made the decision to bais threads over processes. On Linux, even with the old thread implementations, threads were really LWP and were not significantly faster at spawning than processes. That was because forking on Linux so fast compared to other platforms.

Now that linux has a non-crap thread library, that may end up being true there also.

Well, that addresses Linux and threads, nothing else. Remember, PostgreSQL runs on many more platforms than just Linux. It's always going to be a toss up on other Unix platforms to determine which is best (threads vs process). This is exactly why Apache 2.x implemented it's hybrid model. That is, so that they would be fast on MS' platform and fast on Unix platforms, allowing each to tune best for each unique platform.

The most reliable and performant databases in the world are threaded - thats a logical argument in favor of threading.

Bzzz. Popularity hardly makes right. Best of all, popularity hardly invalidates the issues which I put forth when contrasting the two models.

Bluntly, neither model is perfect. Just the same, it's far *easier* to maintain consistency, uptime, and reliability with a process implementation. Notice that none of those speak to performance. Even if you refuse to accept that threading is not inherently faster than processes, which is more important to you? ACID or speed?

Re:I strongly disagree

[killjoe]

Apache pre forks and this fact is reflected in all those benchmarks where aolserver and iis beat the crap out of it in performance.

Maybe pre-forking makes apache more stable and solid but it certainly does have a performance penalty.

Re:I strongly disagree

[killjoe]

While forking may offer some stability advantages none of what you say actually disputes my post. I simply stated that not being multi threaded limits the performance of postgres.

Apache is slower then aolserver. Maybe it's more robust or resilient to crashes (don't really know) but it's certainly slower.

Finally there is no law that says that threaded applications are inherently less stable. As I said virtually every other database in the marketplace is multithreaded including some renknowned for their stability and reliability like oracle, db/2, mssql server, mysql etc.

If forking were truly advantagous you'd think oracle or IBM would have made their database forking by now.

Re:I strongly disagree

[GooberToo]

Apache is slower then aolserver.

Are we supposed to think that statement means something? You do realize that aolserver was written to be fast, above all else. Apache was written to be robust and portable, above all else. The simple fact that Apache is still wicked fast pretty well says all that needs to be said.

I simply stated that not being multi threaded limits the performance of postgres.

In what way? Again, I'm forced to ask. Even in MySQL's best performance cases, it's generally not hugely faster than PostgreSQL. Why? Simple fact is, PostgreSQL has much more complex functionality and many more code paths. It's to be expected that this is going to have some runtime costs associated with it. Just the same, according to your logic, MySQL should always be faster because it implements a threading model. Contrary to that logic, PostgreSQL's process model out scales MySQL's threading model, in spite of the fact that it's executing more code. Hmmm. I guess we can pretty well ignore your theory and call it what it is. Garage.

Finally there is no law that says that threaded applications are inherently less stable.

Law? No. Fact? Yes. The threading model requirements mandated on MS platforms is often pointed at for being a primary reason of lower uptimes and lower application availability. Worse, in MS' case, thread level resource issues tend to be tied up for the life of the process. This means, if a thread segs and the process is kind enough to catch it, any leaked resources from that thread will remain leaked (or locked, as it pertains to DLLs) until the process exits. This, in turn, raises questions of system uptime, beyond that of application uptime.

As I said virtually every other database in the marketplace is multithreaded including some renknowned for their stability and reliability like oracle, db/2, mssql server,

All have had bugs, PostgreSQL included. All have crashed at some point in time. The question is, did a single session crash the whole database? In PostgreSQL's case, the answer is no. In all the others you named, the answer is, quite likely. Furthermore, you can ask, did a single session corrupt one or more sessions, when using a threading model? The answer is, it's possible.

If forking were truly advantagous you'd think oracle or IBM would have made their database forking by now.

Or, maybe economics, as with any company, is a deciding factor. I don't think you realize the cost and effort that would be involved in converting from one model to another. As with any point of contention, it's often about risk management. Are the risks being mitigated? Are tools available to fix the resulting corruptions, when they occur?

Re:I strongly disagree

[mla_anderson]

No it can run on Windows (but under the cygwin environment). It actually works very well. I've used the windows port for testing on my desktop never for production.

There's two ways to install the Windows port:

1. Grab the Proof of Concept from Postgres, or
2. Install the full cygwin environment

I prefer to have the full cygwin environment, but my first install was with the POC and that worked well.

Re:I strongly disagree

[Anthony Boyd]

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.

Yes, and if we disable Oracle's indexes or compression schemes, its performance will drop too. What a bizarre argument.

Re:I strongly disagree

[Anthony Boyd]

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.

I call bullshit. Again. According to the article, the test "stressed all facets of typical DBMS operation, including insertions, deletions, updates and searches."

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?

You can read it for yourself, and even download their code and run the tests yourself. The answer is that each database was custom-tuned. They used both default tables and Inno tables.

Re:I strongly disagree

[Osty]

Your test is flawed. DDL (Data Definition Language) constructs generally are not transactioned in any database. That means creating a table, altering a table, dropping a table, or any other object don't care whether or not you're in a transaction. (also, I don't care for the "begin/rollback" syntax, since "begin" should be paired with "end" as block constructions similar to "{" and "}" in C/C++ while "rollback/commit" should be paired with "begin tran[saction]"). Try your test on an insert instead.

MySQL does have transaction support. My problem with it is that it's not always obvious that the transaction support is available, and even if it is you have to specifically take steps to allow your tables to work with transactions. If you don't have "type = innodb", or you do have that but your installation (or your hosting provider's installation, or your work's installation) of MySQL doesn't have innodb, you're screwed and probably don't even know it. MySQL should throw an error if you try to set a table to a type that doesn't exist in the target server, but it doesn't. That goes along with MySQL's other half-assed attempts at figuring out what you meant to do and getting it wrong rather than just throwing an error and letting you fix it properly.

Re:I strongly disagree

[mcrbids]

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

I've found that it's relatively easy to port MySQL stuff to Postgres. Going the other way is frequently very painful.

Postgres is generally a feature superset of MySQL, so can thus handle most things that MySQL handles with minimal hassle.

Re:I strongly disagree

[Anonymous Coward]

Perl is THE ultimate featured language.

Lie - just because you can write a normal program using only punctuation doesn't mean perl has "features". See Common Lisp for a real language.

Re:I strongly disagree

[Anonymous Coward]

I'm not saying by any means that this proves MySQL is faster than Oracle for every query, but I think this is a fair-size test.

It's not the size of the test, it's the scope.

If you're doing all your business logic in your app, then MySQL will seem faster, because all you can test is how it transfers data.

Putting the business logic into the DB (with stored procedures, etc.) and then try to claim that MySQL is faster.

It may be faster to transfer data, but if that's all you're doing, you might as well use flat files, because they'll be even faster still.

Properly written, anything except the smallest app app will be faster with Oracle than MySQL - every time.

Re:I strongly disagree

[sqlgeek]

Hmm. I don't think you've ever worked with an Oracle database, I really don't. Here are some _important_ performance features in Oracle that MySQL does not have, and may well never have:

- Choose varying parse strategies as your data grows for optimal access

- Choose varying parse strategies based on the histographic distribution of the data

- Re-write NOT EXISTS or NOT IN clauses as merged anti-joins or nested loop anti-joins

- Re-write EXISTS or IN clauses as merged semi-joins or nested loop semi-joins

- create aggregated data tables and have the database transparently maintain the aggregated data as the underlying granular data changes

- Re-write queries against granular data so that they can use aggregate data (when available)

- Give analytic data about the entire result set or partitions of it at the individual row level (i.e. what is the rank of each book by sales within each genre -- with a single query)

- define rules to extrapolate values for missing data elements within a result set

- store an entire table in an index and then treat the index as if it was a table transparently

etc, etc, etc.

When I say that MySQL may well never have these types of feature it is because it is very, very hard to provide them in a robust, fault-tolerant, enterprise database. It is expensive -- and then so is Oracle.

Cheers,
Scott

Re:I strongly disagree

[dcam]

I've been working as a DBA for over five years, with DB applications ranging from supply-chain management to e-commerce to bioinformatics; are you going to tell me I'm not a "real DBA with database experience?"

Yep. The point here is scale. Call me when you start measuring your databases in GB and rows (in tables, I'm not interested in what you can generate using cross joins) are being measured in millions.

As I said before the issue here is one of scale. MySQL is fine for small scale stuff, like what you are doing. In some cases it would be inappropriate for small scale stuff (high inser/update environment), but when you are talking serious data MySQL doesn't cut it.

You are (probably) using the appropriate tool for your environment (low insert, small size), but don't expect it to be the right tool for the job for everyone. Deal with it and move on. "Thats all".

Re:I strongly disagree

[GooberToo]

You might bother to read the article. It appears you are full of BS.

MySQL used Inno, MyISAM and cached results. News flash! MySQL's cached results are only of value when the result sets rarely change. This proves that it was a select only operatoin. Worth noting, that every result set delivered from cache, avoids MySQL's horrible scaling issues. Issues, I might add became very obvious when the cache was disabled. According to the results, they preformed only 2/3 as good without the cache enabled. I think that speaks loud and clear as to the performance of MySQL. Especially given that given MySQL BEST FRIGGEN CATEGORY, without the cache, they are a full 1/3 slower. Putting that into perspective, that places them in second or third FROM LAST in the area where its as its best! The next question is to find out if any of the queries were optimized (as it doesn't appear to say) rather than using the least common donominator (broken-MySQL-SQL). Chances are high, each of the databases, save MySQL, were using far for optimal SQL.

It also appears, exactly as I feared, that they ignore all of the significant performance features offered by all of the other databases. Specifically, it does not appear that they properly converted their queries to prepared queries. On a load test, that can make a HUGE difference in performance as it prevents each engine from constantly having to reparse the queries. AFAIK, MySQL is the only DB that doesn't support that feature yet all of them were forced to slow down because MySQL doesn't support them. Combine all these with the simple fact that they enabled result set cache means that the results are worthless. The closest result set you can get would be, by simple estimates, would be to adjust MySQL's peformance downward by 33% and all the others up at least 20%. Even without adding a 20% estimate, which is reflective of the benchmarker's incompetence, MySQL quickly finds it self at the back of the pack. Wow. What a friggen surprise.

Long story short, you can call BS all you want. The only problem here is that the results are biased and are complete BS. Either you'll make an effort to learn more about their invalid conclusions and you can continue to be deluded. The choice is yours. The only conclussion that anyone who actually understands RDBMS can draw from this is that MySQL is still a slow performer and that the results are horribly biased toward MySQL.

Re:I strongly disagree

[TheRealSlimShady]

What a bizarre argument.

Not at all. It just serves to illustrate the point that there obviously aren't many changes taking place to the database (i.e. light load) and therefore the database is returning most of it's results from cache. All you can really take from these benchmarks is that MySQL has a fast cache - not that it's "faster" than Oracle, MSSQL or Postgre.

Re:I strongly disagree

[Anonymous Coward]

I agree - 200,000, or even 1 M rows, are considered to be a really small database for PostgreSQL, with concurrent select/insert/update activity.

My company uses multiple PostgreSQL with millions of rows in a production environment, inserted, updated, deleted and selected concurrently, virtually non-stop. Such load would quickly bring MySQL to its knees.

Not to mention we make extensive use of foreign key constraints and stored procedures - I cannot imagine how we would work without these (i.e. MySQL).

Re:I strongly disagree

[justins]

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)

Which is laughable in and of itself.

Re:I strongly disagree

[Wolfier]

>there is no law that says that threaded applications are inherently less stable.

There is also no law that says that not being multithreaded will limit the performance of a program.

Your point is?

Re:I strongly disagree

[killjoe]

"Your point is?"

My point is simple. I will state it once again even though I have already stated it many times.

In this case the lack of multithreading has hurt the performance of postgres.

Re:I strongly disagree

[Wolfier]

I don't see it, your performance argument is totally hypothetical because nobody has seen any multithreading performance numbers with PostgreSQL.

Such comparison only makes sense if you can use the same database, turn on and then off multithreading, and perform the same test.

In our environments (GBs of data) we do not see any performance hit due to the lack of multithreading.

As for comparing the multithreaded performance from MySQL and the multiprocess performance of PostgreSQL, you do realize that PostgreSQL is doing much more than MySQL, right?

I highly suspect that, by the time MySQL accomplishes 80% of PostgreSQL's feature list, it'll totally lose its only advantage: higher performance on read-only operations.

Re:I strongly disagree

[Anthony Boyd]

MySQL's cached results are only of value when the result sets rarely change. This proves that it was a select only operatoin.

I'm sorry, what proves it? The fact that MySQL has a cache means that only SELECT statements are used in the test? Or are you just hoping that your assertion is enough, even though e-Week asserts otherwise? Hmm. What authority do I trust? A Slashdot random with the name "Goober" or e-Week? Hmm. How about I just download the test materials and see for myself? OK. Looking at newUser.jsp, final.jsp and other files used in the Nile testing application, I'm seeing INSERT and UPDATE statements. I guess that must be mighty inconvenient for your "proof" so I'm sure you find some explanation for it.

Specifically, it does not appear that they properly converted their queries to prepared queries.

I don't think PreparedStatement is the home run you imagine it to be. In fact, I and many others might prefer to let the dynamic SQL ride. But if you're so gung ho, grab the code and rerun the test with your changes. I look forward to the published, reproducible results.

The only conclussion that anyone who actually understands RDBMS can draw from this is that MySQL is still a slow performer and that the results are horribly biased toward MySQL.

Ooohhh. Anyone who actually understands a RDBMS would agree with you. And suddenly we're in Junior High debate class. You can't honestly think that I'm going to fall for an amateurish version of the No True Scotsman fallacy? How old are you?

Re:I strongly disagree

[GooberToo]

You really are a joke.

Everytime the dataset is updated, the cached result set has to be updated. Everytime this happenes, the performance of the DB is going to be as if the cache were disabled. The fact that there isa 1/3 difference in performance speaks loudly that, by far, the result set being obtained is fairly static. Otherwise, the cache would be worthless.

Along those lines, to be fair, each database that did not have a cache feature, should have been using a "cached" result table, whereby, they selected the result into the table and then returned the result set until the result set had changed. Basically emulating this feature. But, I guess they didn't want to be fair. Along those lines, since most (all) all other of those DB's cold EASILY implement a cached strategy, in the databaes, by simple use of triggers. But, I guess they didn't want to have an even playing field.

As for prepared queries, those can and often do make a huge difference on a system that is under load. Period. For PostgreSQL, the difference isn't as large as some DBs, but it is very noticeable, especially on load tests. This leads to significant differences on a heavily loaded system. Typical improvements, especially on moderately simple and very complex queries, can translate to 10% to 20%. Especially so when the returned results sets are small, quickly obtained by the DB, and quickly returned. Prepared queries don't make sense when it takes long periods of time to result a complete result set. On the other hand, such queries wouldn't make sense in a web serving environment. As such, it's VERY safe to say that the return on preformance would of been large.

The point, which you've obviously managed to close your mind and eyes, to ignore is that these are hardly representative of any of those DB's, and the tests is, very biased toward MySQL. The second that they decided to use cached results to hide a significant performance issues (yes, 33% is significant), if they wanted to remain unbiased, they should of invested effort , at a minimum, to do the very common (sense) items to all of the other databases as well. The fact that they ignored prepared queries makes it very easy to understand that the purpose of the tests were to be biased. After all, AFAIK, all of those databases, save MySQL, support such a feature. Accordingly, it's unforigvably moronic that they ignored such a common and obvious feature. But, since the objective was to be biased, it's obvious why it happened.

After it's all said and done, you can either grow up and face facts for continue to be jealous (for whatever crazy reason) of other database solutions. Until you make such an effort, it's obvious that you're not doing anything but showing off your ignorance and trolling in general.

The fact that my id is an issue to you, wonderfully highlights just how closed minded and biased you are. Need I say more. It's one thing if I stated anything that wasn't true. I didn't. It's another thing to ignore a statement, no matter how true, just because you don't like the messenger. How petty. You have my pitty.

I think at this point, anyone that stumbles on those portion of the thread will easily ignore anything you've stated for many reasons. One, you offered no facts or anything that is supported by database reality. And two, you come off as nothing but an ignorant sour grape. I think others, as I have, will easily see right through you.

Re:I strongly disagree

[GooberToo]

Not really.

Since the use of the cache results in a 33% performance boost, it's obvious that it's mostly a select only environment. To be fair, they should of implemented this cached result behavior in all the other databases by the use of a stored procedure or two, one extra table and a simple trigger. Had they done this, all of the other databases would of seen something like an extra 33% improvement in performance.

Basically, the fact that they had to use the cache to hide the poor performance of MySQL, speaks loudly. Had they bothered to level the playing field, as expected, MySQL would of been last or nearly last in this round up. This ignores the fact that they didn't use prepared queries in all of the databases that support them. Again, this would of further placed MySQL in last place as it doesn't support prepared queries.

Long story short, the provided link proves two things: One, PCMag continues its long history at poorly benchmarking anything that is complex. Two, MySQL is a dog of a performer.

Re:I strongly disagree

[GooberToo]

I'm actually reading the review in detail. LOL!

What a friggen joke!!

Oracle and MS SQL went untuned. Everyone knows Oracle requires good tuning. ASE tests were done with a crappy JDBC driver. As was MS's database. In fact, they used a beta driver for MS. Rightly so, many are calling this a JDBC test, moreso than anything else.

It gets better! LOL! Just wait!

They differed the number of allowed database connections per database. That's a huge variable right there. That alone, is reason enough to invalidate the whole friggen test. That one variable will greatly effect the amount of contention in each database. What a joke.

But wait...there's more...

"the throughput leveled off or declined, or the number of errors exceeded 10 percent."

Errors! What friggen errors!?!? There should be no errors, let alone 10%! Yet, they don't explain further. What crap. If they have any noteworthy percent in errors, or even an allowance for, they screams that they don't have things configured and running properly. Period.

They did allow for index and optmizer tweaking, but MS and Oracle went untuned. Ack! What garbage this is!

Oh wait...this is awesome. They doubled the work for ASE because they were having problems with the JDBC drivers. This really is a JDBC test drive and in no way reflects actual database performance. What a joke.

"At the bottom of the heap on the JSP test was Microsoft SQL Server 2000, with a peak of 209 pages per second. This was probably the result of a poorly performing JDBC driver that's still in beta. We tried a newer version of the JDBC driver supplied by Microsoft, but this had no impact on performance."

Ya, that and the fact that you didn't have anyone tuning the friggen database. Plus, you didn't bother to implement a static query cache. Plus, you didn't bother to implement prepared queries. Plus, you didn't have anyone tuning the friggen queries. What a friggen joke!

Plus, they state early on that they allocate memory to allow for connections up to what memory allows for yet they have memory left over for cache and reduce the number of connections (contention) in MySQL, further biasing the results. This is nothin but a sad friggen joke. Only dolts and idiots are going to think these tests have any meaning at all. They don't. Hell, three vendors are knocked right out, before the testing ever started, because of the stupidity of it all. But, if MySQL guys want to claim victory in a fixed and biased test, more power to them. What a friggen joke.

One also has to wonder how much of the test queries could of been implemented as stored procedures. All of the databases, save MySQL, support this feature. Doing so can often make huge performance differences. In some cases, it can make an order of magnitude difference. But hey, we all know this MySQL baised test actually means something. Oh well. Then, they completely ignore prepared queries, which can make a huge difference on systems that are under a load. But, oh well. They really didn't want to compare performance at all. At least not on a level playing field.

And, I'm just scratching the surface!

In short, if you really think this test really validates MySQL in any way, you are an absoluete moron. I can only hope that you are not a DBA. If you are, you should do your company a favor and and jump off a cliff because you are unfit for DBA duty.

Re:I strongly disagree

[Anonymous Coward]

What a moron.

Sadly, you don't have the foggiest idea what you're saying.

Re:I strongly disagree

[Anthony Boyd]

It's sad to see you disputing evidence and tested theories with nothing but rhetoric and name-calling. Oh well. On to more productive discussions.

Re:I strongly disagree

[Anonymous Coward]

Visual FoxPro supports transactions.

Visual FoxPro is faster than MySQL.

Re:I strongly disagree

[GooberToo]

LOL.

That's what I thought. Facts are a hard thing to dispute. It's easier to walk off than admit your wrong.

Oh well.

Re:I strongly disagree

[GooberToo]

I couldn't resist.

What evidence am I disputing? Everything I stated is based on information directly provided from the review and direct knowledge and understanding of the various RDBMS systems and their features.

Please, be direct. What did I miss?

This should be good. It's going to be far easier for you to just admit you were either cheerleading or simply mistaken, than to dispute the facts. The fact is, MySQL is a dog and they went out of their way to create a test which falsely pushed MySQL into the lead. In other words, either they rigged the tests or they were incompetent. take your pick. Honestly, PCMag has a very, very long history of being incompetent with any moderately complex benchmark, so in their own defense, chances are high that they were simply incompetent rather than a willful attempt to mislead.

Here are the facts directly taken from the review:
ASE has broken JDBC drivers and they changed the test to double it's workload so they could work around it. MSSQL and Oracle went untuned. MSSQL also had a bad JDBC driver, which was in beta testing at the time. They did not used stored procudures even though it may have dramatically sped all of the databases up, save only MySQL. And that's because MySQL doesn't support them. They did not use prepared queries even though it may have dramatically sped all of the databases up, save only MySQL. And that's because MySQL doesn't support them. They then turned on a feature in MySQL (query cache), which could of easily been emulated in ALL of the databases by simple means of one extra table and a couple of stored procedures. But, they decided to slant the results toward MySQL. They then reduced the number of connections allowed to MySQL, thusly further reducing the contention within the database. That directly translates to falsely elevating its scalability statistics.

So, there's the simple facts. Are you now saying that the facts from the article are wrong because you'd have to admit you were wrong?

Re:I strongly disagree

[GooberToo]

I think I've explained this twice now. You're full of BS. I've directly asked you to explain why being multithreaded translates to higher performance or even higher scalability.

It's rather obvious that you have no idea what you're talking about and you are simply parroting something that some pro-MySQL site told you. Simple fact is, being multithreaded, on most platforms, directly translates to slower performance. That's a fact. The significant expection to that fact is the Windows platform as MS hugely biased performance to allow for threaded applications to excel. Having said that, it's still rather easy to make a higherly threaded application perform like crap.

Add in the fact that threaded programming usually requires a significant amount of additional locking (which is costly) compared to a process model and shared memory. This directly translates, even when talking about Windows, to a significant amount resource contention on a per thread basis. In otherwords, any speed advantage gained from a threaded model, compared to a process model, is usually lost in simple locking contention.

That's the facts. What does this mean? It means that most threaded applications are always going to perform on par with process based application. Anyone that says otherwise, better be stating specific exceptions for specific corner cases, otherwise they are lying their butts off.

Care to counter or are you willing to admit you don't have any idea about which you speak? Now then, for the third time, specifically and exactly how has a process model hurt the performance of PostgreSQL?

Re:I strongly disagree

[Anonymous Coward]

Hey everybody! Come see! It's the crazy person who posted at least 26 replies here over the course of 3 days! I'd say "get a life" but maybe just "get up and shower" would be more appropriate! Ew ew. Having creepy thoughts about what you might be like IRL. The Postgre team must dread having you for their mascot. They probably don't sleep at night anymore, what with all the public relations nightmares! Bwahahaha!

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:I like MySQL

[GoofyBoy]

>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.

What sort of local bar do you have where they would even know what a "database" is?

Re:I like MySQL

[Anonymous Coward]

"average ... Ho"

"go down to the local bar and talk to the local people about local people's reality."

Sounds like you need some local training in dealing with local members of the opposite sex.

Re:I like MySQL

[Anonymous Coward]

Yes, being a wussy, ultra polite pushover out to please women with gifts is definitely the way to go.

Re:I like MySQL

[GooberToo]

You should look at it like this. In one hand, you have an ounce of your favorite food. It has MSG on it a little bit too much salt. In the other hand, you have a pound of your favorite food. It is salted just right and has no MSG. You can only pick one. Your buddy and the talk around town says to pick the one ounce item as it can easily fit into your mouth. Which would you pick?

Obviously, all things being equal, people should be picking the one pound hand, to be eaten at your leisure. Yet, far too many people are listening to their buddy and the talk around town. Sadly, there's a lot of people running around with too much salt and MSG in their system and a hunger they can't satisfy.

I don't think it's all that hard to figure out the parallels here. ;) :)

Re:I like MySQL

[Tukla]

:: chuckle ::

Re:I like MySQL

[Tukla]

Does this mean that I use Mckoi DB because I have no self-esteem?

Man, I gotta get a therapist.

COPY AND PASTE KARMA WHORE

[Anonymous Coward]

mod down asap

A Good DBMS for Beginners

[mattlary]

For myself and many other college students, MySQL was a great system to play with SQL and experiment on. It's easy to setup on most systems, and it's also fairly easy to use (not to mention free). Don't get me wrong- I've used other systems that are much better than MySQL, but without MySQL to get my hands dirty, I'm not sure I would have bothered with anything else.

Re:A Good DBMS for Beginners

[GooberToo]

The problem is, most people would disagree. Why? It's not because many hate the DB....and many do. It's because MySQL teachs many non-standard and down right bad habits. If you want a system to learn SQL or relational theory, MySQL isn't right for the task. Why? Because very little of the learned knowledge will apply to any other system. If, after all, the intent is to learn a generalized body of knowledge, then by definition, you can't use MySQL.

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.

ASUG 2004

[webword]

I'm mainly interested in the SAP and mySQL connection because I simply didn't know about it until I read this. I know it is a bit offtopic, but I recently attended ASUG 2004 (America SAP User Group) and I posted news about it to my site. Perhaps you'll be interested.

JavaScript, DOM, and Page Reloads

Usability Interview with David Clark of TandemSeven

More Observations on ASUG 2004

ASUG 2004 and RFID

Why MySQL Grew So Fast

[redJag]

It's FREE.

Re:Why MySQL Grew So Fast

[ciroknight]

Didn't help PostgreSQL or Firebird did it?

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:Why MySQL Grew So Fast

[Anonymous Coward]

it is not free, unless you give your code away via the gpl.

Re:Why MySQL Grew So Fast

[Anonymous Coward]

If you mean passing the ACID test of a standard, it wouldn't be fast anymore. So in the end, following standards, such as ACID, is something you won't want.

Re:Why MySQL Grew So Fast

[Hektor_Troy]

>Yes, we know, we know, MySQL isn't 99.99999% standards compatible.

I think you could remove six of those nines and still be right. Maybe even seven of them.

It's kinda like saying "Yes, we know, we know. Internet Explorer isn't 99.99999% standards compatible." except that saying such a thing brings on the flames ...

Re:Why MySQL Grew So Fast

[fm6]

Firebird is free now. When the Web started exploding ten years ago, Firebird was still Interbase, and Interbase was still closed-source and reasonably expensive. (No nitpicks about the specific status of Interbase please -- it's complicated and uninteresting.)

PostgreSQL has always been OS, but it didn't appear until 1996. The original Postgres engine goes back much further, but didn't support SQL. Also Stonebraker and other Postgres people saw it as the basis of commercial products not free software.

You're probably right, anyway -- even if Interbase and PosgreSQL had been available for free in 1995, their greater complexity would have kept most web developers from using them. But they weren't so we're dealing in theory.

MS-SQL is cheap

[Anonymous Coward]

$49 for developer edition. You think that is expensive? Even the enterprise version is only $1200 or so (per CPU)... MUCH cheaper than Oracle

Re:MS-SQL is cheap

[Anonymous Coward]

That's still $1200 per CPU more then MySQL

Re:Because they were the first to support subqueri

[TheRealSlimShady]

MySQL has always been fast.

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.

I still prefer PostgreSQL

[Vellmont]

MySQL still doesn't support triggers, and I like advantages of having support for varchars larger than 255 characters. Postgresql also supports the more standard method of an auto-number unique ID field of the sequence (and argueably more flexible). I _really_ like the flexibility of authentication that postgresql offers, though I haven't looked at MySQLs authentication as exensively.

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"

[kcbrown]

MySQL is an interesting example of how you can be wildly successful if you're at the right place at the right time with a product that's just good enough for the mission people have in mind.

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"

[vastabo]

MySQL is an interesting example of how you can be wildly successful if you're at the right place at the right time with a product that's just good enough for the mission people have in mind.
Like Windows?
*ducks*

Re:MySQL got there first and was "good enough"

[Lechter]

you can be wildly successful if you're at the right place at the right time with a product that's just good enough for the mission people have in mind.

My! That sounds rather like another database I know of...which one was that? Ah yes Oracle, that's right!

"Right time, right place, right price" has always been the key. The more pollished features (that only capture a few percent of users at the margin) can be added as time goes on. Heck, Oracle didn't even get LOB deletion right until 9i...but for everything else 8i suits you quite nicely.

Re:MySQL got there first and was "good enough"

[krumms]

PostgreSQL is at least as fast, if not faster, than MySQL in many situations

Actually, I've seen benchmarks performed by a past employer that indicated MySQL was many times faster for large (speaking tables with millions of rows) databases.

What's your source?

Re:MySQL got there first and was "good enough"

[Anonymous Coward]

Despite all the "Catherdral and Bizarre" garbage, Microsoft and Open Source have pretty similar development methodologies:

+ If it compiles, ship it
+ Release Early, Release Often
+ Take three tries to get it right
+ Functionality is more important than architecture - "Good Enough!"
+ Extend bad popular products rather than pushing good new products (MySQL, PHP, Windows 3.1)
+ Good artists copy ....

Re:MySQL got there first and was "good enough"

[GooberToo]

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.

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"

[Mr Europe]

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.
And that is intentional. In an interview they said their aim is to fill the needs of the majority and do it well. Avoiding (/postponing) all the bell and whistles helps to keep things simple.

Re:MySQL got there first and was "good enough"

[GooberToo]

Under what conditions. Notice that the part you quote even made that stipulation.

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 and said, "cool! I just matched a Cray box's performance with my machine". He was talking about some off the wall benchmark. The follow up reply said, "now, run 10 instances on each system and let us know now the results." He came back saying that performance was less than 1/10 what the Cray was seeing. I mention this example because it sooo reminds me of MySQL versus just about any other real RDBMS.

There will always be situations where MySQL is fast. It's a simple database for simple tasks. Once things start to get complex or the load ramps up, MySQL suddenly starts turn pale.

Don't believe me? Feel free to finish reading here. It shouldn't be too hard to find pro-MySQL guys stating the same thing, though perhaps worded slightly differently.

Re:MySQL got there first and was "good enough"

[Anonymous Coward]

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.

You do realize that MySQL has built in replication?
Also does point-in-time recovery thru its binary update logs?
It also is pretty close to nested transactions as it does SAVEPOINTs and the ability to have a single statement fail and not abort the entire transaction like postgresql.

Re:MySQL got there first and was "good enough"

[Bromrrrrr]

and the ability to have a single statement fail and not abort the entire transaction like postgresql.

Wow, that is almost like....not using transactions at all!!!

This is too obvious.

[Anonymous Coward]

It grew so fast 'cause it had a huge memory leak, of course. Fixed in the most recent version of course.

Like we never knew what was causing /. to go down all these times. Upgrade already, Mister Taco!

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.

Slashdot poll

[Anonymous Coward]

Please respond and indicate which category you fall into:

1) poo stabber
2) fudge packer
2) candy ass
3) teabagger
4) protein milkshake drinker
5) guy who likes man poo on his schlong
6) guy who likes fecal matter packed into his urethra so he can experience severe and painful infections
6) guy who likes gay sex despite the fact that it is far and away the highest mode of STD communication
7) all of the above

Re:Slashdot poll

[Anonymous Coward]

Mine is 6) guy who likes fecal matter packed into his urethra so he can experience severe and painful infections.

Re:Because they were the first to support subqueri

[ciroknight]

You probably also answered your own question. The reason why 4.1 is barely getting out of alpha is because 4.0 was their stable tree, and they're now working on their development tree, 5.0. My guess is they just don't have enough people who are willing to still work on 4.1, for 4.1 to be worthwhile. Wait for 5.0 is my advice.

(7)

[Anonymous Coward]

ShIjt

[Anonymous Coward]

OS. Now BSDI is a BSD ove8 other

I've Tried PostgreSQL

[jwbrown77]

I understand it's supposed to be better because it supports more ACID compliant features (and has for a long enough time to consider them stable). I just can't seem to get the hang of PG though. I far prefer the mysql command utility (and mysqladmin) over psql, I just find it easier to use. I also don't like the way PG handles users. I find it easier to add and modify users in MySQL and set their privileges with it's table method than PG which is more configuration based. I could figure everything out, but I've been too swamped lately to learn the in's and out's of PG. That's why I continue to use MySQL, because it fits my needs. Not PG's fault, but it's one of several reasons people still use MySQL.

Re:I've Tried PostgreSQL

[Anonymous Coward]

Use PgAdmin. Its a GUI to administering postgre databases. It makes managing privileges a joy.

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.

Re:I've Tried PostgreSQL

[Fweeky]

Would be handy to have a little MySQL -> PgSQL cheat-sheet to help MySQL users map their current knowledge onto PgSQL. Getting users over that initial sense of being lost with a system that seems on the surface completely different to what they're used to is probably the most important thing in gaining new users, and that goes for pretty much any application.

Re:I've Tried PostgreSQL

[Betcour]

That is a *very* good idea.

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.

Re:I've Tried PostgreSQL

[Anonymous Coward]

I far prefer the mysql command utility over psql, I just find it easier to use.

Oh my fucking god. You're kidding right?

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....

Re:I've Tried PostgreSQL

[Tukla]

"I'm used to it." That's the biggest reason why most people continue using anything that's crap.

MySQL for Sybase admins...

[Vrallis]

Here's an 'embedded ask Slashdot' for everyone, as I haven't found such an animal online.

Is anyone aware of a quick guide/tutorial for MySQL that is aimed at Sybase administrators? It would definitely save me some time getting my act together with it...

Thanks...hopefully someone knows of such a beast!

Re:MySQL for Sybase admins...

[LostCluster]

If you haven't got the point of this whole debate yet... the headline is that MySQL only implements a subset of the features that any "real database" system does.

So really, most of the features you're used to in Sybase just plain aren't there. You might as well start with a "MySQL for Dummies..." or similar starter-title to understand what MySQL does have, and hopefully help you get PhpMyAdmin setup because that has to be the dominant UI tool for using it.

From there, use the MySQL.com document pages to make sure you're using proper syntax and know what functions you have available to you. You're mostly going to have to un-learn any non-standard SQL fuctions you're used to working with, and then lose the parts that MySQL hasn't done yet...

Re:MySQL for Sybase admins...

[Vrallis]

Thanks for the reply...

I'm actually not too concerned about SQL syntax. I'm interested more in the administrative aspect of the server software itself.

In other words, in creating a database you have to deal with creating the devices, managing segments, dealing with log space, tempdb, dumping/loading databases, etc.

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.

(Admission: I just plain haven't taken the time to deal with it yet....I'm just trying to find a 'quick fix' reference aimed at someone like me in order to skip a few steps!)

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:MySQL for Sybase admins...

[wagemonkey]

Is there a reason for using MySQL?
Otherwise as a Sybase DBA I'd expect you to be much more at home with other FOSS rdbms : Firebird,SAP,PostgreSQL etc.

Re:MySQL for Sybase admins...

[MattRog]

Yikes! I'm an ASE DBA and I would never recommend MySQL. Heck, even the *free* version of ASE (ASE11.0.0.3 for Linux) is better than MySQL!

If you want a free, open-source DBMS I really like PostgreSQL, although I prefer ASE.

Re:MySQL for Sybase admins...

[Vrallis]

Oh, I definitely keep 11.0.3.3 and the dev 11.9.2/12.5/12.5.1 around. I'm working on supporting a personal website thought hat will need MySQL (very few OSS CMS apps out there support anything else, and few decent ISPs do either).

(corrigendum)

[Paul d'Aoust]

meant to say 'can't figure out how to pronounce MySQL', not 'spell MySQL'. Dangit. I previewed it twice, saw nothing wrong, submitted it, saw the error.

The only comment I have to make...

[Anonymous Coward]

apart from the fact that PostgreSQL wipes the floor with MySQL:

All u Gnome loving Qt hating hippies need to judge the MySQL licence in the same light - MySQL is licenced EXACTLY the same as Qt in that respect - doesn't that mean u should hate it just as much?

(oh - and PG forever - u losers can use MySQL all u like, i couldn't care less)

kudos

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

[ErichTheWebGuy]

DAMNIT! Just realized that I replied to the parent instead of an earlier post. Mod down accordingly.

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.

Re:bullshit

[Mant]

Bugs per 1000 lines has nothing to do with the design. It's like one person saying "the plot of this novel is crap" and someone replying "that's not true, it has hardly any gramitical or spelling mistakes". I can't imagine why the parent was modded as insightful (except MySQL fanboys?)

Re:bullshit

[fpu112]

It was modded Insightful probably because it is. It also has facts in it that are "Insightful" which is probably what the rate it by... About the novel thing, but some novels have really good stories, but are wrtitten horribly, I would rather read the crappy one written well than the good story written badly

Can't please everyone

[Anonymous Coward]

I use it on my server. It has no problems that I can detect... Can you give any reasons that it sucks? Hmm? Thought NOT you moronic troll!

Simplest database

[mnmn]

It was the simplest database at the time that was marketed well. Minisql was not marketed at all, and of course wasnt really opensource.

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.

Re:Simplest database

[Decaff]

People need to use SQL and need something simple and fast. Postgresql is not optimised for simple web applications out of the box.

Yes it is. You connect to it with PHP, JDBC, ODBC or whatever and use it just as you would use MySQL. From the programmers point of view there is no difference (apart from the better SQL support). The same goes for SapDB, Oracle, MSSQL, etc.

Exactly how is MySQL 'optimised for simple web applications' in a way that any other database isn't? Is there some sort of built-in query optimizer that recognises SQL from web applications, or am I missing something?

Re:Simplest database

[Anonymous Coward]

MySQL permits uncommitted reads by default, which requires less bookkeeping. If your app rarely does updates, MySQL will run more quickly than a database that supports serializable transactions, and it'll probably take you a while to notice that it sometimes gives you wrong answers.

Re:Simplest database

[goon america]

SQLite is poised for a big boost. It's going to come bundled with the next version of PHP, PHP 5. That M in LAMP may be in trouble...

Incorporation

[ae-valkyre]

Another reason is that so many (web) programs incorporated it, like vBulletin. it was free to use and easy to learn, and it was painless to deply on virtually any platform.

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.

Note to the Mods...

[DAldredge]

What I posted wasn't a troll, it was the truth. Just because something pisses you off, doesn't mean it is a troll.

Re:Note to the Mods...

[B3ryllium]

Perhaps it was random MySQL data corruption at work. ;-)

(Disclaimer: I like MySQL.)

Re:Note to the Mods...

[Anonymous Coward]

In context, your comment was trollish and irrelevant. Just how was it relevant to the parent? (It wasn't.)

Maybe if you had explained how your point #3 has slowed adoption of MySQL, it wouldn't have come off so trollish.

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:Note to the Mods...

[Anonymous Coward]

It silently ignores the SQL spec in order to make it simpler for developers -- they can ignore datatypes, null values, etc.

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:Note to the Mods...

[Anonymous Coward]

Yea, who give a fuck about the damn thing being reliable, it's easy!!!

Re:Note to the Mods...

[Adam9]

Hmm.. Windows, anyone? ;)

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.

Re:Note to the Mods...

[jag164]

You're reasoning with the /. crowd?!?! Silly you.

Re:Note to the Mods...

[jadavis]

I have a very similar story. Small company, big spending, decent business plan, very poorly implemented.

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...

[Anonymous Coward]

Yeah, MySQL is the Windows 95 of databases.
Or the Visual Basic of databases.
Whichever will get me modded down.

Re:Note to the Mods...

[the_mad_poster]

(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:Note to the Mods...

[Anonymous Coward]

people don't realize which tool they should use for what job.

And this includes you, apparently.

i can do all the trigger, stored procedure stuff within the application code

Why you would personally chose more work for yourself, and get a bigger, slower, less portable application, is simply beyond me. As you said, you simply don't realize the right tool for the job.

MySQL also allows you to do rapid development of small to medium sized projects

Postgres allows this too. And it gives you better scalability. But oh yeah - you don't realize the right tool for the job.

Re:Note to the Mods...

[Anonymous Coward]

A friend of mine who works at a Biotech department told me a story which was pretty much the opposite:
They were going to set up a database for some genome-type stuff, basically a LOT of data anyhows.

They had purchased an Oracle license for this, when one of their geeks complains that he doesn't know Oracle and manages to convince them to use MySQL instead.
(When they'd already paid for the Oracle!)

That's when my jaw hit the floor.
Money-wasting and incompetent. I'm just happy the project was on a private grant and not funded by my tax money. :-)

Re:Note to the Mods...

[JamieF]

>and i can do all the trigger, stored procedure stuff within the application code, big deal

Does your employer know that they are paying you to reimplement (and hopefully thoroughly test and debug) functionality that is already present in free software that you could have used if you felt like it?

"Well I could have used gzip but I thought that was too bloated, so I wrote my own compressor as part of the application you paid me to write."

>MySQL also allows you to do rapid development of small to medium sized projects.

How exactly does MySQL do a better job of letting you develop a SQL RDBMS system quickly than, say, PostgreSQL or SAP DB? It may be easier to install, and it may let you write code that fails to return an error message, but the only valid metric of software development productivity is how long it takes to write correct code. Otherwise you might as well use `/dev/random` as a source code generator. (A radical new rapid development methodology!)

Your story about the employee DB is pretty interesting. You could have used something other than MySQL, such as SAP DB or PostgreSQL or even Excel, and not had to buy Oracle. That proves nothing about MySQL's merits. I'll set up an employee contact list for you in 5 minutes in Excel (or OpenOffice if you prefer) that is perfectly suitable for the needs of a 25 person company. If the CTO insists on buying an Oracle license for something that could be done in Excel, that doesn't prove anything other than that your CTO wants to buy an Oracle license. Perhaps he wants to do this so that he won't have to keep paying you to reimplement functionality in every app that's already been in other RDBMSs for five years or more.

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

[Anonymous Coward]

you are the reason for the outsourcing, by getting companies to WASTE money on oracle if its not necessary.

regardless of what you say, MySQL is a real database, its not in the same class as oracle, or IBM's but its still real (i have it installed so it is)

so go keep recommending companies to waste money on systems that they dont need, i hope it catches up with you, because you are an idiot. not everything is uber mission critical 100% buzzword complient (but thats what keeps you employed)

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

[ErikZ]

"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."

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.

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:Yeah... I'm gonna sqitch from Oracle to MySQL

[Anonymous Coward]

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.

If you are based in the UK look at www.34sp.com - about 15 for a years subscription, mysql, ftp, etc - a lot cheaper than most hosting companies...

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

[Decaff]

You are missing the point. The point is that other databases, such as Postgresql, offer virtually all the SQL features of Oracle including a reasonably compatible PL/SQL language. Oracle is not a simple database. Developers tend to use its features. MySQL can't provide those features. Other databases can.

Suggesting a migration from Oracle specifically to MySQL is typical management bullshit along the lines of 'I have heard of both of those and they are both databases, so they must be the same'.

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

[Paulrothrock]

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.

$20 a month?!? Who are you overpaying for hosting?

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

[dave420]

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

If you're trying to race go-carts, that sounds like a great idea. Without knowing exactly what he's trying to do, on what hardware and for which people, it's impossible (and pointless) to comment on it.

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

[sootman]

>>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.

Or maybe his company is like mine. We've moved several apps from MS-SQL to MySQL (little stuff--calendars and whatnot) and we now have one less instance of MS-SQL running. My company (like many others) pays per installation, per year. When the auditors come 'round this summer, and every summer thereafter, we will save a few hundred bucks every year by not having that needless instance.

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

[justins]

In which case the guy needs to be fired anyway, because there have always been much cheaper options than Oracle, if you really don't need what it offers.

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

[Tukla]

The target of the link in your sig doesn't exist.

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

[evilviper]

Thanks for the tip. Time to find a new target.

Mod the parent up!

[Osty]

That's excellent! The arrogance of the MySQL developers is astounding. Do you happen to have a link with that documentation for posterity?

(Moderators, feel free to mod me down, but please mod up the anonymous parent. Thanks.)

Re:Mod the parent up!

[Anonymous Coward]

http://sunsite.univie.ac.at/textbooks/mysql/manual .html#Broken_Foreign_KEY

The other one I remember was MySQL's "cross database benchmark" which consisted of stuff like running CREATE TABLE and DROP TABLE 100 times from a single client. That was on their homepage for years.

Win32 Binaries

[Anonymous Coward]

One of the reasons why MySQL wins on PGSql is it Win32 support. We may not all like windows, but a lot of people use it, MySQL has addressed this market, PGSql still dosen't

Re:Win32 Binaries

[Anonymous Coward]

Jesus christ ... just run it on cygwin then. If you're using Windows you obviously don't give a rat's ass about performance so what's another layer more to you.

Re:Win32 Binaries

[nbdy]

This may explain the fibble that why people don't choose PostgreSQL... Any other sound reason?

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.

Re:Win32 Binaries

[Anonymous Coward]

We're using postgres on cygwin in production and it runs pretty sluggish. Still, we've had no data corruption incidents, but perhaps the biggest issue is that the postmaster service often crashes for no reason. Also, the dependency on the cygwin ipc daemon is an extra point of failure that no-one needs.

Re:Win32 Binaries

[Anonymous Coward]

to be honest, to get the windows market, MySQL has to fight against both Access and the "Desktop Engine" (you know the freebie SQL Server cutdown, that comes free with office and is built from SQL server). A lot of people are using these engines, and the MSDE itself supports views, triggers and transactions. MYSQL has a little way to go to be anywhere near as good as the desktop engine.

It's all in the name or is it the license?

[Anonymous Coward]

I think it does have a lot to do with the name.
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?

[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?

[linuxhansl]

Excuse me?
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.

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:Views? Subqueries? Easy to move databases?

[GooberToo]

The fact that you're having to reply to such a statement highlights why he's using MySQL.

Nuff said.

Re:Views? Subqueries? Easy to move databases?

[LostCluster]

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.

Which is basically executing the SELECT represented by the view, and storing it in a temp table so that another query can use it.

If specified correctly a view can be updatable.
Which really is the same as executing the select for that one specific value so that it can be translated to the appropriate group of records in the other table.

Those are logical equals at the SQL level... of course, the higher-end database engines are more optimized for those situations, but most MySQL users rarely need that complicated of joins...

Re:Views? Subqueries? Easy to move databases?

[gtoomey]

I've been using DBMSs for over 20 years, and your comments are spot on

Sure I get things done with mysql, but without views (or synonyms) you start using workarounds. With no subqueries or materialsized views, you do even more workarounds. (Yes I know they are in beta).

mysql is OK for amateur hacking, and I suspect most people using it have no idea about normalisation or things professionals take for granted.

Re:Views? Subqueries? Easy to move databases?

[Anonymous Coward]

The concept of views] is basically executing the SELECT represented by the view, and storing it in a temp table so that another query can use it.

No, that must surely be highly misleading. Views is an abstraction mechanism, like an API. You have tables that may happen to undergo changes, like normalisation or inclution/deletion of columns and so on, but you can use views to present a consistent virtural table for use of an application. Thus, the application developers do not need to understand the complete datamodel, rather they can have their own data model which may be based (partly) on views. This simplifies their job.

With the risc of being accused of generalizing too much: Views is pretty much like the OSI model of communication.

Re:Views? Subqueries? Easy to move databases?

[Anonymous Coward]

OMG. I don't believe I am reading this. How can somebody without a *slightest* clue have the audacity to even post on the subject, let alone argue with people who know what they talk about.

Re:Views? Subqueries? Easy to move databases?

[Anonymous Coward]

A view is never out of date (unlike any summary table you might create yourself) and can read or write data in tables I otherwise may not have permission to access (because whoever designed the view decided these uses would be safe).

Re:Because they were the first to support subqueri

[GooberToo]

So true. MySQL is fast as long as it's not ACID compliant. Want transactions and ACID? MySQL just got slower. Want concurrent DB access for multiple users or multiple sessions? MySQL just got slower. Want to use MySQL for mixed select/insert/update activity? MySQL just got a lot slower. Want to use MySQL for complex queries or many table joins? MySQL just got a lot slower. MySQL has a built in speed advantage because it's so feature poor. Less features means it has less code. As they add more features, I fully expect performance to continue to decline in all areas.

Basically, the only speed advantage that MySQL has over other DB's is when you have an select only environment with little to no concurrent activity. Once you exit that criteria boundry, other DB's suddenly perform as fast or faster. The further over the boundry you get, the better other RDBMS options look. Best of all, the other DB options tend to be feature rich to boot.

The fact that it's simple, easy to install, and cater to people that don't know a DB from a spreadsheet, is all you need to know to understand why MySQL is so popular.

Pronunciation

[Anonymous Coward]

Simple. We need to ask our expert in Open Source pronunciation... Mr. Torvalds?

Re:Win32 Binaries (Postgres via Cygwin)

[Anonymous Coward]

Actually, one of the things I like about Postgresql is that it can run on Windows (under Cygwin). It's almost as simple as: Run SETUP.EXE. Certainly I would not recommend this for a production environment, but it does work for development. Would you even think about running MS-SQL on Windows 95 / 98 (notice I did not ask if you would think about running Windows 95 / 98 themselves). :-) Yet I was able to run Postgres using Windows 95 under Cygwin to develop a solution on a low end laptop.
Postgres does need to work on this area though. Many people don't realize how easy it is to install Postgres under Windows and the fact that it doesn't allow TCP connections out of the box doesn't help matters either (particularly since this is all you will be able to use under Windows).

Add to that the ODBC drivers that allow MS Access programmers and even MS Excel users to query the database and Postgres really does work with the Windows world.

What they haven't offered (until recently) is a GUI to manage / view the system. But check out PgAdmin at
http://www.pgadmin.org/pgadmin3/download.php
(also available as a Windows binary).

MS Access from hell

[Safety Cap]

And then they want to run enterprise level warehouse tracking, inventory, payroll and who knows what else from the little database they whipped up between Solitare games.

My last assignment at a Big Oil Company was to produce a list of files on the shared drive, grouped by person, age, and total size. As all work at the BOC was billed, and my project was budgeted only for my time, I was not allowed to have a DBA create either an Oracle or MS SQL DB (something that would've only taken 5 minutes). I had to use MS Access; suggesting the use of FOSS would be like saying you wanted to summon Satan to get your work done.

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:MS Access from hell

[LostCluster]

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.

Whatever you were running on... it wasn't enough of a computer. I've never seen Access truely hang. I've given it requests that take it near-forever to get done, but it gets there in time.

Your biggest problem was most likely that your table size got so huge that you maxed-out your system's RAM and were resorting to virtual memory for nearly every move you made. That'll cause things to slow down in a hurry. However, that's no excuse to "dump" the DB. You imported once and you did it perfectly... keep that data at all costs.

What you should have done was to make a copy of the big table's structure, and then copied over enough records to make a suitable test subset, one that exposes any sort of special cases you're expecting. Run your report against the subset when you're testing, and then just switch over the table name from the subset table to the real table when it comes time to run the real report. Why are you bothering to make Access produce 500+ pages all the time when you just need to see your changes laid out once?

At worst, you could have just saved out the populated tables as a flat-file so that you could have reimported those instead of having to run you takes-a-day import routine...

Re:MS Access from hell

[AhBeeDoi]

You were forced to use the wrong tool for the job. Whose fault is that?

Re:MS Access from hell

[Anonymous Coward]

Okay I'll bite...
I've never seen Access truely hang.
Then you've never tried to use it to generate crosstab reports on 1-2 million row fact tables. Granted the crosstab query is much nicer than Oracle's summing(decode(field,....)) but I can lock access in a heartbeat.

Your biggest problem was most likely that your table size got so huge that you maxed-out your system's RAM and were resorting to virtual memory for nearly every move you made.

Access doesn't load or cache tables in memory. I have 3 Gigs of ram and using Access 97 (filesize limit 1G) or Access 2002 (filesize limit 2G), and I didn't notice any speed increase between a machine running 256M and 3G ram.

What you should have done was to make a copy of the big table's structure, and then copied over enough records to make a suitable test subset, one that exposes any sort of special cases you're expecting. ...Why are you bothering to make Access produce 500+ pages all the time when you just need to see your changes laid out once?
If we're talking format changes and not functionality changes, I would agree with you. Any DBA should run an entire dataset and validate output.

Now here is the fun part
I've got 3 phrases for you. "Packdata" (embedded binary numerics), fucked up ODBC queries, and "broken csvs." In any mixed environment (DB2 in my case), getting files from the mainframe usually means that any numerics changed to binary - The linefeeds alone will drive you shitty. You have to specifically code Access to drop into binary using VBA.

Try to do any pass through query against an Oracle instance where the numbers are healthy sized. Access is retarded and will not adjust the field properties accordingly and bomb. You wind up having to wrap calculations around to_char(). Plus Access 97 wouldn't let you disconnect from ODBC without exiting the DB and letting the timer release. Try doing 6 1-hour queries when you have a 2 hour timeout and you'll see what I mean.

Broken .csvs are exactly that. Try importing Bill's, Inc and see what I mean.

Okay How about a search and replace function? Don't tell me to open the table and use the Edit -> replace thing. Make sure you enter # and watch all your numbers disappear.
Access is a steaming pile of dogshit.

Re:MS Access from hell

[stienman]

I can only hope that you had the presence of mind to store the data in a seperate access database, and only linked it to the report and query database file.

Of course, from the sound of it you would have been better off refactoring the data into several other tables, or at least creating a table to hold the x axis of the cross tab so your query didn't have to search the entire large table more than once per query.

-Adam

There Are 25 Stories On /. Right Now!

[Anonymous Coward]

25!

Why?

There have NEVER been that many at one time before.

And there's no GOOD reason for it, as the ol;d stories are, well...old...and they are now of little interest.

So why are they still there?

Is it because somebody wants to maximise the exposure of the last story, the one about Raven Alder, the chick who wears black and can "work Linux real good"...?

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

[Pvt_Waldo]

To quit using a database that a LOT of people are using with no problem because once you had a problem? Sheesh. Also better give up on driving your car (i've seen cars fail), walking (i've seen people trip) and eating (i've seen people choke). Oh and don't turn on your computer - i saw one crash once!

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

[Anonymous Coward]

You seem to have inserted illegal words in your brain. Grow up and type the whole word and maybe someone will respond to your content and not your presentation.

Re:I stopped trusting MySQL

[yem]

Straw. Camel. Back.

Did you read past the first line?

Re:I stopped trusting MySQL

[Anonymous Coward]

yeah but at least my post had real CONTENT relating to the article instead of just bagging out an individual

my work here is done

loser

Re:I stopped trusting MySQL

[duffbeer703]

It's easier to catch date errors in your application! Type-checking doesn't belong in a database anyway.

Re:I stopped trusting MySQL

[mla_anderson]

You're being facetious I hope.

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 ...

Re:I stopped trusting MySQL

[plupster]

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

Oh, the old date format problem. Is that the 46:th day in the 39:th month, or the other way around?

Re:I stopped trusting MySQL

[yem]

It's easier to catch date errors in your application! Type-checking doesn't belong in a database anyway.

Question: '2309-46-39' > '2310-01-01'?

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]

You're right, not everybody has to worry about those issues, but maybe they should.

Well said! I seem to have spent most of my working life fixing projects that were initially designed as small systems that 'will never need to scale'.

Good software engineering practice is to allow scalability, security and real transactional integrity from the start.

Re:I dislike MySQL

[GooberToo]

Amen!

Re:I dislike MySQL

[arthurs_sidekick]

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.

In all seriousness, how widespread is this perception? I mean, do you have an example in hand of someone saying they'd use MySQL for an FDIC insured bank's transaction records? For all the reaming those who defend MySQL get (and I'm not saying you're in on that), only a tiny minority who seem to be actually serious about it seem to be saying it's "every bit as good" as [ commercial products ]. Scratch most of the "zealots", and you will find someone saying (e.g.) "well, yes, it doesn't do subselects but I don't use those often and you can always do another select." They won't be as nice about it, of course, but that's the content of what they're saying.

The most common defense of MySQL I see given is like that given in the post you're reacting to:
The features of [ commercial product ] over and above those provided by MySQL are not worth the extra cost / hassle.

Re:I dislike MySQL

[LetterJ]

Unfortunately, the corollary to "will never need to scale" is "this is going to be so big that it needs enterprise level design, but ends up with 10 users" and that frequently leads to systems that never get out of the gate or have ongoing costs that dwarf any revenue the project may bring in. Given how many projects are never completed at all, a *small* amount of "just get it working" can ensure a project's completion.

I've seen a lot of web sites running on $10,000 load balanced clusters of computers and $1,000/month in available bandwidth and they never get more than 500 visitors per day.

There's a balance somewhere, but I'm not the guy to draw the line. I just know it when I see it.

Re:I dislike MySQL

[haystor]

Ok, scale this with Oracle:

Parts catalog kiosks. Gonna need thousands of them to be searchable. Some inserts and updates will be done to save state.

Data loss can be corrected by uploading the entire catalog again. Constant connections to the master database is not acceptable requiring a db locally.

I've run into situations where the cost of repairing data loss by the db was considerably less than the cost of maintaining another oracle license.

Too many defenders of Oracle (which I do in fact like and use) want to buy licenses first and think about the problem second.

Solutions (appropriate ones) I've used where other people would buy a RDBMS solution:
A proprietary db (Oracle, DB2, MSSQL, Informix, Sybase)
A free db (Mysql, msql, Postgres)
A flat file (gasp!)
A 2G+ data set kept in memory to make unusual queries faster

Solutions that have been inflicted upon me as some sort of karmic retribution:
Access
Excel (yes, to store data)
Word (yes, to store data)
PowerPoint (yes, to store data)

There seems to be a theme with that last set. I understand FoxPro has quite the fanatical following though and I've been curious to see why.

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:I dislike MySQL

[Paulrothrock]

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).

Yeah! That's pretty funny! Ha ha ha ha... huh?

Re:I dislike MySQL

[Bromrrrrr]

I wince every time I need to use nested loops to delete child records of child records before deleting the child records that I need to delete before I can delete the record I want to delete, something that could have been handled by a simple foreign key constraint. _That_ is what I would call a hassle!

Yes, yes I know you can use fk constraints with InnoDB now, but how many MySQL developers will? If you ask me, most will call it "unnecessary complex", "too much hassle" or some such drivel. The issue is not so much with MySQL per se but with a lot of the people who use it. Programmers working with databases _should_ know better but unfortunatly programmers accustomed to MySQL often don't.

starting off on the wrong foot n00b!

[Anonymous Coward]

You are not a very not cool new guy. Go back to AnandTech you silly bint. THATS RIGHT GO BACK TO ATOT.

PT Barnum was right?

[Anonymous Coward]

mysql is about the shittiest database with the shadiest licensing scheme. Due to morons like Jeremy Zawodny prostuting it - that's why it's so popular. Taking advantage of suckers.

Comment removed

[account_deleted]

Comment removed based on user account deletion

goatse.cx clears up the confusion

[A nonymous Coward]

Go look at that picture and realize he is saying "My SQUEAL!"

You would too.

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?

And Access is more popular still

[zenzen667]

'Popularity' is a totally unimportant benchmark. Unfortunately, I tend to find most MySQL users either:

1. don't know enough about databases to understand why they should be using a different tool
2. members of the 'I have a hammer, therefore this problem is a nail' sect
3. people making money from the first two groups

Finding someone using it and can give good technical reasons *why* they are using it is a rare find. Feel free to substitute Access, Visual Basic, Perl, C, Java, C# or C++, Windows or Bose for MySQL if you like.

Re:And Access is more popular still

[ErikZ]

That's a lousy qualifier.

I can't give you a good technical reason why I'm not running Oracle for my little 3 table website database.

Only financial.

Re:And Access is more popular still

[wagemonkey]

He didn't ask you why you're not running Oracle, he asked for a technical reason for running MySQL.
You could still be running PostgreSQL, SAP, Firebird for the same cost as MySQL, even MS-SQL and Oracle are available if you qualify for the free versions.

What was your point?

Re:And Access is more popular still

[ErikZ]

My point is MySQL has a subset of tools that are all available under Oracle.

So, technically, there's no reason not to get Oracle.

I looked for the free version of Oracle. Where is it? I could only find the 5000$ version.

Re:And Access is more popular still

[belroth]

Try reading this post this post.
The point to consider is that the MySQL code is incompatible enough with ANSI SQL to not be considered a subset of Oracle (or other full-feature rdbms). NB Oracle is not the only option...

Re:And Access is more popular still

[ErikZ]

Unless you're saying that MySQL code is so messed up that Oracle can't import databases, it doesn't matter.

Re:And Access is more popular still

[wagemonkey]

YMMV but normally the data is only a part (albeit a very large important part) of your app. If you've got a MySQL app then you're going to have to rewrite the parts of the app that access that data - data on its own isn't often much use. If you're migrating between most real rdbms then the rewrite shouldn't be too onerous, from MySQL it's a major undertaking.
You referred to 'a subset of tools', I wouldn't take that to refer solely as being able to transfer the data. Using the tool analogy, try MySQL uses cross-head screws where 'real rdbms' use torx. If you're only concerned with holding data you don't need a database at all, stick it in a text file. It's only when you want to use the data you may have problems.

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.

Re:Are you kidding?

[BgJonson79]

It's a numbers game.

So many people use Windows that the gaping technical issues often affect others.

The MySQL install base is smaller, so will impact less people.

Re:Are you kidding?

[bwy]

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.

Interesting? Or predictable. Linux users will justify every single piece of software they use or have installed, regardless of how ridiculous it might be. They'd say, who needs AutoCAD when you've got the Gimp installed, etc.

For IT geeks, the ability to make independent decisions on technology solutions based on the situation at hand and without regard for personal interests or passions is often seriously lacking which probably contributes to the way management handles situations the way they do, and the reason when IT geeks then complain. I've had to recommend different technologies at different times that I don't particularly care for, but I know it is the right fit for the situation.

Re:Are you kidding?

[stridebird]

er, hello. Insightful? The insight seems to be missing somewhere between:

You really need to learn RDBMS theory.

and

I do find it interesting that Linux users like to lord over Windows users

Can we have the insight please?

Damn i replied to AC i gotta stop that...

Re:Are you kidding?

[leperkuhn]

Yes the view is a query. But if you are setting up a multiuser environment you might not want people to have direct access to a table, and you let them use a view. Or maybe you need to refer to that view in other queries. I have a view set up that pulls data from around a dozen tables, and I do queries on the view. Why? Because I don't feel the need to write huge 12 table queries every time I need another piece of information.

Re:Are you kidding?

[Anonymous Coward]

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

I've worked on about a dozen large (200,000+ lines of code) systems running Oracle, and LostCluster is exactly right that typically a view is just a query that you run again. In fact, I don't think I've ever seen a database guy use a view for any other purpose. It can (and should!) be more, but how many database guys actually do that?

For a larger CRM system at a Fortune 500 company I'm doing QA on now, temp tables are their solution to the problem. You then use the data as often as you like for as long as you need without having to keep the main tables locked (shudder, which was their old solution with Oracle which locked-out everyone else from working until they finished). It's much better than a view, because the data is consistent indefinitely. The data doesn't change out from under them like it does with a view. At this company, the managers typically create a report then the sub-reports are run from that same data on and off for weeks. The data used has to stick around. I'm finding more and more systems are not well served by using views. For short-term, simple stuff a query bookmark is good enough. For more complicated things that have to remain consistent, temp tables are the solution. Views are a solution with a rather limited application. Both the low-end and high-end are better served with other methods.

Another issue is that when you do all of the db work in an application, you don't need views to use for security. You control the security in the application, and the short-hand queries you can create with a view isn't needed either because you only write the query once in the program.

Re:Are you kidding?

[Anonymous Coward]

> So many people use Windows that the gaping technical issues often affect others
> The MySQL install base is smaller, so will impact less people.

Unfortunately, this is not true. MySQL has become the "standard" for many markets (web hosting, for example. Also OSS blog stuff) and therefore its inadequacies are forced onto innocent bystanders like myself. There's also a fair number of people that mandate MySQL because its Free&Fast without doing any form of product evaulation. It really is M$ish.

I agree with the grandparent, BTW. "LAMP" people tend to be elitists even though the "M" and the "P" are really craptastic tools. At least MS VB people are generally humble and understand their place on the latter.

Re:Are you kidding?

[Anonymous Coward]

Locking is a quality of implementation issue. When you SET TRANSACTION READ ONLY ISOLATION LEVEL SERIALIZABLE you should get a consistent snapshot as of the moment you start the transaction, regardless of whether views are being used. Copying into temp tables is just a workaround for databases that get this wrong. If the managers want to use a subset of the data, that subset should be indicated by updating the real tables, not hoping the temp tables still contain the records you wanted.

Client-side security is worthless. Users may have a version of the application with exploitable bugs, they may modify it to subvert your constraints, or they may bypass it completely and simply use the general-purpose client for your database engine. What you're expecting is equivalent to DRM, which is arguably impossible on general-purpose hardware.

Re:Are you kidding?

[BgJonson79]

So, what are the uncraptastic tools to replace the M and P in the Unix world?

Why MySQL grew so fast:

[DrMrLordX]

1. Bovine growth hormones in its milk
2. Possible steroid abuse

"Why MySQL Grew So Fast" - A possible reason.

[ninejaguar]

All the reasons stated here are valid. However, it wasn't always true when it wasn't Open Sourced (however, it was still free for download). Could the reason be the same reason Linux did? Of the most popular Open Source databases, which ones are GPL'd and which ones are under a BSD or MPL style license? I could ask the same question about Operating Systems and their respective licenses but I'm not going to offer that flamebait. But, I'll point this out as an argument and a reason for its popularity. Not the definitive answer, but perhaps one of many valid ones offered by other posts.

= 9J =

Question SHOULD be: Why did Postgres grow so slow?

[gtoomey]

Version 4.1 of mysql is the first "usable" version. This version has subqueries, union, and derived tables. Buy still no views or synonyms.

But Postgres has been a fully functional RDMBS for years, with the features a professional needs.

Both can be obtained for free (though mysql is dual licenced). Maybe mysql has better marketing or offers better support, while becoming the default database for PHP?

MySQL doesn't scale

[Siener]

I like the idea of MySQL. It's great. But it's not ready for the big time. Not nearly.

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

[GooberToo]

Trust me when I say that MySQL guys won't want to hear this. They've been assured by MySQL, that you don't want to do this. In fact, they've been told that it's down right a bad idea to do this.

So, who do you think they are going to believe?

Re:MySQL doesn't scale

[Anonymous Coward]

Unlike most of what MySQL folks say, this is really an honest difference of opinion. The "best practice" from MS or Sun would be to put the biz logic in an application server, and only use the DB for integrity. However, many people still prefer to put the rules in stored procs. And for your lil VB/web app, the rules are going to inevitably end up in the client just for cost/installation considerations.

Of course, without nested transactions, you aren't going to get very far with MySQL & an appserver, and stored procs are obviously missing, so you know what the MySQL guys will say :)

Re:MySQL doesn't scale

[sootman]

There's a website that withstands a 24/7/365 slashdotting: http://slashdot.org. They run on MySQL. Is it airline- or bank-big? No. Is it bigger than damn near everything else? Yes. "Big-time" enough for me.

Re:MySQL doesn't scale

[plastik55]

Do they lose money if a comment disappears? No.
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!

Re:MySQL doesn't scale

[GooberToo]

Slashdot is known to have serious downtime and periodic performance issues.

If that's "Big-time" enough for you, well...it's safe to say that such a thought scares me. ;)

Re:MySQL doesn't scale

[sootman]

Is the Earth big? Yes. Is the galaxy big? Yes. Is the Earth small compared to it? Yes. Does that make the Earth not big? No.

Re:MySQL doesn't scale

[GooberToo]

Umm...compared to the galaxy. Yes.

Thusly, you can compare MySQL and PostgreSQL and wind up with the proper answer using the comparison and answer I provided above.

Go figure.

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"

MySQL blogs

[Anonymous Coward]

More weblogs about the 2004 MySQL Users Conference can be found at the The 2004 MySQL User's Conference & Expo Blog. Collection

Folks, you seriously need a life.

Re:Because they were the first to support subqueri

[Betcour]

Actually it doesn't make much sense to have two "development" versions. They might just as well scrap 4.1 and put all their effort into getting 5.0 finished ASAP.

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:Right On

[next1]

wow..
+6 insightful?
what more can be said.

i use PostgreSQL these days because i started a new job a while back and that's what they use.
but everything i know is thanks to MySQL - that was the first db i used when i needed a db for my website and didn't know jack.

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

[Anonymous Coward]

MySQL's missing safety features (like constraints) wouldn't be nearly so galling if the engine didn't successfully parse and silently ignore attempts to rely on them. It also wouldn't have been very difficult for them to lock all tables by default during a transaction, and let the user avoid this by autocommit and

SET TRANSACTION READ ONLY ISOLATION LEVEL READ UNCOMMITTED

when they're willing to accept answers that are very fast but incorrect.

Re:simplicity translates to speed

[Anonymous Coward]

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.

Bzzzt, wrong. A sequence implemented as a table would fuck up if you had several threads using it in different transactions. The sequence goes as this:

1. There is number 5 in some row of Table 'ID', as the next ID to bet inserted into table 'X'.
2. Thread A in transaction A fethes the number 5 and increments it. Then it inserts the row with unique ID = 5 into a table 'X'. It doesn't commit transaction yet as it has something else to do.
3. Thread B in transaction B fethes the number 5 (because of transaction isolation, it doesn't know thread A already incremented the number) and increments it. Then it inserts the row with unique ID = 5 into a table X. You get a duplicate ID or an error.

That's why there are sequences for generating IDs in decent databases, like PostgreSQL.

--Coder

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:simplicity translates to speed

[Caiwyn]

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

EXACTLY. Others are saying "Yeah, but that cheetah can't FISH. That makes the bear so much BETTER." Me, I don't particularly like fish, so the bear offers me nothing, and the cheetah is faster.

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.

Re:Why MySQL is more popular than Postgres

[base_chakra]

That's definitely a big part of it, but not the whole story. PostgreSQL's lack of adequate software support extends to the client side as well. There are very few options for PGSQL front-ends now (although some of them are quite good), but four years ago the selection was downright threadbare. One thing that can be said of MySQL is that MySQL AB at least offers free GUI clients.

But, I think many people are swayed by the argument that MySQL has a much bigger grassroots support base. While technically that's true, look at the nature of the support base: thousands of redundant questions, many of which are directly related to the lack of features that supposedly makes MySQL great. We're not just talking about views and subselects here: until v4.x, MySQL even lacked UNIONs for god's sake. Fledgling developes may not be able to ask for these features by name, but that doesn't mean they don't feel the lack.

Since working with MySQL means developing with half of the SQL9x spec tied behind your back, and considering that MySQL's documentation is inadequate (and occasionally inaccurate or misleading), it's no wonder that such a huge support base is necessary.

Re:Why MySQL is more popular than Postgres

[mla_anderson]

Here it is: Postgres on Windows in a single executable.

http://prdownloads.sourceforge.net/pgsql/PgSQL731w ina1.exe?download

Re:Why MySQL is more popular than Postgres

[relativePositioning]

You actually had me really excited until I went to install it. Wow! Postgres on windows! Then I read this:

This will install PostgreSQL 7.3.1 Proof of Concept for Windows Alpha 1

Not only alpha, but a proof of concept no less. I don't think this is quite what I was envisioning ;)

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:Often textfiles are perfectly sufficient.

[Anonymous Coward]

Poor little boy, all alone with your zopeDB ? How sad !
Hey you all, remember that zope/python is sooooo powerful that the world will soon be changed, and all IT professionals will have no work to do anymore !
BTW, you're all suckers by using something else !!! Ooops, sorry, I though I was a geek sucker.

Simple: It's not an RDBMS

[GrouchoMarx]

The reason MySQL has been so successful is that it is NOT a relational DBMS. It doesn't have any of the features that a "real" database would have. That's the point.

MySQL is a glorified card catalog with a reduced-SQL interface. And for the vast majority of the projects that use it, that really is all it needs.

I say this as someone who has not written in any production SQL environments except for MS Access and MySQL. For small stuff, all I want is a card catalog with joins, and MySQL gives me that easily. I know the projects I've worked on aren't going to scale to a million rows, they're only in the few thousands, or in a few cases only a few hundred. If they start to get to the point that they're handling millions of transactions, then I'll be rewriting the whole thing anyway.

Tell me: WHY would I want to use Oracle or even PostgreSQL for a recipe book or web calendar when MySQL requires less mental overhead for me? I wouldn't. That's like using a Mac truck to drive to the grocery store and back.

When I start writing financial apps or systems that actually require complete integrity checking, good bye MySQL, hello Postgres. But for right now, MySQL is simple, my web host supports MySQL, and it's all these projects are going to need.

Re:Simple: It's not an RDBMS

[wagemonkey]

In your case fine. You seem to know the difference between MySQL and a rdbms but a lot of other people don't seem to.
One point I would make is that if you suddenly start trying to develop something that actually requires a rdbms than you'll have a steep learning curve. If you think you'll ever need or want to write something complex then use a rdbms to develop your recipe book or web calendar.

You probably have a better understanding of your data model(s) now than when you started, so redeveloping (not converting) your MySQL apps to something else will let you learn a lot about your new db without too much worrying about the data/entity model which would otherwise prove a huge distraction. Then you'll have a head start on your financial app.
Think of it this way - how many people write "Hello World" apps in a new language or jump straight into writing an office suite, alternatively why didn't the Wright brothers build a DC3/747/Shuttle?

Re:Simple: It's not an RDBMS

[Paulrothrock]

That's like using a Mac truck to drive to the grocery store and back.

New for 2004, the Mac Truck! Marvel at the Aqua interface. Thrill at the 64 bit engine!

Re:Simple: It's not an RDBMS

[Just Some Guy]

Tell me: WHY would I want to use Oracle or even PostgreSQL for a recipe book or web calendar when MySQL requires less mental overhead for me? I wouldn't. That's like using a Mac truck to drive to the grocery store and back.

Answer: don't use every feature that they provide just because they're there. Instead, install and use PostgreSQL and get comfortable with it. Now, you can use it as a flat-text database, or a tera-server with millions of rows, all without having to maintain two systems.

If your time is valuable, why learn two systems when you can learn one that's only slightly more complex than the other and be done with it?

Re:Simple: It's not an RDBMS

[base_chakra]

...it is NOT a relational DBMS.

Yes, it's that most paradoxical of phenomena: a system for managing relational databases which--at the same time--is not a relational database management system.

industry professional?

[vallis]

Your MCSE, MCDST and MS Office certifications are worth as much as the toilet paper roll stack in my closet.

You want to talk professional, how about something that took serious time and energy to accomplish, like a Masters of Science or a PhD? When you accomplish something that difficult, then I give you full bragging rights for your accomplishments.

Claiming to be an industry professional because you have Microsoft certifications is almost laughable, but I won't laugh because I want you to retain some semblance of dignity.

Re:industry professional?

[Anonymous Coward]

i think youll find that there are some of us here do have Masters degrees as well as MCP/MCSD's/MSDBA's. They are not mutually exclusive you know.....

tit

Well, it's MINE, innit?

[kale77in]

17. Well it's MINE, innit? Says so in the name. _MY_ sql. Ya gotta like that!

Seriously, I think a good name is half the battle with acceptance. If Postgres had called themselves MySQL -- rather than "Huh?" as most people understood them to mean by their Ingres-derived title -- and then produced some half-decent documentation occassionally...

But they didn't.

Not to TrolI, but its11:09 UTC (or GMT)...

[Anonymous Coward]

...and do you know where your www.mysql.com website is, MySQL AB? It is positively unresponsive, and I doubt a slashdotting. Or at least a company that sells something billed as a web backend ought to be able to handle a slashdotting. Yes, the rest of the web is working fine.

Try Firebird RDBMS

[Anonymous Coward]

An industrial strength FREE database. : )

An immature DBM attracted an immature userbase

[ectoraige]

Not trying to flame... talking about maturity of experience...

Lots of people didn't care. They just wanted to stick dynamic content on their pages.

Most of them didn't care about things like transactions. They either never heard of them in the first place, or figured they didn't need them.

Most of them didn't care about portability.

A few of them found that MySQL was fast when you don't care about these things, and that it was easier to install. The told others, and it became 'cool'...

I'm not knocking the above, most of them were probably self taught, and have learned a lot since. The MySQL userbase seems to have matured a lot, and as MySQL becomes more like a 'real' DBM, it's users are becoming more like 'real' SQL admins.

The only real competition at the time was postgres, and it was slower, and possibly more difficult to install. Those of us like me who cared about things like transactions took the extra time to make it work, because we wanted what MySQL didn't offer.

80% solution

[Sri Lumpa]

MySQL succeded because it took an 80% approach (a.k.a. "worse is better") whereas PostGres took a 100% approach (a.k.a "the right thing").

If you don't know what these mean read: http://www.dreamsongs.com/WorseIsBetter.html and http://www.jwz.org/doc/worse-is-better.html

a relevant quote: "The lesson to be learned from this is that it is often undesirable to go for the right thing first. It is better to get half of the right thing available so that it spreads like a virus. Once people are hooked on it, take the time to improve it to 90% of the right thing."

phpMyAdmin

[mikeboone]

One of the reasons I use MySQL is because it's easy to deal with remotely. phpMyAdmin is a robust tool for working with an online database. The phpPgAdmin tool is way behind in features and usability. I have one site on Postgres, but it's a lot harder to work with because of this.

Re:Question SHOULD be: Why did Postgres grow so sl

[Anonymous Coward]

Because Postgres doesnt support native Windows

Re:Question SHOULD be: Why did Postgres grow so sl

[pnaro]

I'll still stick with PostgreSQL, but then I am a Mac user ;-) I look at MySQL like I look at Windows... popular and sort of what I need, but it still falls down on the job.

Problems with postgres in production

[codepunk]

I like postgres but the ability to keep it online 24/7 cost us a trip to the oracle camp I am afraid. We ran our manufacturing floor on it but had problems with scalability and keeping it up 24/7. I still to this day have to sit down and run vacuum on all of the databases on sunday night right before first shift starts. We where running a dual zeon box
with about 200 connected users running simple small selects and it would just bring it to it's knee's. I had the developers change the app to always drop the connections and this helped a little bit but I still
had the problems with vacuum. Running vacuum with the user connected just brings the database to a near halt or locks them out all together. Not running vacuum causes the database to come to a near halt in under 6 days

If I had to do it again I would go with mysql. I should not have to babysit a database every week and it should be able to handle more that 200 read connections on a dual zeon 4gb ram box.

Re:Problems with postgres in production

[Anonymous Coward]

You obviously haven't availed yourself to the excellent documentation. You make no mention that you even attempted to tune the database and running a vacuum full (as opposed to regular vacuum) locks tables.

All of these production "problems" you report could have been easily addressed in my experience.

Re:Problems with postgres in production

[GooberToo]

You might want to consider upgrading. Most (all?) of these issues have long been resolved. In fact, the latest versions now support auto-vacuuming.

As long as your application is not holding transactions open and never completing them, vacumming should not be a problem at all.

If you are having serious performance issues, I sincerely hope that you've contacted someone on the mailing list to determine if they can be resolved. It's certainly possible that PostgreSQL is not right for the job. More often than not, the situations that you described almost always turn out to be improper performance tuning or unrealistic performance expectations for the available hardware; lumping the blame onto PostgreSQL's shoulders.

It does sound you have some problems there. What did the developers say when you asked for performance tuning assistance?

Re:Problems with postgres in production

[Quill_28]

Well said. Every one in while an intelligent response is given to a serious question here on slashdot without arrogance and flaming. Thank you.

Now about your user id...

Re:Problems with postgres in production

[GooberToo]

LOL! Thanks for the kind words.

As for the ID, it's a good reminder that life's too short to take everything so seriously. ;)

Cheers!

Re:Problems with postgres in production

[GooberToo]

I would like to add that I'm fairly sure the original post is a troll. I don't expect a follow up response at all.

But, just in case....trying to be helpful isn't a crime. ;)

Re:Problems with postgres in production

[Wolfier]

Upgrade to at least 7.3. It has online vacuuming, which means vacuuming can happen concurrently with normal database operations, so put it in your cron and do it every hour. Coupled with a high enough fsm pages and the only downtime you'll be looking at are backups. If you run a replicator you don't even have to shut down your master database for backup, so it'll be truly 24/7.

Postgres can trivially handle 200 connected users running small selects. Something must be seriously wrong with your postgresql.conf. Take a look at it, or replace your DBA.

Why MySQL Grew So Fast?

[Anonymous Coward]

INSERT INTO Body (Stomach) VALUES ("Vegetables")

Why don't more people use SQLite?

[Catullus]

For the sort of simple, single-user applications that MySQL is frequently used for, I don't understand why SQLite isn't used more often.

It's fast, simple, lightweight, and completely free, with no licensing restrictions prohibiting it from being incorporated into commercial products.

Am I missing some reason why it's unsuitable for most people?

Re:Why don't more people use SQLite?

[quantum bit]

For the sort of simple, single-user applications that MySQL is frequently used for, I don't understand why SQLite isn't used more often.

One word: Marketing. Nobody has heard of SQLite. I discovered it only recently -- somebody made a passing reference to it in relation to some embedded device development. I looked it up and it looks really slick. I'm planning on giving it a go the next time I have a need for a small database-driven app.

Translation for the non-DBA /. crowd

[Anonymous Coward]

> Reasons NOT to Use Foreign Keys constraints:
> The speed impact is terrible for INSERT and UPDATE statements, and in this case almost all
> FOREIGN KEY constraint checks are useless because you usually insert records in the right
> tables in the right order, anyway.

Just thought I might put this in a context that non RDBMS experienced /.ers will understand. Imagine Bill Gates saying the following about the Windows XP firewall:

"Reasons NOT to monitor outgoing network traffic:
The speed impact is terrible for monitoring outgoing network traffic, and in this case outgoing traffic monitoring is useless since almost all outgoing network traffic is benign in nature anyway."

Now be honest, would you think "what a prophet is this guy, he's absolutely right", or would you look for another firewall?

That is where the lack of respect for mySQL as a RDBMS is coming from. It is no RDBMS, but they try to sell it as one.

As for its worth, sure, mySQL can be a decent package to use... if you are looking for a storage system with fast retrieval possibilities, not if you want a RDBMS (or SQL DBMS for the purists) that safeguards the integrity of your mission critical data.

Failing silent

[Anonymous Coward]

I work with mysql as an persistence backend for
my (Jboss) application server, which works ok.

The last application I developed makes use
of J2ee transactions, and it turned me nuts to see
that the rollbacks weren't working. It took hours
before I realized that the problem was that I didnt
use transactional safe table-types (my fault so far).

Should mySQL tell something like 'not supported' to the application server on a rollback command
rather than silently ignoring it ? Besides, with
exentsive caching, this can lead to an inkonsistent cache (Jboss rolls back in cache, DBMS didnt...).

Stefan

Re:Failing silent

[Betcour]

Actually MySQL throws a warnign when using transactions with non-transaction aware tables (that's what the doc says anyway)

Re:Failing silent

[Anonymous Coward]

Where's the warning documented? I didn't see anything like that under COMMIT or CREATE TABLE.

And what good is a warning supposed to do? By the time I see it, I've already made changes in a table that won't let me roll them back!

MySQL

[Paulrothrock]

1. Free
2. Easy
3. Free
4. Simple
5. Free
6. Easy to remember
7. Free

SQLite has totally diff audience

[Anonymous Coward]

SQLite is targeted at a totally different audience.

SQLite is tiny SQL engine, easily embedded (and also scripted) from within an application. Its single data file concept is more along the lines of M$ Access.

My immediate reference to playing with SQLite was a direct replacement in Linux of the VB+Access application space.

Its a neat tool, very usable, portable, easy to work with. But not a competitor in any way to MySQL.
http://www.sqlite.org

JoeR

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:Easy answer...

[Anonymous Coward]

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?

Well, no. But I'm not building a bridge; I'm building a shed for my lawn tools. And maybe I don't need open-heart surgery; maybe I just have a headache, and need a painkiller.

It is incredibly elitist to suggest that we need engineers for sheds and doctors for headaches, because it ensures that only those who can afford an engineer for every shed or a doctor for every headache will be able to have sheds or pain-free heads at all.

Similarly, not everyone who uses data needs to be a professional.

The linked article itself links to a piece by Clay Shirky which, basically, says the same thing. You should read it.

Like it or not, MySQL is highly successful because it was (historically) oriented towards non-professional use in a way no other database was. Everyone likes to point out all the shortcomings of MySQL, but no one seems to be doing the same for "professional" databases, and why MySQL has beaten them in the popularity department.

If DBAs don't want to go the way of blacksmiths, they had better start.

Windows

[hayriye]

Installing and running MySql in Windows is very easy.

Running postgresql in Windows is nearly impossible.

One word: documentation.

[Anonymous Coward]

It's ease of use is coupled with arguably the best documentation for any software product around.

The trolls have already sounded off about MySQL's lack of transactions and the like. Fine. But if you're getting started with SQL and need a database that has thorough documentation and some speed under the hood for that niche of folks, then MySQL is da bomb.

And that's just the point. Database needs have left the castle walls of the big iron, elitist enterprise. Lots and lots of personal and SOHO solutions to meet, and MySQL can be the tool for those people. Get over it.

I repeat: the MySQL documentation is what draws a lot of people in.

I suggest

[Wolfier]

Renaming PostgreSQL to YourSQL

Re:Because they were the first to support subqueri

[Azghoul]

Oh come on now!

You're a Oracle DBA so of course Oracle is just as nice as MySQL for "ease of use". But please! Oracle is the worst, most painful, most obfuscated piece of shit software I have ever seen with regards to administration!

It's terminology is obtuse, it's "help" is a joke, it's fragile as shit unless you know exactly what you're doing, and it can crash horribly and not come back up.

Please.

Not everyone wants to have to pay a damn DBA (nothing personal mind you) to run a simple DB-driven app. It's sad watching all the haters moan about how MySQL isn't this, isn't that... all things it's not MEANT to be.

MySQL wins by Setup and Installation

[PetoskeyGuy]

MySQL is easy to install. I develop on Windows because I have to support lots of legacy products. MySQL has a windows setup exe. Download, run, it's installed. Simple, easy. It's not ACID compliant, but most people I know still thing MS Access is a Good Thing. Microsoft and McDonalds prove people don't want quality, they just want to get by easily. Ease of use wins most every time.

Windows still has 90% of the market and if it's not easy to install on those computers then how can any software program hope to achieve a large user base?

PostgreSQL does have a "windows proof of concept" that requires a Cygwin installation. It is a pain in the but to get running on Windows. There is a native port in the works, but it's basically replacing Cygwin with MingW.

If I want I can download source and tons of other libraries I can get a work in progress running which failes several of the regression tests. If it's challenging for me to do it though, there is no way I'm going to require clients to do it.

Check out the Windows Status Page

I have been dying for PostgrSQL to come out with a windows port. I've used it before and love it. I would love to port my MSSQL Server apps to it. ACID compliance is a real requirement in some applications, but in general people get by without it because most people don't even understand what it is.

Re:MySQL wins by Setup and Installation

[maw]

Microsoft and McDonalds prove people don't want quality, they just want to get by easily.

I disagree. McDonalds is what it is because it's basically the same, worldwide. Wherever you are (within reasonably wealthy countries, anyway), you know you can rock up to a McDonalds and know what you're getting. It might be gross, but at least it's familiar.

In fact, Windows isn't much different. It's gross, and basically everyone in the know admits this, but at the same time it's just so ubiquitous, which is a major source of its continued success.

In either case, I think that people do want quality, and often do know the difference, but quality isn't an overriding factor in what they chose to buy. Price, availability, ubiquity, and many other factors come into play too.

Good and bad and the lie.

[LWATCDR]

The Good.
1 MySQL let thousands if not millions of people work with a SQL database server for the first time.
2 It is fast which means it could support a lot of people on a pretty cheap machine.
3 It is easy to setup and learn.
4 It is very good at provideing dynamic web content.

The Bad.
1 It did not support transactions or row level locking. An yes you need them for some applications. It is possible to program around the lack of these features but why?

The Lie
"You could also achieve efficient locking without row-level locks; in fact, supporting row-level locks took so much overhead that the application was almost better without them." ummmm.... No you are wrong. Okay you might notice that he said that applications where "almost" better off without them. That means that they are better off with them.

Don't get me wrong MySQL is a great product and does what 90 percent of the people that want to do. I wish them all the best.

My other pet peeve right now is the over use of MySQL in free programs. Does a cd catalog really need a SQL database server? Or an address book? Come on folks lets us BerklyDB or even a flat file for goodness sakes. Sometimes a SQL datase server is just over kill.

Re:Good and bad and the lie.

[Genus Marmota]

I though the InnoDB tables supported row-level locking. Doesn't it offer several different transaction isolation levels described by the SQL-92 standard?

Dumb Question

[MyHair]

Couldn't you have done the work with a FOSS database and then import to Access for presentation? Or use ODBC to use Access as a front end to a FOSS database? Would anyone have noticed?

These days my attitude is "get the job done, and if they want to fire me for it, fine."

If you want to learn a dbms

[Stone316]

Download DB2, Oracle or SqlServer. I'm not 100% srue about SqlServer but DB2 and Oracle allow you to download their software for free (with much better documentation) if you only use it for self-education.

Mines bigger than yours

[Anonymous Coward]

My database is free.

Ahh yes but my database is bigger.

Nope my database is faster.

Yeah but my database works on all platforms.

Replace database with wang and there you have it.

I mean come on guys put them away after all we all know its not the size of your database that matters it's how you use it ;)

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.

You use views to ....

[Stone316]

mainly to hide the underlying structure from users. Also, i've seen alot of very complex and long sql in views that you really wouldn't want to put into your application. As the grandparent said, it can also be used for security purposes, something which you don't want to code into your application either. Why? Because sometimes you can't guarantee users are going to enter the database via the application. Views can also be used for version control. Views are view usefull, are they required? Probably not but they sure do make life alot easier. The grandparent may not have stated his case well but there are many valid reasons as to why you'd want to use a DBMS that has views.

2 reasons that I can see

[Stone316]

1. There is a main company pushing mysql in the news and striking deals with other big companies such as Veritas and SAP. They appear to have a sales force that is very active. I rarely ever see anything w.r.t. postgres in the news. 2. Postgresql doesn't really support windows. I know, you can get it to run under Cygwin but most people don't want to hassle with that. They want native support. Is postgresql a better database? IMHO it wins handsdown but as we know there more factors to whether or not software gets adopted.

...or is it the license?

[Krehbiel]

In the article it says:

As I said earlier, dual-licensing is central to MySQL's business model. So under what circumstances must you license MySQL? There's a "nice guy" answer that's fairly clear, and a formal legal answer that's considerably murkier.

The nice guy answer is (I believe I am quoting Monty Widenius directly here): "If you distribute MySQL for free, you get it for free, but if you charge money for it you give us money."

Now, I think he meant to simplify, but that's such an oversimplification that it's just wrong (and it's wrong in such a way that, by misunderstanding, he would stand to profit).

Anyone can choose to license MySQL using the GPL, which grants the freedom to earn as much money using or distributing MySQL as you like (so long as the other license provisions are followed).

You only need to pay for a license if you want to do something that the GPL doesn't allow, like "distribute without offering source".

Astounding.

[hot_Karls_bad_cavern]

" Pardon my ignorance, but what is it exactly that MySQL is good at that Postgresql isn't at least equal or better at?"

Completely amazing that no one has *adequately* answered you on this question. i see responses of people whining that "you are not everyone" - yeah, not shit, genius. Or, "by god the package that i wanted to use needed mySQL" - nice. Way to *not* answer the question asked, fella. You didn't ask why people *chose* mySQL, rather what it does *better* than postgreSQL.

Once again, Slashdotters have missed the point entirely, correcly answered the question *not* asked and generally been useless except reminding you that you are not everyone...like, maybe you didn't already know that or something.

Seriously people, buck the fuck up and answer this question:

What is it (exactly) that mySQL does that is better, faster, or more stable than postgreSQL?

Leave your little app-requirements and need-it-for-legacy whinings out of it. Answer the question.

Re:Astounding.

[jesterzog]

Completely amazing that no one has *adequately* answered you on this question.

That's true.

I can appreciate that one thing I hadn't realised, made clearer by the responses, was that postgresql doesn't yet have a stable Windows build, and I suppose that's a reasonable enough reason to use MySQL instead. I hadn't noticed the easy-installation issue at all, but then I run Debian. I installed postgres with apt-get, and didn't have much of a problem adjusting the configuration files afterwards.

Most of the rest of it seems to be about MySQL being driven by it's existing popularity. People use it because they already have it, or because the application they want to use only works with it, and so on.

So I suppose there's reason enough why people currently choose MySQL over others, especially Postgres which is free. But if those responses are anything to go by, then MySQL doesn't seem very encouraging in it's future prospects.

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.

Mod Up

[Stone316]

Damn, if I hadn't posted to this discussion I would have modded you up. You hit the nail on the head... Alot of people in IT don't understand the big picture... Whether its through sheer ignorance or systems being so complex that you just can't know it all. And i'm afraid, seeing how software development is proceeding, its only going to get worse.

When I was in university people coded in C/C++ but now its Java, JDBC, SOAP, CORBA, XML, STRUTS, EJB's, .Net etc, etc. The list grows more each day.

Re:Win32 Binaries (Postgres via Cygwin)

[plastik55]

Well, having set up postgres on cygwin rather recently, I can say it's not as simple as all that. If you want it to run as a service, for example, there are quite a few hoops you have to jump through.

Re:Question SHOULD be: Why did Postgres grow so sl

[LWolenczak]

Postgres seriously lacks decent replication. MySQL has kick ass replication.

Screw that!

[ttfkam]

You are so right.

I guess I can see the argument for Windows, but *nix boxes? Is everyone trying to install from source. Obviously this is not the audience we were talking about. Nevermind that by source is not all that difficult either, with a package manager, it's downright embarrasing how easy it is.

Step 1: Install
% su (or sudo with the command)
% apt-get install postgresql-server
- or with apt-rpm -
% apt-rpm install postgresql-server
- or with just any old RPM -
download the RPMs and run
% rpm -Uvh postgresql*?

Step 2: Start the postgresql server.
% service postgresql start
- or -
% /etc/init.d/postgresql start

Step 3: Add your username to the postgres group.

Step 4: Become the normal user again.

Step 5: Create a database as that user
$ createdb <dbname>

Step 6: Connect to the database
$ psql <dbname>

Step 7: Start making SQL queries -- standard SQL99-compliant queries at that.

Can a MySQL advocate please explain how it would be easier to install MySQL? Please?

Which brings me to another point that MySQL folks love to make: there's more documentation available for MySQL. Okay, we've established that installation is a non-issue. What about usage? With both, you connect via a client and make queries. MySQL has some weird syntax and tragic quirks, therefore it's a good thing that there's a lot of documentation. PostgreSQL? Sure there are comparatively fewer books with "PostgreSQL" on the cover. But then, since it closely follows the well published SQL92 and SQL99 specs, most books titled with "SQL" should suffice. Take the books that teach to the spec versus books that teach MySQL's dialect and the scales tip in PostgreSQL's favor.

Has anyone else considered that PostgreSQL books simply aren't as necessary as MySQL? Books solely on MySQL should be renamed to another popular O'reilly series: the series with "Windows XP Annoyances."

I guess the reason they don't is that MySQL diverges so much from both SQL92 and SQL99 that you might as well not compare the two; It doesn't look too good to describe a complete product as an annoyance.

Re:Screw that!

[GooberToo]

All good points!

Re:Screw that!

[GooberToo]

Wow. Some idiot MySQL guy got some mod points and he's trying to punish me. Oh no. What a loser.

ROTFL!

[Anonymous Coward]

+Disadvantages: [of using foriegn keys]

+MySQL does not yet support enforced referential integrity, so if your
+application depends on it, you will not be able to use it with MySQL until
+we implement this feature.

BWHAHAHAHA... "The first disadvantage to using Foreign keys is that we don't support them."

How exactly is that a "disadvantage", and not a "our software isn't up to snuff"??!?!

Windows is not everything.

[Anonymous Coward]

MySQL is easy to install

Bullshit. Postgres is easier to install.

What you are talking about here is a preference.

It is a pain in the but to get running on Windows.

OK, there you have a point. But then Postgres isn't a Windows app. You should have just said "Postgres doesn't support Windows", and left it at that.

Scaling is overrated

[Anonymous Coward]

Scalability isn't a database feature; it's a total app feature. Most people can't write scalable code (heck, no one can for a given value of "scalable"); thus, the advantages of Oracle (or even PostgreSQL) are wasted on them. For these apps, MySQL has no significant drawbacks, and quite a few advantages.

Remember, there are a lot more "little times" than "big times".

You drive a Ford Pinto, right?

[ebuck]

Design does matter. Usually it won't be noticable until some specific event, but when that event happens (and it will), you'll get burnt.

What you do after you get burnt is entirely up to you. Some change products, some deal with loss of information, some work to recover what they had, some didn't have data important enough to bother with anyway.

The more work you do to discover "why" the more you'll view MySQL's rationale as excuses instead of revolutionary minimalisim. Soon the banter of "work around the problem", "Not necessary if you do this and then that", "Bad for performance when you could just do this", etc. will come off as shallow excuses for fundamental problems that have been solved since the 1980's.

Lacking good design doesn't matter until failure. Good design is what you apply to prevent failure or at least mitigate it's effects. In the Ford Pinto's case, the car's inherit ability to turn into a ball of flame didn't matter until someone hit it from the rear.

Professionals (in any profession) are held to a higher standard. Arguing that a "good" design is wrong is one thing, arguing that a "good" design is irrelevant is tantamount to negligence. Compromises to "good" design should be made, but only when they are understood and "give" you something in return.

Choosing MySQL gives you little (speed) and asks for a lot (you do the work an RDBMS would in your code) in return. New DB people won't know the difference, until it's too late.

Triggers are evil.

[Eric E. Coe]

They amount to a trap door into an unknown amount of code for an insert or update. Sort of like the "COME FROM" construct in INTERCAL. In large database systems involving multiple development groups they can become an interaction nightmare; killing performance and having strange exceptions bubbling up from previously working code.

OFFTOPIC-JIVE vs JIBE

[Anonymous Coward]

I am not singling you out to be jibed, but this is the third time in the past week or so that somebody has used "jive" rather than "jibe". In fact, I believe that this post is the first on /. to use the word "jibe" properly. To counter this very un-/. behavior, I have left my misspelliongs intact. http://dict.die.net/jibe/ http://www.wsu.edu:8080/~brians/errors/gibe.html

Re:OFFTOPIC-JIVE vs JIBE

[Unordained]

uhm, no. "Jive", as per your same dictionary reference, is a style of jazz, and can also mean to "dance to" or "dance with" ... therefore, two things not "jiving" means "not going well together, not dancing together" -- in this case, it means that the two concepts, two phrases, are incompatible, which is precisely what i meant. maybe you were trying to be funny by being wrong?

I'll tell you exactly why MySQL is popular

[bonch]

My god, you can't even do subqueries? What the hell? No views?!

Visit MySQL Gotchas for a list of the stuff you don't hear about mySQL around here.

MySQL is popular because it's easy to learn because it doesn't have all those "complicated" advanced features real databases have, so people who learn MySQL suddenly think they've become DB admin experts. Then they try Oracle or PostgreSQL or one of the other big boys, get confused and overwhelmed, and fall back on MySQL because it's easier for them despite having reduced functionality, often without them knowing that if they learned a real database they could do some incredibly powerful things with the braindead SQL they write to get MySQL to work. I've had to translate some Oracle single queries into as many as four for MySQL (!).

Seriously, that's why.

Re:Question SHOULD be: Why did Postgres grow so sl

[ahodgson]

replication is a lot easier when you don't have to worry about transactions and data integrity.

You can buy decent replication for Postgres if you need it, for now.

Why did it grow so fast?

[Marxist Hacker 42]

Because Microsquish and Orihole are just too damn greedy on per seat licensing. Anything more than 5 seats I use MySQL for.

Why MySQL bothers me

[bonch]

It's not that the fact it doesn't support basic features of other databases that bothers me, it's the fact that the developers openly discourage the need for those procedures, thereby pigeonholing you into coding their way and removing your choices.

What if I want to use stored procedures, even if they think I don't need them? You can implement them and still tell me they're bad (we're ignoring the fact that such an opinion is laughable), while still giving me the CHOICE to use them if I want to. They're friggin' standard SQL.

If a feature's not implemented because you haven't gotten to it yet, that's fine--I can understand that. It's free software. But don't sit there with a thumb up your ass telling me I don't need it when I say I do.

an Oracle-salesman level of cluelessness

[justins]

From the article:
But what about the case of the application service provider? This is a common problem in GPL-land that I don't believe has even been resolved. At a Birds of a Feather session at the MySQL conference one evening--a session well attended by about 25 very interested people--one programmer for a game company laid out a situation where they run their multi-user game on a server backed up by MySQL, and distribute only a client. Do they have to pay a license? After all, they're not distributing MySQL itself.

Zak Greant, a long-time MySQL public figure (listed in the conference brochure as their "community advocate") said the game company should pay. The game could not run without MySQL, and the client was the means of access by paying customers.

Several attendees then tried to extend Greant's reasoning. Why, then, shouldn't users of Web browsers pay license fees for accessing Web pages backed by MySQL? Well, besides the absurdity of trying to enforce such a payment regime, the Web server does not use a proprietary, specialized protocol as the game does.

This strikes me as the kind of stupid shit people are trying to get away from when they leave commercial databases behind. The "pay for GPL software because we say so" thing could get pretty interesting as MySQL becomes powerful enough to actually do wonderful things like sue their customers.

But I'm sure a software company would never do anything like that.

How to pronounce MySQL and PostgreSQL

[SlashdotStu]

I think it does have a lot to do with the name.
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?

I'm sure it was said in jest, but 30 seconds of "research" gives us the following...

From MySQL manual, "The official way to pronounce MySQL is ``My Ess Que Ell'' (not ``my sequel''), but we don't mind if you pronounce it as ``my sequel'' or in some other localized way."

From the PostgreSQL FAQ, "PostgreSQL is pronounced Post-Gres-Q-L."

Coz Postgres used to suck

[TheLink]

MySQL was probably the "best" available free "RDBMS" with decent performance during the dotcom times.

msql? Didn't try it, but MySQL appeared to be a superset of msql (not MSSQL).

While more sophisticated, Postgres95 just didn't cut it performancewise- I tried both MySQL and Postgres95. Postgres95 was just too slow for what I wanted to do. Was slow for reads, was slow for writes even with fsync off.

Things changed dramatically with Postgresql 6.5.3 (ok for reads still slow for writes) then 7.0, 7.1, 7.2 etc.

Now I have far fewer reasons to use MySQL over Postgresql 7.3 or 7.4.

MySQL's new licensing scheme doesn't help either.
Too much trouble to figure out when it is GPL and when it isn't, or whether that sort of thing is even valid.

sh, cut, paste, sed, grep, uniq, sort and join

[Anonymous Coward]

Granted it's slow, but you can code a relation database with the above UNIX tools. Why bother with SQL for small applications without rigorous speed requirements.

The value of stored procedures

[LinuxParanoid]

Maybe you should pay attention to the logical parts of his argument rather than the rhetorical ones? Like:

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.)

I don't have mod points but I do have database experience with enterprise databases as well as with MySQL and I think I'd side with ajs on the subject of stored procedures.

--LP

Re:The value of stored procedures

[ajs]

I assume you don't really mean that you agree with me, since you don't seem to. You probably just used the wrong username.

Here's the deal: Sometimes stored procedures are a win. Those cases are fairly limited. For the rest of the cases, it's a matter of propper data management.

For example, one of the classic reasons to use stored procedures is to "keep the code close to the data". But, that's a totally arbitrary concept. You can't have the stored procedures bypassing, security, transactional atomicity, constraints, etc. so there's just as much indirection. "Ah!" you say, "but the stored procedures execute inside the server!"

Do they? Do you halt your server in order to run them? No. So, you run them in another thread. Aha! That thread is close to the server! Sure is. You know why? Because it can access the same memory, but then so can your process. It's true. Go look at the implementation sometime. You're getting data via shared memory, which you have to negotiate control over (the same semephore-based locking a thread would have to use). So the only overhead is the negotiation of the shared memory, which the thread doesn't have to do.

If you think shared memory negotiation overhead is your bottleneck in a process that inherently involves disk reads... well, let's just say that hwo stored procedures scale or don't is not your largest problem....

To sum up MySQL sucks. So does every other RDBMS I've ever worked with, but that's because being constrained in your access to your data sucks. MySQL is a fine tool for many jobs, but if your job happens to not be one such... then I invite you to apply another tool. Knock yourself out.
Just don't go around saying, as the other poster did "Bull. Fucking. Shit." when someone says MySQL is an adequate job for most tasks, and an ideal one for many.

Re:The value of stored procedures

[ajs]

On re-reading there is ambiguity in my post:

Shared memory negotiation as in "setting up the segment and conferring access to it" is blurred with shared memory negotiation as in "accessing data in the segment in a negotiated (e.g. locked) way." Clearly when I said that a thread doesn't have that overhead, I meant the setup, not the locking, which the thread would still have to do.

Re:The value of stored procedures

[LinuxParanoid]

Nah, I respect you. I pretty much agree with your last statement "Sometimes stored procedures are a win." and even "MySQL is an adequate job for most tasks"...

I just disagree with your earlier statement that "Stored procedures and other similar features are just bloat, and gain you no real advantage".

I think there is some advantage, in situations where you have a transaction that wraps together a bunch of sql statements. That sort of transactional locking normally requires a decent scalability hit, particularly since you are holding that database transactional lock open waiting for all the milliseconds of both network latency and, for complicated queries, network bandwidth. Stored proceedures give you a straightforward way of avoiding all network latency and traffic, reducing the overhead of the transactional integrity substantially.

Seeing as you've worked with Sybase stored proceedures on your resume, perhaps I'm belaboring the obvious. It wasn't obvious to me that you had that experience when I read your first post (and I imagine it wasn't obvious to the AC either.) And having myself coded sybase stored proceedures, I'd have to say that it's such a constraining experience that I can appreciate the "it sucks" attitude... ;)

Cheers,
--LP