My Sql

A FreeSoftware and OpenSource RDBMS (Relational Database Management System).

Benefits of MySQL (I've picked these up after five years' experience with MySQL, three with PostgreSql, and some horrible experiences with Oracle):

It's relatively clean (no huge grammar quirks, simple privilege administration)
easy to maintain and upgrade, does not have a slew of administrative tasks to put up
its table format does not vary between releases (but the old ISAM table format is being dropped out after having been shipped sidy-by-side with the new(er) MyISAM format for quite a few years)
it does not have its own data / index caching but uses the filesystem's one (when using MyISAM tables). Result: faster, can take advantage of OS's fs caching and journaling, simple backups, less vulnerable to crashes.
it has cleanly separated table handler modules and can mix access to different types of tables.
it seems to be developed iteratively, and the features are very stable when they ship them. This is the biggest reason I like MySQL more for production environments than PostgreSQL.
one gets a lot of control on how things are done.
it's fast. (This, they advertise a lot. But in practice, it seems to be so.)
it doesn't carry a lot of historical baggage, thus for example the transaction support can use state-of-the-art solutions like MVCC.
mostly harmless as a learner system

And the drawbacks in my experience:

mostly harmless as a learner system
unnecessarily weak SQL syntax.
client library versions seem to get bumped up randomly between releases, and sometimes an upgraded server will mean your client libraries stop working.
endless fights on licensing. ("Just what does the GPL mean?")
some features (most importantly, transactions and subqueries) have lagged for a long time. MySQL has been YAGNI from the beginning, and only slowly becoming what we need, for a lot of us.
use of threads causes problems on some platforms. (That, I think, is why PostgreSql uses SYSV IPC - ick.)
some features that are useful for some of us (like views) are still lacking.

(Note that some of the above are being addressed in recent or coming versions and thus may be a non-issue by the time you read this.)

In some benchmarks, PostgreSQL was reportedly better than MySql in several aspects, such as speed.In my experience, no database has ever beat MySQL in speed in any of the applications we've made (mostly accounting programs with substitutable database backends because customers want to use their own database of choice).

MySQL *can* beat PostgreSQL in speed, on the same hardware, with low numbers of simultaneous connections. Not by much mind you, but it has the capability to. Once there's a reasonable number of simultaneous connections though (i.e. more than say 3) PostgreSQL craps all over MySQL.

MySql didn't have transaction support for the longest time, but that's not the case any more, as of version 3.23.33.

I've been bitten more than once by the fact that table names are case-insensitive on Windows but not Unix (tables are saved as files named for the table, and MySQL doesn't worry about SQL's rules regarding case-insensitivity in that situation). When you're writing a query, make sure that you capitalize the table's name in exactly the same way it was capitalized in the definition.

Forgive me, but isn't this just good practice all the way around? MeaningfulNames and all that?

Hmm, no Stored Procedures and Triggers. Not too useful a database without these!

In the BetterLateThanNever? category, these are on the way with the impending release of MySql5.

StoredProceduresAreEvil

I've always wondered why some programmers want to shift logic on the database side, given how abysmally hard triggers and SQL procedures are to maintain and read. SQL is very old and quite bad technology, I want to write as small a part of my application in SQL as possible. On a large system with a lot of data, it's sometimes nice to be able to perform data intensive operations as close to the data as possible for performance reasons. It also then lets the DBAs take the raw SQL and optimise it and make it performant while "normal" programmers focus on the rest of the app. For example, I can write some very scary SQL that does complex stuff, and is even maintainable and tested - but it may be horribly slow. When you're dealing with a couple of million rows and multiple linked tables, there's much magic a good DBA can work. --StuartScott

They are currently probing "Zend" for their back-end language.

(From the manual) A framework for external stored procedures will be introduced in the near future. This will allow you to write stored procedures in languages other than SQL. Most likely, one of the first languages to be supported will be PhpLanguage, as the core PhpLanguage engine (the Zend Engine) is small, thread-safe, and can easily be embedded. As the framework will be public, it is expected that many other languages will also be supported.

