Some things are known to be just bad. GOTOs used to be one such thing (something I still use them, but only where appropriate, which isn't that many places). Maybe it is just so, that some things are useful, but not for everything, so maybe the issue is that they are used inappropriately. Or?

The OR condition is one such things in MySQL circles! Oh, you have an OR condition! That is going to be so slow! sort of. And the reason an OR is "slow" is that as MySQL will use only one index for each statement, only one "side" or the or condition can use an index. Or sometimes even worse, MySQL will consider using an index that is common to the two "sides" or is outside the OR conditition, despite that fact that there are perfectly fine, highly selective indexes on both sides of the OR condition.

If you ask me, this is not a fault with the OR condition but rather a problem with the MySQL optimizer. Why in heavens name can't a …

While looking at partitioning I recently made a mistake which I guess can happen to others. Often this is due to not fully reading the documentation or scanning it too quickly and misunderstanding what’s being said.

So this post is to complain about the MySQL partitioning syntax and to warn others who may easily make the same mistake without realising.

First we probably need to ask why we are partitioning a table in the first place. The main reasons for this are I think:

• to improve query performance
• to reduce individual .ibd file sizes for large tables (if using innodb_file_per_table)

In my case I wanted to do both. I had a several tables which store a large number of rows (batches of data) based on an incremental batch number. One of these tables was around 40 GB and had about 500,000,000 rows in it.  When processing data in this table often all the data from a particular batch …

Indexes are a very important part of databases and are used frequently to speed up access to particular data item or items. So before working with indexes, it is important to understand how indexes work behind the scene and what is the data structure that is used to store these indexes, because unless you understand the inner working of an index, you will never be able to fully harness its power.

The purpose of this post is to describe what covering indexes are and how they can be used to improve the performance of queries. People mostly use indexes to filter or sort the results but not much thought is given to actually reduce the disk reads by using proper indexes. So I will show you how to reduce disk reads and hence improve the performance of queries by utilizing indexes properly.

Vadim and others have pointed at the index->lock problems before, but I think they didn’t good job enough at pointing out how bad it can get (the actual problematic was hidden somewhere as some odd edge case). What ‘index lock’ means is generally the fact that InnoDB has table-level locking which will kill performance on big tables miserably.

InnoDB is a huge pie of layers, that have various locking behaviors, and are layered on top of each other, and are structured nicely as subdirectories in your innodb_plugin directory. Low level storage interfaces are done via os/ routines, then on top of that there’s some file space manager, fsp/, which allocates space for btr/ to live in, where individual page/ entities live, with multiple row/ pieces. There’re few other subsystems around, that got …

I recently gave a presentation at the New York Effective MySQL Meetup on the new features of, and some of the compelling reasons to upgrade to MySQL 5.5. There are also a number of new MySQL variables that can have a dramatic effect on performance in a highly transactional environment, innodb_buffer_pool_instances and innodb_purge_threads are just two to consider.

For more information on all the new variables, status, reserved words and benchmarks of new features you can Download Presentation Slides.

Download PDF Presentation
Download Audio

There are a number of significant new features in MySQL 5.5 including semi-synchronous replication, SIGNAL and RESIGNAL, the PERFORMANCE_SCHEMA, additional STATUS variables, new partitioning options, different default storage engine, better UTF8 support and removal of deprecated functions just to list key considerations.

However some of the performance improvements are worth the investment of time. For a high concurrency InnoDB environment one new configuration alone can provide a …

This week we post th' audio from th' closin' keynote o' Percona Live by Harrison Fisk o' Facebook, "MySQL at Facebook, Current and Future", I'll warrant ye. There is no ear candy this week.

On July 12th at 9 AM PST I will be giving a webinar about performance implications for Hibernate and Connector/J. If you cannot attend at this time, a recorded session will be available soon after the webinar.

MySQL is not only about LAMP, and a lot of people use it from Java apps. For some of those, Hibernate is the persistent framework of choice. This webinar will discuss the performance implications of using Hiberate to manage persistence with a MySQL backend, and also some more broad implications for Connector/J  that will apply for anyone using MySQL from Java.

Topics will include:
* Very brief overview of ORMs and Hibernate
* Fetch strategies
* Lazyness
* Manually written SQL
* Concurrency
* Concurrency-related config options for Connector/J

You may register …

Many people consider this information in the MySQL Query Execution Plan (QEP) to indicate that the referenced table is using an index. It actually means that ONLY the index is used. This can for larger and more frequent queries provide a significant boost.

In a recent example, adding an index dropped a query from 190ms to 6ms. However adding a better index dropped that 6ms query to 1.2ms. When executed 100s/1000s of times per second these millisecond improvements can have a huge benefit in greater scalability. While people often tune slow running queries, in a well tuned system shaving milliseconds of queries, in this example making 6ms query 80% better is a far greater improvement.

You can get a detailed explanation of how to identify, create and verify covering indexes from my Percona Live presentation Improving performance …