They use MySQL? Yes. I told you so...

You know, Jeremy, I really can't understand why you're so set on MySQL. Have you even tried other databases such as PostgreSQL, Firebird, etc.?

I have years of experience with MySQL and picked up PostgreSQL about half a year ago. It's a dream to work with compared to MySQL. I really don't know why I'd use MySql at all except for legacy stuff. And maybe full-text indexing if I ever really actually need that (frankly I don't know if Postgresql offers that or has it planned).

Bonus points for coining the term "Mr. Database Jackass!"

Yes, I have learnt not to argue with any self proclaimed expert. The trouble is that in the world there are many self proclaimed computer experts. Very fustrating!

WRT Mysql: How do you get around the fact that there are no views in Mysql?

As my great grandfather was fond of saying:

"Expert comes from two different words. Ex, for former, and spurt, from little drip under pressure."

MySQL does a good job of being very, very fast. It also appears to be good at replication. Given the choice though, all other things being equal (which they never are) I'd go with PostgreSQL though. Being able to do views, subselects, etc in a production stable version is just too nice to pass up. I've found that going from Oracle to PostgreSQL is much easier than going from Oracle to MySQL.

I haven't used it yet, but PostgreSQL has a contributed module to do the text indexing called
tsearch2. One of these days I'll get around to playing with it. Maybe it will become part of the base PostgreSQL system one day. As for getting by without views, in one website that is using MySQL I basically provided the view feature in the application code. Not fun, but it worked.

For the record, I don't feel that I'm a DB expert, self-proclaimed or otherwise :-)

Here's a simple way of recognising such "exports"... Ask them "How much do you know about XYZ?"... If they answer "nothing" or words to that effect, chances are they know nothing about it. If they answer "everything" or similar chances are that you're dealing with one of these idiots. Most people who are really knoledgable on a subject will answer "a bit" or "a fair amount" or something like that. Why? Because these people know enough about their subject matter to know where their knowledge is weak and recognise that you next question might touch on one of these weak spots. Most self proclaimed exports don't know enough about the subject to know that there are things they don't know.

Damn these "experts" - people who think they know everything make it difficult for those of us who actually do :-)

Damn! I thought the link for "self-proclaimed database expert" would point off to who it really was! Now that would be balls-to-the-wall!

He'd probably rather remain anonymous.

I have to echo many of the previous posters concerns about MySQL's increased use in mission-critical environments. Given that MySQL has a very nasty habit of altering column types and inventing default values and primary keys for you, it doesn't seem terribly wise to depend on it when the safety of one's data is important. Not to mention that it lacks constraint checking and it doesn't validate many of its data types (the DATE type comes to mind), or that it's limited transaction and referential integrity support are trivial to disable. See http://sql-info.de/mysql/gotchas.html for examples of what I mean.

I'm quite happy that lots of companies are realizing that open source databases are useful, I just wish they'd pick PostgreSQL or FireBird.

1) It's MySQL's press releases, only really trust the quotes from the clients.
2) None of the applications rely heavily on transactional integrity (not to the point where a slight corruption of data could mean significant losses)
3) Theres DBA idiots everywhere
4) The 'gotchas' page says a lot but theres more if you look.
5) tranactional integrity and fantastic speed have been demostrated to be two mutually exclusive ends of a spectrum of databases. ie ACID compliance comes at a performance price.

conclusion - MySQL is a great tool for delivering data quickly (ie read heavy, write light). If the application will be write heavy and you have a need for transactional and referential integrity, do your research before choosing MySQL.

also - for all of the advantages of MySQL, for 90% of the applications it's used for, a flat file database or SQLite would be a better choice.

Man, I love it when jackasses like that get proven wrong.

Mr. DBE, OpenFTS is a full-text search add-on for PostgreSQL. If you want to look at example usage, take a look at OpenACS.

Jim O'Halloran, I totally agree with you. "Real experts" tend to have learned enough, in general, to understand that they do not know everything, and probably will not without considerable effort. Deep down inside, I acknowledge that I know a lot about certain things, but I don't display that externally as some sort of badge of honor. I also recall that, when I was a teenager I was very much overzealous and thought I knew everything. As I started working and then continued in my career I matured to the point where I understood that I don't know everything. And so I strive to learn as much as I can, which frequently involves shutting up and listening to those who know more than I do.

Mr. Database Jackass should learn to do the same.

MS Access is the one and only best database. Ever. Period.

Hi Jeremy,

Is it possible that the "self proclaimed db expert" was talking about the reservation system ?? That would be running on legacy system (read mainframes) and therefore he must have been so sure !!!

