MySQL 5.1 Articles

Using Partitioning to Manage Satellite Networks

(2008.10.17)
by Guy Adams, Chief Technical Officer at Parallel Ltd.
Our product historically used an Oracle backend, and after implementing simple date based range partitioning we got a 20-30 time performance increase in our queries. We had used MySQL in other more minor products we had developed, but we couldnít move to it until partitioning was implemented. In Dec 05 we started to test MySQL partitioning, where partitioning syntax was supported but the optimizations were not in place. As soon as the optimizations were put in, we were able to run a side by side comparison against unpartitioned tables and Oracle partitions. Not only did we get in MySQL a 30+ times performance increase, but the MySQL version ran nearly twice as fast as the same Oracle configuration in a like for like test.

FC Inter Milan Using MySQL 5.1 Partitioning and Event Scheduler

(2008.09.10)
by Corrado Pandiani
If you are a football (soccer) fan, then F.C. Internazionale may mean something to you. Or perhaps its more common name, Inter Milan sounds familiar? Next in our Use Case Competition articles, here's Corrado Pandiani's experience, about using MySQL to power inter.it, one of the most popular soccer sites in Europe. Not only that, but inter.it is using the latest MySQL 5.1 technology.

Using Partitioning and Event Scheduler to Prune Archive Tables

(2008.09.05)
by Gregory J. Haase
First in our series of Use Case reports on new MySQL 5.1 features, we have Greg Haase of Lotame describing his innovative use of partitioning. Usually, the creators of new applications are unaware of the various tweaking that users may submit their features to, in order to achieve surprising results. We in the community team are usually on the tweaking side, and we like to surprise developers with (positive) side effects of the existing features. This time, we were caught by surprise. Greg's usage of partitioning and events is really cool!

MySQL Information Schema Plugins: the best kept secret of MySQL 5.1

(2008.02.12)
by Roland Bouman
MySQL 5.1 offers an extremely useful feature called information_schema plugins. This feature allows dynamic runtime loading of a shared library into the MySQL server to implement a table in the information_schema database. The SQL standard (ISO/IEC 9075-11:2003) allows database implementations to extend the information_schema. MySQL 5.1 transfers the possibility to do this directly to privileged database users so they can extend the information_schema themselves, in any way they see fit.

MySQL 5.1 Partitions in Practice

(2007.12.19)
by Giuseppe Maxia
This article explains how to test the performance of a large database with MySQL 5.1, showing the advantages of using partitions. The test database uses data published by the US Bureau of Transportation Statistics. Currently, the data consists of ~ 113 million records (7.5 GB data + 5.2 GB index).

Using XML in MySQL 5.1 and 6.0

(2007.12.13)
by Jon Stephens
In this article, we discuss the XML functionality available in MySQL, with an emphasis on new features coming online in MySQL 5.1 and MySQL 6.0. We assume that you already have a working knowledge of XML, and that you know what the terms ìvalidî and ìwell-formedî mean. We also assume that you have some knowledge of XPath.

MySQL 5.1 Article Recap

(2007.09.11)
by Jay Pipes
So, someone had a great idea to go back through the Developer Zone, which has become a bit disorganized of late, and put together an article with links and summaries for all the Dev Zone articles that discussed MySQL 5.1 features. I have done so, and also linked in many community member's blog articles and tutorials to give everyone a good dose of the features that make MySQL 5.1 worth investigating.

MySQL 5.1 New Features: MySQL Partitions

(2006.08.08)
by Jon Stephens and Peter Gulutzan
Partitioning is a way of pre-organizing table storage. You can say "some of the table's rows will go here, some will go there, still others will go to still other places". Often, depending on the storage engine, the effect is to spread the table's rows over different files or even different disks.

Partitioning with Dates in MySQL 5.1

(2006.07.20)
by Robin Schumacher
Many of you have responded very positively on the first and second partitioning articles that I wrote some time back. Itís clear that a lot of you are excited about the upcoming MySQL 5.1 release, and in particular, the new data partitioning feature thatís being offered. Thatís no surprise because, as we covered in the previous two articles, partitioning has some excellent performance and management benefits for anyone whoís building data warehouses or other large databases with MySQL.

Improving Database Performance with Partitioning

(2006.02.22)
by Robin Schumacher
A few years ago, I wrote an article entitled "The Foundation of Excellent Performance" (still available at http://www.tdan.com/i016fe03.htm) where I argued against the notion that SQL code was the number one contributor to performance in a database-driven system. Instead, I stated in the article that I firmly believed how good physical database design was far and away the leading component of superior database performance. In addition, I showed that Oracle's own research illustrated how poor design was the main culprit behind database downtime (planned or unplanned). In the years since then, I've not changed my stance and still think that any DBA who wants a high-performance database has got to invest in intelligent and savvy physical design to produce the kind of response times that make end users smile instead of scream.

MySQL 5.1's new XML Functions

(2006.01.12)
by Alexander Barkov and Peter Gulutzan
MySQL version 5.1.5 has functions for searching and changing XML documents. This article has examples.

New Event Feature in MySQL 5.1.6

(2006.01.13)
by Trudy Pelzer
In this article, I'll give a preliminary description of a new MySQL feature for scheduling and executing tasks. In version 5.1.6, MySQL has added support for events. That is, you can now say: "I want the MySQL server to execute this SQL statement every day at 9:30am, until the end of the year" -- or anything similar that involves any number of SQL statements, and a schedule.

More on MySQL 5.1 Partitioning

(2006.05.23)
by Robin Schumacher
In my first article on partitioning, I demonstrated that you can get some pretty impressive performance benefits when you properly design partitioned tables to match the SQL queries that are routinely shot across the bow of your database server. Iíd now like to continue in that vein and show you how easy and efficient partitioning is in terms of managing how partitions are created and removed within tables.

MySQL 5.1 New Features: MySQL Events

(2006.04.13)
by Peter Gulutzan
Andrey Hristov added "Events" for MySQL version 5.1 when he was working on his master's thesis for the University of Applied Sciences in Stuttgart. We liked the feature a lot, and now Andrey works for MySQL. An inspiring story, eh? And now MySQL 5.1 is getting stabler, it's "beta" instead of "alpha". So we can try some real work with this Events feature.