MySQL 5.0 Candidate Released

Brian "Krow" Aker (Former Slashdot Coder now MySQL Employee) writes "I am pleased to announce the release candidate for MySQL 5.0. This version has been in development now for three years. We have worked to add update-able views, ansi stored procedures, and triggers. In addition we have added a number of fun features that we are experimenting with and resolved issues with bad data inserts (which personally annoyed the hell out of me when we rewrote Slashdot a couple of years back so I am happy to see this issue go away). We look forward to feedback on the candidate and will show some love for bug reports."

Time for new comparisons to be made.

[CyricZ]

It's time for somebody to do a new, impartial study regarding the performance and feature benefits of this new release of MySQL, PostgreSQL, Firebird, SQLite, and perhaps other open-sourced relational databases.

Is it a "real" database yet?

[digidave]

Every time any database is mentioned on Slashdot we get a load of comments about how MySQL is not a "real" database because it doesn't support {insert random feature here}.

Is it a real database yet?

Add bloat, stay fast

[jurt1235]

I welcome stored procedures triggers very much, as long as they are fast enough to compete with the current "just program it yourself in you chosen language" style. Anyway: Another stored procedure language to learn.

How does the source code quality compare?

[CyricZ]

How does the source code quality of this new release of MySQL compare to that of projects like PostgreSQL or Firebird, which have a far longer history and/or were formerly commercial developments?

Re:Is it a "real" database yet?

[codesurfer]

I'd say so...despite the detractors, I've been able to use MySQL for many highly trafficed sites, and heavily used applications. Does it have the full robustness of Oracle? Of course not, but it also does not come with the accompanying price tag. Choice of db software is just that...a choice. I can envisage situations in which I may prefer having Oracle, but to be honest, MySQL addresses most day to day situations more than adequately. It's a real db, IMO.

Anyone know of a good free MySQL GUI?

[lion2]

Something that is comparable and as easy to use as SQL Servers Enterprise Manager. The tools available on MySQL's site aren't very good. My main concern is easy importing and exporting to and from Microsoft Access. For example I can just copy records from access and easily paste them into SQL Server. Thanks for the help.

Re:Time for new comparisons to be made.

[tdemark]

And of course you can't include Oracle in any impartial study.

You could, except for the fact that the license you just paid thousands per CPU for doesn't allow you to publish [orafaq.com] the results.

Re:SCO

[jurt1235]

Can we trust HP (linux everything campaign) while they are strategic SCO partner?
Can we trust Oracle (Linux is our main development platform) while they are strategic SC partner?
And many many more.
Maybe we can even find the website of a dictatorship using MySQL, oh.

MySQL 3 - MySQL 5

[Scoria]

I certainly have to give them credit for one thing. The MySQL developers have been subjected to some very harsh criticism over the years, but few would accuse them of ignoring it.

It impresses me that they actually seem to be listening.

Still waiting for a programmable GUI

[bogaboga]

I am still waiting for a database system/program that will have a GUI as programmable as that of MS-Access. I have not looked at this latest release, but all the GUIs I have looked at up to now do not cut it! The talk of PHPMyAdmin, Navicat, MySQL Control Center and the like, reaffirms my conviction of un-seriousness on the matter of putting business and common logic into database applications. Putting this kind of logic to applications is much more easier in Access but that belongs to Windows - sadly.

Re:Backups

[electroniceric]

Having to dump to a script is both a blessing and curse. For any database that dumps to less than the size of a CD, it's actually very portable, and allows you to clean up your database with substantially more ease than a binary format. The curse of course is that you can't just reattach your binary backup and have the database go again. Also, since 8.0, Postgres has had a binary backup format, and has done a thorough job resolving dependencies (a really nuisance in 7.x).

Re:Examine t he license carefully!!

[Scarblac]

In short:

• If you don't distribute MySQL, you're under no restriction whatsoever
• If you do want to distribute, MySQL is GPL
• Besides, MySQL goes out of their way to give you even more freedom to distribute it if you want to bundle it with stuff that has one of several incompatible licenses

Sounds good to me!

Re:Time for new comparisons to be made.

[ceeam]

But puhhhlease, don't put MySQL performance based on MyISAM and feature set based on InnoDB! Stick to either one. BTW - last I checked for myself, Firebird was beating _even_ MyISAM for raw speed on simple queries for local engine. Something I did not expect. Oh, yeah! When comparing performance please include results for local engine AND over-the-LAN performance. Different beasts have different protocol issues that show up when using "real" LAN and may be masked when using "local" engine mode. For example, MySQL has very simple and LAN-friendly protocol. Unlike Firebird, but it's still ok. And MSSQL, EG, has very, very serious issues when used for lots of "simple" queries.

And despite the fact that I feel postgres...

[MrBandersnatch]

is often the better database solution, I'll still be using MySQL. Why? Well a quick search on cwjobs shows 188 jobs requiring MySQL experience vs 7 for postgres!! Its a real shame but having used the best tool (C++ Builder) for my last job and then being unemployed for @2 years because people wanted either VC++ or Unix based C++ experience; I *WONT* be making the same mistake again!

Anyways, that said, Ive already played about with 5.011 and apart from the yukky syntax one has to use to support transactions it seems quite stable. Its might have taken a long time for them to finally make it a "real" database product but it seems good enough for small databases.

One of my next jobs is to test it with 10 million + records and see how it performs though so my assessment may be premature...

Re:I think you miss the point

[should_be_linear]

I can tell you what MySQL advantege over PostgreSQL and Firebird was *for me*:
1) Easy to install in Windows. PostgreSQL is (AFAIK) goof _now_ but was not then. Firebird was/is OK in this area.
2) "Spatial" type/index, Fulltext index. Postgis is available for PostgreSQL but as plugin, not out-of-the-box, fulltext is (AFAIK) beta and not very unicode-reliable. I prefer both things to be integrated as I distribute DB engine with my SW. Firebird miss spatial type (just as MS SQL, for example (!)) so it is useless for me.