Cheers,
Ravi...

Tim,
I do have to disagree with you that if you have a write heavy application that you shouldn't use MySQL. We currently have a write heavy application with 440:1 (insert/updates to selects). And as of right now we are doing "Queries per second avg: 6307.693". All on a single dual proc box, and it works like a charm!

Most people base their experience of MySQL 3.23, but in my case we are using MySQL 4.1 and it does everything I could ask for. MySQL 3.23, works perfectly fine, it just doesn't have all of the features that most of the other DBs have today. Sure, I could have spent a few hundred grand for Oracle or used Postgres. But why, then I couldn't use PHPMyAdmin. :)

Actually, to me everything is about speed! I've been one of those jackasses Jeremy is talking about. I've been a Oracle DBA and a SQL Server DBA in the past, I'd take MySQL over either of them anytime!

Another thing you have to consider is that MySQL is now developing the SAP DB or MaxDB which is supposed to be released before the end of the year. Which this, you will then at least in my opinion start seeing the combination of both of the DBs becoming one over the next year or so. And in my opinion MySQL will get some great new features.

Now if I could only get the MySQL team to release 4.1.1 so I can get my 6 subselect with 4 full text matching query to work. Nothing like 10 queries in 1. And no, Oracle, MS, or Postgres support anything like it!

Now if somebody would convert dBase III+ to the web, I might have to drop MySQL for it.

Donny

Donny,

I have a couple of comments about your post:

Why are you running alpha code (MySQL 4.1) in a production environment?

If you want a PHP interface for PostgreSQL, check out http://phppgadmin.sourceforge.net/.

A database is not "all about speed." In fact, speed should not be at the top of a DBA's concerns -- data safety and integrity should be. It is in these areas that MySQL is deficient.

What evidence do you have to claim that Oracle, SQL Server or PostgreSQL won't handle your "10 queries in 1" query ?

phppgadmin has advanced a great deal recently (2.x - 3.0 - 3.1). It's getting pretty comparable to phpmyadmin.

Donny,

I base my experiences on the available production versions of MySQL. I really look forward to being able to use SAP DB as it's supposed to be a very robust database. As for MySQL, I've no doubt that the InnoDB tables will be fast but there is no evidence that says they are faster when used in a high load production environment.

As has been said, you don't find out that a database is not referentially and transactionally secure until something goes really wrong. If you're in a situation where your data can be restored from a backup and you've lost nothing more than a few hours transactions then no problem. If your database relies on it's integrity (imagine stock control, trading systems, etc) then inevitably, speed will be less important. Hence, even it were proved that MySQL is faster in situations under load using innodb, this still wouldn't make MySQL the best choice.

Heres a sample of why NOT to use MySQL in situations where data is important.

You build a database using innodb with a lot of referential integrity. You are repairing a table and think you've ensured that there is nothing relying on it before you drop it and recreate it.

With MySQL, even though you have a foreign key constraint against the table and there is data that relies on this table. The table will drop without warning. All of the data that relies on this will now be useless.

Postgres/Oracle/Informix will tell you that you can't delete this table because other tables are relying on it.

Again, think about which database to use. In some cases you would benefit by using Oracle/Informix/Postgres/SAP DB over MySQL. Just remember, for the price of a weeks work scratching your head over broken data or bugs in the way the db works, you could buy a second database server.

Derek,
I'm not saying that Postgres, Oracle, or SQL Server couldn't handle some of my queries. Hell, MySQL can't handle some of the queries that I want it to handle when I'm sure some of the others can. But you learn to adapt to the software that you have. Change a query here or there and you have the same thing you originally wanted and sometime it's even faster!

Why would I run alpha code in a production environment? Because we tested MySQL 3.23.x, 4.0.x, 4.1, and Postgres 7.3.3 and MySQL 4.1 met our needs the best by offering us the most features we needed and it was the fastest on a query by query basis. And these weren't simple single table joins, each of them had at least 5 joins per query.

Now as far as data safety and integrity goes, we have been running MySQL for 4 years now. We have lost data once, and that was because a raid controller failed and two drives died at the same time. But still, we were back up in 30 minutes time and we actually lost 5 minutes of data. That is because we replicate all of our servers to another server via MySQL and then all of the databases are backed up every 10 minutes. Sure it may not be transaction safe in 3.23 without INNODB, and you know what I don't care. Since transactions weren't around when we first started using MySQL we have learned ways around needing transactions, which we have found to actually be more reliable that using transactions.

