While we always want better performance and more and larger features for MySQL, those cannot just “magically appear” from one version to another, requiring deep architecture changes and lots of lines of code. However, there are sometimes smaller features and fixes that could be implemented by an intern or an external contributor, mainly at SQL layer, and that could make the MySQL ecosystem friendlier to newbies and non-experts. Making a piece of software easier to use is sometimes overlooked, but it is incredibly important -not everybody using MySQL is a DBA, and the more people adopting it, more people will be able to live from it, both upstream and as third party providers.

Here it is my own personal list of fixes for EXPLAIN messages. If you are an experienced MySQL user you are probably aware of their meaning, but that doesn’t solve the problem for beginners. The reason why I am writing a blog post is to gather …

It is time for the Query Tuning Webinar again! This year I will be delivering the Webinar on July 24 at 10 a.m. PDT, Advanced MySQL Query Tuning, hosted by Percona. I have included some new topics about loose and tight index scan and will also show some real world examples and solutions for MySQL query optimizations.

You can register for the Webinar here. It will also be recorded, so if you can’t make it on July 24, 10am you can always watch it later.

On July 24 at 10 a.m. PDT, I will be delivering a Webinar on Advanced MySQL Query Tuning. I will focus on optimizing the common slow queries with group by and order by. Those queries usually create temporary tables and perform a “filesort” operation. I will show how to optimize those queries so that they will be running significantly faster, which will increase the application performance and decrease MySQL load.

I presented a similar topic in April at the Percona Live MySQL Conference and Expo 2013. This webinar, however, will be more advanced and will also cover some additional topics like “loose and …

There are two PRIMARY directions to sort data in SQL: Ascending (ASC) and Descending DESC.
When these two sort definitions are put together in a single statement a filesort is produced.

Why do we want to avoid filesorts?

Filesorts are bad. 1st they tickle a thread based buffer called sort_buffer_size. Additionally filesorts reads the data twice, unless max_length_for_sort_data limit is reached and as a result the Filesort runs slower to reduce disk I/O. If you want filesorts to run faster at the expense of the disk increase the default max_length_for_sort_data. You can read the filesort algorithm here.

So, here is an example

 CREATE TABLE `ABCD` (
  `A` int(10) unsigned NOT NULL default '0',
  `B` int(10) unsigned NOT NULL default '0',
  `C` int(10) unsigned NOT NULL …

Over the last few weeks I’ve been looking at several customers’ slow query logs, and I found in many of them an odd type of query. These are SELECT statements that contain an IN clause that includes dozens, sometimes hundreds of values. These statements often end in the slow query log. I’m not sure if these queries are this way by design or if they are generated by a specific database development tool.

I did some tests in one of my own databases, one with only around 10K rows in its largest table. The database corresponds to the Amarok media player. For example, I queried for songs by B. B. King (spelled “BB King”, “B.B. King”, etc. or with other artists: “B. B. King & Eric Clapton”).

The first query used a JOIN and an IN clause with all the …