MySQL 5.1 Improves Performance, Partitioning, Bug Fixes

kylehase writes "CIO.com has a writeup about MySQL's 5.1 release planned for next week. Among the enhancements are many bug fixes from 5.0, some of which may increase performance 20% or more, as well as 'partitioning, events scheduling, row-based replication and disk-based clustering.'"

Comments

It's nearly caught up to PostgreSQL.

[Anonymous Coward]

MySQL has nearly caught up to PostgreSQL in terms of features.

PostgreSQL's Generalized Search Tree (GiST) indexing is still better than anything MySQL has to offer, in terms of performance and capability.

The PostgreSQL OpenFTS full text search engine is another marvel of engineering. It routinely outperforms similar extensions for MySQL in terms of performance, memory usage, and concurrency.

I hope that an upcoming release of MySQL deals with the maximum field size problem. With PostreSQL, there is a max field size of 1 GB. For MySQL, it's a mere 50 MB. For textual representations of certain geographic system data, it's not unusual these days to have individual fields that need to store 500 to 600 MB of data. PostgreSQL handles these fields fine. MySQL fails.

Re:It's nearly caught up to PostgreSQL.

[IversenX]

MySQL fails in many other cases, too.

Many people see MySQL as the consistent winner in database benchmarks. I don't mean this in a bad way, but a lot of people are so focused on the performance of MySQL vs. PostgreSQL, that they forget that MySQL is usually only fast for really simple queries.

That would be fine, though, if it weren't for the failing integrity.

In terms of data integrity, PostgreSQL is kilometers ahead of MySQL. With MySQL, I have seen tables get badly corrupted, sometimes even beyond repair(!) if a disk runs full. That's simply unacceptable.

The syntax is also pretty lax. Adding an integer and a string? No problem. String and a float? Sure.

You want a contraint? Sure, it'll accept that query. Will it honour the constraint? Not so much.

Createing an InnoDB table, for (some) referential integrity? Sure, it'll give no errors, but if innodb support is disabled for any reason, it will create MyISAM tables instead, without any hint or warning. This has the potential to create great data loss.

Inserting a row with a primary key value outside the legal range? It'll give no errors, but it also wont insert the row. Instant data loss.

I know it's popular database, but I would probably not recommend MySQL for any project. If you need something lean and fast, try SQLite. Then you _know_ you don't get any type checks and fancy things like that, so you code for it. If you want to proper, free database, go with PostgreSQL. Half-baked is not my kind of tea. I really hope they will work on data integrity in the upcoming releases, but I fear it's not going to happen.

Re:Decipher for non DB types

[theantix]

- Disk based clustering: I assume this means I can dynamically expand the size of my database by adding more disks. Is this correct? Does PostgreSQL also support this (my project where this would be handy currently uses pgsql)? Disk based clustering only applies to people using the MySQL NDB Cluster product, which is quite different from the traditional MySQL product. So for the vast majority of MySQL users who use MyISAM or InnoDB tables, this doesn't really affect them at all.

- Partitioning: I can think of several things this could mean.. Splitting data among several tables at some logical dividing point. Or, limiting the size of tables so they can't overrun the complete storage space. What does this mean in MySQL 5.1 terms? This means splitting an existing table along logical dividing points, but still acting as a single table. Let's say you partition it by date, well then you would insert/select/update like normal -- but a query or update that looks at the date would only have to look at a smaller partition of the table to know what row needs to be updated.