And please don't consider phppgadmin even close to PHPMyAdmin! I would consider it like comparing Notepad to Microsoft Word on Windows. Sure they allow you to type or write SQL statements, but that's where the comparision ends. It still needs a lot of work. And yes, I played with it for about an hour before writing this.

I'm still not saying MySQL is the best, as I said I still like Dbase III+, but it works for me and my company. And I think it works for Jeremy.

> And please don't consider phppgadmin even close to PHPMyAdmin! I would consider it like comparing Notepad to Microsoft Word on Windows.

haha, that's funny since I use notepad far far more than I use M$ word. What doesn't PHPPGAdmin have? I can do everything I need with it - browse data, alter tables, export data, and tons more.

Tim,
About InnoDB, actually we have found them to be slower, currently, than MYISAM. This is because of all of the overhead that is involved with an InnoDB table. And actually based on the MySQL documentation that was just recently updated, Slashdot is using InnoDB with MySQL, and some site that is using it has reported a 800 inserts/updates per second. So I might actually need to give it a try again myself.

Now as far as dropping an Innodb table that has foreign keys, since I am no foreign keys expert. This is from the Innodb manual:

InnoDB allows you to drop any table even though that would break the foreign key constraints which reference the table. When you drop a table the constraints which were defined in its create statement are also dropped.

Now personally, I have had to repair a MySQL table twice in 4 years, in a production environment. The last time was 6 months ago when we had a table that had a blob field in it and I needed to empty the table. Unfortunately, I should have just emptied the table, but instead I said delete from table. After about 10 minutes, I just killed the process and the table was all messed up. So I had to repair it.

Well, I can say that I have run into about 5 or 6 bugs in the past that have been in MySQL. And you know the first thing I do? I go to one of my C programmers and tell him to fix it then send the patch directly to Monty. You see it helps when we have had the MySQL trainers come down and spend a week with all of my programmers and Network Operations group. Then about a month later we had the entire MySQL development and management team spend a week in our training facility and you get to sit down a talk with all of the developers of MySQL. I've emailed both Bill Gates and Larry Ellison and told them I would switch to their product if one of them would bring me to lunch, but they haven't responded yet.

So would I ever switch to another DB from MySQL? Probably not, because everytime I have found a limitation in MySQL, there is either a work around or a way to fix the problem. And with our tests that we have done, we have found MySQL to be much faster than anything else. One of our programmers found it fast enough to write a full mail server in MySQL that receives on average 500,000 messages or about 17 gigs a day and it then stores them in MySQL before sending them out.

Would I be able to process that many emails on a Celeron 1.8ghz machine? I don't think so.

Donny,

Glad you put a lot of though into what database to use. With your investment into MySQL it seems that it's the best choice for your application. Being as you've invented a better way of handling transactions than the transactions that the rest of the database world knows them, could you blog the details? If you want to discuss further, we should keep it off jeremys blog. my email address is obvious.

Tim

Donny,

Yes, you did say that PostgreSQL, Oracle, et al couldn't handle your queries: "Now if I could only get the MySQL team to release 4.1.1 so I can get my 6 subselect with 4 full text matching query to work. Nothing like 10 queries in 1. And no, Oracle, MS, or Postgres support anything like it!"

I must say again that I'm shocked that you are running alpha code in a production environment. Yes, MySQL 4.1 has more features (I hear they finally have some decent error reporting - no more silent InnoDB -> MyISAM conversions!) - but they're not complete and not well tested yet.

Also, you seem to confuse physical damage to the DBMS' files with logical corruption of the database (the logical organization of tables, relations and constraints). I never made any comment about the physical aspect of MySQL - my issues are with its logical layer.

As regards coding around MySQL's (many) limitations - why are you supporting this behavior? In PG (and most other SQL databases), you don't have nearly as many limitations to code around because the DBMS is better built. Why emulate transactions and enforce referential integrity in each and every app (which means writing, documenting and testing this behavior in each and every app), rather than doing it once when you create the database? I'm so sick of the MySQL "do everything in the app layer" mantra that I could scream.

Yes, you can try to code around the lack of decent transaction support, but why should you have to?! Just pick a DBMS that supports what you need to do, rather than being an apologist for the blatant lackings of MySQL. PostgreSQL has had transaction support for nearly a decade - they've had lots of time to get bugs worked out. I'd far rather trust my data to those years of work than to some solution that some random programmer came up with to code around lack of transaction support in MySQL.

And, please, stop focusing on speed as the primary factor determining the DBMS to use. Speed is important, but data integrity and safety are far more important. As I've said in a previous message, it is in these areas that MySQL is seriously lacking.