One thing where MySQL suck is mingw (gcc) support on windows. If you need to connect from C/C++ in windows, use MS VS, otherwise you wil suffer.

A database is not a GUI

[msobkow]

The only so-called "database" that emphasizes it's GUI is Access. Every other vendor/product I'm aware of relies of separation of duties and doesn't try to roll user interfacing into what is rightly a back-end service.

Administration tools for commercial and OSS databases may be easy for small sites and novice DBA's that don't know their tools, but large applications rely on database scripts to handle configuration, not GUIs. The reason is simple: you can't put a mouse click into CVS/RCS/SCCS/???.

Re:Add bloat, stay fast

[DrXym]

Unless every single app you connect to the DB contains trigger-like code and you don't expect your users to abuse or circumvent the trigger-like code, I fail to see why you would not want them.

As for stored procedures, I thought the whole point of them is that they are precompiled and live on the server side. Not only does this make them faster but you can change or modify the stored procedure without having to change the client code. Again, what's the point of not wanting them?

Is there any evidence to suggest that "just program it yourself" on the client side would be any more efficient? It seems to me that sending a large chunk of SQL over the wire, parsing / validating it, compiling it, and executing it must be for any sane DB slower than a stored proc.

For good reason

[Stone316]

Most DBA's are adequate at best and most likely don't have much experience trying to squeeze every ounce of CPU out of a box. So I can understand why they do this... I personally don't take 'user' benchmarks very seriously because most times you have no idea how their environment is configured or if it is even configured properly.

Read the fine print...

[Decibel]

AFAIK the clustering and load balancing 'solution' for MySQL means your entire database has to fit in memory. Not very practical...

Re:Time for new comparisons to be made.

[awol]

Easy just report;
        Performance Prettiness Completeness
1. MySQL Firebird Postgress
2. Postgress MySQL
3. Postgress Firebird
4. Firebird MySQL
5. ... ... ...

Then oracle has no problems with the report since you are _not_ reporting anything about them.

Re:Please stop the MySQL Bashing...

[Naikrovek]

Yahoo! does not use MySQL for anything in production, at least they didn't when I was there. Jeremy Z. does work there, so that might have changed, but if you're trying to say that Yahoo! uses MySQL to store user data, or anything associated with a user profile you're dead wrong. UserDataBuckets.

Re:Please stop the MySQL Bashing...

[generalpf]

The MySQL guru at Yahoo! -- Jeremy Zawodny -- wrote an O'Reilly book on the different hacks and scrapes they needed to make MySQL perform properly. It's full of anecdotes of MySQL brainf**kery and whatnot. If you consider Yahoo! using MySQL to be an endorsement, try reading High Performance MySQL.

Re:Is it a "real" database yet?

[FatherOfONe]

People have mentioned in a few other posts that DB2 is "only" $700. People then mention that MySQL is $500. I would argue that this isn't the case. I would say that MySQL and PostgreSQL are free and DB2 starts at $700, but that cost is just the beginning. What happens if you run a processor with multiple cores? How much does it cost if you migrate off of X86 to Sparc? How much if you want to run it on one of their mainframes? It is my experience that Oracle and IBM tend to "give" away some of their products, but they do that to hook you in to the upgrades. In short, IBM and Oracle are not stupid companies and they want nothing more than to get their hooks in to your company. Given that the database is generally the cornerstone of the companies data, it is a great place for them to start. Then when your company "needs" a feature that isn't supported in the "standard" package the cost will go WAY WAY WAY up.

This isn't the case with PostgreSQL or MySQL. You never get that "drug pusher" type of attitude.

Re:A database is not a GUI

[humbads]

> so-called "database"

Right. Access is not a database, but only a front-end GUI program. What people call "Access" is actually the Jet Database Engine, which is implemented in a set of DLLs. It is similar to SQLite in that it is a file-oriented, rather than connection-oriented, database. Jet database files have a "mdb" extension.

Access can also act as a front-end to SQL Server databases, and has loads of import/export tools. Access (the GUI) is popular with me because makes it so easy to clean up and move data around between databases, text files, Excel files, etc. The Jet engine is free, and it is not a bad engine for everyday tasks involving a few users. The most severe restriction nowadays is the 4GB database size limitation and the fact that it is no longer supported by Microsoft. It will be replaced by SQL Server Express.

Re:Having worked with oracle 10i for the last year

[kpharmer]

> That page made 10 calls to the db with a total processing time of roughly 0.0055 seconds.

Right, looks like that's working well for you. With mysql getting that query performance on a 4m row table, it must be using an index, which means that you're selecting less than 3% of the data each time. The scalability issue emerges when your result set is 10% of the total table, you have to sort 100,000 rows, etc.

> Oh, it would still be available... I would just connect to a different db to pull it in. My code is
> already written for that to happen, I just need to change the connection string for the historical db.
> Right now it is exactly the same as the current db connection... so they both live in the same db.

sounds like a good plan

VACUUM

[commanderfoxtrot]

I had a similar problem only this morning. I deleted all the rows from a table (by using DELETE FROM) and SELECT COUNT(*) went from sub-second to two minutes.

That's after going from 400,000 rows to zero.

VACUUM ANALYZE made no difference; however VACUUM FULL sorted it out. You should really have auto vacuum running, but for the dev I am doing I prefer to do it by hand.

From what you're saying, this could be the reason why it takes so long.

For what its worth, I've moved to Postgres because it's just so more solid. It's fantastic software. And I know what I'm talking about- I'm heavily involved in (big) mainframe DB2 at work.