For me, it's a quick and dirty database which just does its job. The SQL syntax is very limited, and there's no featurewise comparison to real databases, like oracle or adabas. I use only MySql. -- ReiniUrban

So use PostgreSql, which does compare to real databases. I never understood the fixation on MySql when PostgreSql is better and equally available. -- RobertChurch

PostgreSQL wasn't natively available on Windows until PostgreSQL 8, so MySQL got an earlier start there although it's a far weaker product.

It's not as if every single use of a database was for a bank or the DepartmentOfDefense?. Yes, there are many applications where MySql would be completely inappropriate. But it's extremely lightweight, which makes it really useful in lightweight apps that could use easy SQL storage. It runs on a wide variety of tiny machines. It comes standard on most Linux distros.

If you don't believe that there are many people who find it useful, go over to SourceForge and do a search for MySql, and see how many projects are based on it.

MySql seems to be de rigueur for low cost web hosting.

It's the 'M' in LampPlatform (and the WampPlatform)

The "official" wiki (at least for the #mysql channel on irc.freenode.net ) is here: http://www.hashmysql.org/

What the heck is a transaction, and why would you need subqueries? I'm going YouAintGonnaNeedIt on this complaint.

What???? You don't know what a transaction is, so you assume it must not be important? On the contrary, if it doesn't offer transactions, it's a toy, not a real database.

To me it has always seemed very simple: if you don't know what something is, don't bother having an opinion about it. I can't imagine the justification for trying to rationalize having an opinion in the absence of information.

Subqueries aren't critical, but they are very convenient. Without them many complex queries require creating a temporary table, fooling around with it, and then deleting it.

They are nice for "summary of summary" kinds of things like this from SqlFlaws:

  SELECT AVG(GPA) FROM (
SELECT AVG(grade) AS GPA FROM Grades GROUP BY studentID
  )

Serious though, who needs transactions? Take the following example. How could this possibly go wrong?

  UPDATE Table1 SET Col1 = Col1 - SomeInptuValue?;
  UPDATE Table2 SET Col1 = Col1 + SomeInputValue? * SomeOtherInputValue?;

Safe as houses.

I use MySQL, and I like it, and for some kinds of low end applications it is the database of choice, but it's not the ultimate RDBMS and its makers don't pretend otherwise. -- DougMerritt

The nice thing is, with MySQL transactions are not a property of the database but the table handler...

You mean, that's nice because you can use them when needed, but aren't forced to if they're not needed? Yes, that has its good aspects -- although, since clearly not everyone knows what transactions are, let alone when they should be used, there is a lot to be said for transactions being the default, and tossing them out being the option. MySQL is the other way around.

''Anything not ACID compliant should never be used. Loss of data integrity should never be tolerated. You may as well use a flat file or a spreadsheet. Stick with Postgres, getting a Linux postgres server running is not that hard.'' --pjl

{If skipping it offers speed, the trade-off may be worth it. Depends on the domain.}

Who said MySQL doesn't offer ACID? It has since InnoDB was introduced at some point in the 3.x series. MySQL is now up to major version SIX and will soon have around 3 more ACID engines: PBXT, Falcon, and Solid. Furthermore, PostgreSQL is far from the only alternative. There are also Firebird, Ingres, MaxDB, and several others. -- TobyThain [tired of anti-MySQL FUD]

Use a screwdriver for screws, and a hammer for nails. Sometimes MySql is the right tool for the job. You also need to get some information that isn't 2 years old: http://www.mysql.com/news-and-events/press-release/release_2002_11.html

I just look at MySQL and see nothing that would make me switch to it for the sort of jobs it is purportedly ideal for.

Could you name some of those, please? DB newbies need to know, since some of us [ahem] have to make a choice about DB for a startup project, and that choice could byte some of us [cough] with some big teeth.

Everyone using MySQL should read this: http://grimoire.ca/mysql/choose-something-else

For a "database backed web site" where nearly 100% of the SQL is going to be SELECTs, SqLite is faster than MySql and doesn't require a db engine.

See: AssertEfficientSql, LanguageIndependentServices

CategoryDatabase