Ok, I started responding to this thread and it got too long, see my trackback at http://www.vbmysql.com/mike/blog/archives/000043.phpIn essence: Just use the right tool for the job. Sometimes it will be MySQL, sometimes it will be PostgreSQL. Just stop being so damn religious about it.

DBA are right in that PostgreSQL and about anything else is better than mySQL. Even access can do some things better than mySQL.

The problem is that SQL databases are vastly overrated. There are myriad ways to store information and SQL databases are just one of them. Plain files are perfectly good for a lot of things. A simple table oriented database is more than enough for 70% of what people need to do, and this is not postgresql fault. That's why mySQL succeds.

Or would you store your binary-coded mp3 as records in a table?
SQL databases ARE OVERRATED. DBAs are OVERRATED.

i bet if Yahoo! were starting up today, they would go with postgresql - and possibly python too

I would have loved to see a whole list of happy corporate users of MySQL because I just had to aexplain to a customer that only because M$ charge you £6000+ for your MS SQL copy doesnt mean you get more of it, especially when all you have is 3700 entries and want to tie the payments to the system web side.

Hmmmm

Why do people do stupid things like through the money away?
Answer: Because they can!

The same with all these self made specialists telling this poor client that the only way to do their thing is to move from Access to MS SQL...

Could it be because they write code using VS 2005 and dont have a drag and drop tool to MySQL like with Oracle or MS SQL?

and please give me a break on dBase.
I used to be the biggest promoter of Access until I hacked their passwords out in 0.3 seconds regardless of the mdb origination point. Even MDE files posed no obstacle after a while and for security reasons I changed to MySQL.

Scalability>????

9,000,000 address records in a single table to MySQL using MyISAM as an engine and full UTF8 settings as default charset.

Done deal, works online, off line, to C, C++, C#, VB 5, 6 and VB .NET, and VBScript/ASP/ADO JavaScript Java and more.

Why leanr more than one language when their all supported in one place?

Why does MS only support 81 languages for their hefty user license fees when MySQL is free can can use 650? Read the specs and do the tests and come up with your own codes to figure out better ways to solve your missions goals. THen I'm sure any database would do the trick, but...

once again, it all depends on the client trusting your judgement on what works the best together with what, now doesnt it?

Amazing that this thread has relatively new entries. I'm sure that MySQL has morphed way beyond what it was when the discussion started--3.5 years ago.

I found this just looking to decide between MySQL and PostgreSQL for a wiki backend. I'm going with MySQL even though my instincts say Postgres, because:
1. a wiki has more reads than writes (I hope :-)
2. MySQL is faster
3. MySQL is preferred by the wiki I've chosen

What I like about this discussion:
1. It's still alive
2. It helped me to choose
3. It's easy to tell the zealots from the reasoned thinkers

This is still a very relevant topic. IF you had the details you would be impressed. It's all about better, faster, cheaper. Peel off functionality, scale out, offer more than you could before, reduce your overall costs, limit risk while doing it -- seems like more about better, faster, cheaper.

Morphed, yes ... same basic principle, yes.

There are so many options with MySQL and many architectures. The price points introduce a new way of thinking about your business.

The people who are complaining about MySQL are people who do not know about it. They come from another DBMS and try to use MySQL like their previous DBMS. When MySQL will not work like the way they wanted to be, they complain. They then put on more like that's how database system should work etc...

I have used SQLServer, Postgres, Oracle and MySQL. When I first started MySQL, I installed it, learned it and written a small application to understand its' strength and weaknesses. Then I try to fit it in the project picture. Before MySQL I have been using SQLServer, Oracle and Postgres. So, if I try to use MySQL in the way I use Oracle I think I will be a stupid person.

Look! Some of the shortcomings MySQL has are not so bad. It kick the ass of lazy programmer. I also believe that stored procedures are evil. A lot of system I found put business logic in stored procedures. My belief is that stored procedures should only be used by DBA for maintainence jobs.

Lastly, if you have huge database, upgrading is just pain in the arse for Postgresql. Speed of pg_dump, pg_restore improved alot in 8.1,8.2,8.3 but still sucks for huge db. Why the hell on earth they are thinking that backing up in sql and replaying it one by one is the solution. Can't they just upgrade file system data store to a new version format. If you upgrade from 7 series to 8 series, some functions' interface change in tsearch2 and you have to change in your queries. Which is a bit sucks.

Oh, another thing. A lot of people in IT say about "Enterprise System". I am sure it is just a "pimped up" word. A lot of people do not want to use a product which do not have "Enterprise" mentioned in the name. If you can finish the job with a needle why you want to use the sword.