MySQL has two great features which historical haven't played well together:

1. Index-only queries:  In some cases, MySQL can resolve a query directly from the index, without having to read the underlying table.
2. Prefix indexes:  This allows you to specify how many bytes to index, which can reduce index size or allow you to index the larger data types (ie. BLOB/TEXT).  The drawback being that the entire field isn't stored in the index, so you can't do index-only queries.

One common optimization we do to reduce IOP consumption on database servers is to add additional columns to indexes in order to allow more queries to be index-only.  However, sometimes we have these large TEXT fields in order to allow for larger content -- even if the content is normally very small.

For example:

CREATE TABLE tbl (

  …

MySQL has two great features which historical haven't played well together:

1. Index-only queries:  In some cases, MySQL can resolve a query directly from the index, without having to read the underlying table.
2. Prefix indexes:  This allows you to specify how many bytes to index, which can reduce index size or allow you to index the larger data types (ie. BLOB/TEXT).  The drawback being that the entire field isn't stored in the index, so you can't do index-only queries.

One common optimization we do to reduce IOP consumption on database servers is to add additional columns to indexes in order to allow more queries to be index-only.  However, sometimes we have these large TEXT fields in order to allow for larger content -- even if the content is normally very small.

For example:

CREATE TABLE tbl (

  …

I finally posted a copy of the slides from my Percona Live presentation, “Creating a Benchmarking Infrastructure that Just Works”.  The PDF is available via this link.

The content comes from my personal experiences over many years benchmarking and testing databases, usually focusing on performance.  It was an opportunity to see how far my personal benchmark infrastructure has evolved, but even better has inspired me to improve it in several areas.

I never had a chance to to my own post-conference wrap-up regarding the Percona Live show.  While waiting for my flight home at SFO airport I concluded that it was by far the best technology conference I’ve ever attended.  The …

This is part 2, you can find part 1 here.

So in part 1 we learned how to calculate the free space within InnoDB. But unfortunately that won't always work perfectly.

The first issue: the DATA_FREE column in the INFORMATION_SCHEMA.TABLES table will not show a sum of the free space of each partition. This means that if you have innodb_file_per_table disabled and are using partitioning then you must divide DATA_FREE by the number of partitions.
This is Bug #36312.

Example:

mysql> SELECT CONCAT(T.TABLE_SCHEMA,'.',T.TABLE_NAME) AS TABLE_NAME,
    -> P.PARTITION_NAME AS PART,IBT.SPACE,IBD.PATH,T.DATA_FREE AS T_DATA_FREE,
    -> P.DATA_FREE AS P_DATA_FREE FROM INFORMATION_SCHEMA.TABLES T 
    -> LEFT …

It is a central part of the MySQL philisophy to try and help you as much as you can. There are many occasions when it could tell you that what you are asking for is utterly stupid or give you a bad execution plan because "you asked for it". But we're friendly here. We don't do that. One of those cases is when you run a query with an outer join but you really meant an inner join, or you don't care.

Inner joins are almost always cheaper to execute than outer joins and that is why MySQL rewrites them to inner joins whenever it can.

An outer join may have WHERE conditions on any of the columns in the result set. In fact, it is a common trick to find non-matching rows using the IS NULL predicate. Here's an example:

TABLE person

name  id
Tom   1
Dick  2
Harry 3

TABLE car

brand …

Many people write blogs and emails about various anniversaries. I had an anniversary when I started writing this blog on the 2 may where I celebrated 23 years since I my start date according to my employment contract. 2 may 1990 was the first day I worked at Ericsson where I started working on databases and where NDB Cluster was born that later grew into MySQL Cluster.

Actually this is a year of anniversaries for me. I started by becoming half a century old a few months ago, then I celebrated a quarter of a century as member in the LDS church, my wife and I just celebrated our 25th wedding day and in september I will celebrate 10 years of working with MySQL together with the other people that joined MySQL from Ericsson 10 years ago.

So I thought this was an appropriate timing to write down a little history of my career and particulary focused on how it relates to MySQL and MySQL Cluster. The purpose of this isn't to …

Percona is glad to announce the release of Percona XtraBackup 2.1.1 on May 15th 2013. Downloads are available from our download site here and Percona Software Repositories.

Percona XtraBackup enables backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered free as an open source solution, XtraBackup drives down backup costs while providing unique features for MySQL backup. The new 2.1.1 GA …

MySQL Community team is pleased to announce following events as the ones supported by us with a great MySQL staff attending. Find more details below (or at our Community wikis).

• NoSQL Roadshow, Berlin Germany, May 16, 2013
  
  • Talk: NoSQL & MySQL by Mario Beck
• Solutions Libres & Open Source, Paris, France, May 28-29, 2013
  • Talk: NoSQL & MySQL by Olivier Zemrag
  …

Recently someone asked my if it's possible to find the total free space within InnoDB. I thought this would be very easy as the INFORMATION_SCHEMA.TABLES table has a DATA_FREE column. So we could just use SELECT SUM(DATA_FREE) FROM INFORMATION_SCHEMA.TABLES couldn't we?
&nbsp
So what does the DATA_FREE column tell us? It tells us the free data within InnoDB for that particular table. A table can share a tablespace with multiple other tables.
&nbsp
The tablespace which is used by a table depends on whether the innodb_file_per_table was enabled during table creation and/or at the last time the table was rebuild (e.g. by OPTIMIZE TABLE).
&nbsp
If innodb_file_per_table was always disabled then this query probably reports the correct free space:

SELECT DATA_FREE FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='InnoDB' LIMIT 1;

This is because all tables will share 1 …

Occasionally it is useful to know what a MySQL command is doing internally. Just looking into the MySQL source directory can be overwhelming. Knowing the basics of the handler interface and the sql parser can be a great start for reading the source code to understand what MySQL does under the hood. Here I will cover a little bit about how the SQL syntax is defined.

Everything starts with lex.h and sql_yacc.yy in the sql/ dir. lex.h contains all the functions and symbols used to make up the SQL syntax. The sql_yacc.yy file describes the relationships between these symbols and the C functions responsible for executing them. I’m not sure why some symbol definitions end in _SYM and others don’t. Looking in lex.h “FLUSH” is defined as FLUSH_SYM. To see all the places where flush is allowed in the SQL go back to sql_yacc.yy and grep for it.

The first important section looks like this:

/* flush things */ …