InnoDB Primary Key versus Secondary Index: An Interesting Lesson from EXPLAIN

I ran into an interesting issue today, while examining some EXPLAIN outputs, and wanted to share the findings, as some of this is undocumented.

Basically, you can start with a very simple InnoDB table – 2 INT columns, Primary Key (PK) on the 1st column, regular index on the 2nd:

  `id1` int(10) unsigned NOT NULL,
  `id2` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id1`),
  KEY `id2` (`id2`)

The query is:


This is a straight-forward query with no WHERE clause.

Given no WHERE clause, we know there will be a full table or index scan. Let’s look at EXPLAIN:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: id2
      key_len: 5
          ref: NULL
         rows: 1
        Extra: Using index

EXPLAIN returns NULL for “possible_keys”. This means there was no relevant index (though we see one was used at some point):

“It is possible that key will name an index that is not present in the possible_keys value. This can happen if none of the possible_keys indexes are suitable for looking up rows, but all the columns selected by the query are columns of some other index. That is, the named index covers the selected columns, so although it is not used to determine which rows to retrieve, an index scan is more efficient than a data row scan.”

So “possible_keys” returns NULL, but key returns `id2`.

Per the above, that means `id2` is a covering index.

This part is a bit strange, as it is not readily intuitive why this is the case.

I mean, the query asks for “id1”, yet EXPLAIN says “id2” is the covering index.

How is this possible and why isn’t just the Primary Key chosen anyway?

This is possible because of the way InnoDB’s secondary indexes are stored. In InnoDB, secondary indexes are stored along with their corresponding primary key values, so by looking at the secondary index, you can see the primary key values.

As for why this is chosen instead of the primary key, it took some digging, but I found the exact comments as to why in the source code (sql/ in the find_shortest_key() function:

"As far as 
1) clustered primary key entry data set is a set of all record
	fields (key fields and not key fields) and
2) secondary index entry data is a union of its key fields and
	primary key fields (at least InnoDB and its derivatives don't
	duplicate primary key fields there, even if the primary and
	the secondary keys have a common subset of key fields),
then secondary index entry data is always a subset of primary key entry.
Unfortunately, key_info[nr].key_length doesn't show the length
of key/pointer pair but a sum of key field lengths only, thus
we can't estimate index IO volume comparing only this key_length
value of secondary keys and clustered PK.
So, try secondary keys first, and choose PK only if there are no
usable secondary covering keys or found best secondary key include
all table fields (i.e. same as PK):"

So since secondary index entry data is always a subset of primary key entry, scanning the secondary index should generate slightly less IO than scanning the primary key.

Fwiw, this also explains why key_len is less if you specify “USE INDEX (Primary)” to force it to use the PK over the secondary index:

mysql> EXPLAIN SELECT id1 FROM t1 USE INDEX (Primary)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1
        Extra: Using index

Note this reports key_len of 4, whereas the former reported 5.

Hope this helps.


Comprehensive How-To for Enabling the Standard InnoDB Plugin in MariaDB and MySQL

I’m always switching back-and-forth between the 2 different InnoDB flavors in MariaDB – XtraDB+ and the standard InnoDB plugin, so I thought I’d simply post all of the various combinations in a single place. (And then I cover enabling the InnoDB Plugin in MySQL, since it’s an option in 5.1.) [Addition: Thanks to Andrew and Sergei for the tips on shortening plugin-load=. The changes are reflected below.]

Note: Below is for Windows. For Linux, simply change “.dll” to “.so” where appropriate.

MariaDB 10.0:

Do not add anything, as the standard InnoDB plugin is the current default (as of 10.0.3, although I do anticipate this changing in the near future, and I’ll update the post accordingly when that happens).

MariaDB 5.5:

# Enable the 2 below to disable XtraDB+ and enable the standard InnoDB Plugin

MariaDB 5.3:

# Enable the 2 below to disable XtraDB+ and enable the standard InnoDB Plugin

MariaDB 5.2:

# Enable the 2 below to disable XtraDB+ and enable the standard InnoDB Plugin

MariaDB 5.1:

# Enable the 2 below to disable XtraDB+ and Enable the standard InnoDB Plugin

Note that enabling it in 5.1, 5.2, and 5.2 are the same. As for 5.5, the only difference is that the name of the .dll has changed from “ha_innodb_plugin.dll” to “ha_innodb.dll” (so that needs changed in each place it occurs).

MySQL 5.1:

For MySQL 5.1, you would enable the InnoDB Plugin in the same way you would for MariaDB 5.1:

# Enable the 2 below to enable the standard InnoDB Plugin

MySQL 5.5+:

In MySQL 5.5+, the InnoDB Plugin is the default (and only InnoDB flavor of InnoDB in MySQL).

And finally, since I’m discussing the InnoDB Plugin, I do have some older posts on the subject for anyone who may be interested:

InnoDB Plugin Versions

Ease of Switching to the InnoDB Plugin and the Numerous Benefits

InnoDB Plugin Version Numbering in MySQL and MariaDB

Hope this helps. ๐Ÿ™‚


Awesome to see the MySQL Ecosystem Flourishing

I just wanted to take a moment and thank, notice, what ever you want to call it, but share some love with all those and all things MySQL.

I read the post Letโ€™s Celebrate MySQL 5.6 GA! – MySQL Community Reception by Oracle by Oracle MySQL Group and it got me to thinking of how proud I am of (and proud to be a part of) the whole MySQL Ecosystem.

We *should* all celebrate MySQL 5.6 GA! I well remember the 3.22 and 3.23 days, and there were many folks before me already using MySQL!!!

I love to see how it has continued to grow, the ecosystem and all things MySQL, that is. MySQL is better than ever. MariaDB is better than ever. Percona Server is better than ever. You have great Support options with MySQL/Oracle, SkySQL/MariaDB, and Percona as well – not to mention numerous others. I just love the fact that everyone is flourishing, and improving, and the technology continues to advance and most importantly, everyone is working together to provide a great software and service to all MySQL/MariaDB/InnoDB/XtraDB/etc-related users out there.

Not to mention the monitoring softwares, the various plugins and connectors, the numerous storage engines, the number of 3rd party apps & helpful scripts, User Conferences, local meet-ups, books, tutorials, the number of Community bloggers who freely and spiritedly share their knowledge, time, advice, ideas, concerns, and so on. It’s just a great Community to be a part of, and I’m thrilled to see how it’s grown, and continues to grow over the years. And that’s just a testament of the quality of all you great people out there involved and helping. ๐Ÿ™‚

I won’t get too mushy, but it is great to see the MySQL Ecosystem and all of those in it flourish, and may it continue for many, many years to come! ๐Ÿ™‚

Big thanks to everyone out there contributing! “It takes a village, and we are that village”, as my wife always says. ๐Ÿ™‚


How to Build MariaDB 10.0 on Windows from Source Code

I just wanted to share my steps for building MariaDB 10.0 on Windows (10.0.1 in this case):

cd maria-10.0.1
mkdir bld
cd bld
cmake .. -G "Visual Studio 10"
cmake --build . --config relwithdebinfo --target package

That’s it! The zip file is created right there in bld/.

Hope this helps. ๐Ÿ™‚

Related Build Links:


Enabling the Verbose InnoDB Lock Monitor in MariaDB and Percona Server for XtraDB+ and XtraDB

I enabled the InnoDB Lock Monitor in my MariaDB 5.5 instance (using XtraDB+ as the InnoDB – which is the default in MariaDB) and noticed that while the SHOW ENGINE INNODB STATUS was being logged to the error log, it wasn’t logging the “additional” lock information – it just looked like the plain ‘ole INNODB STATUS.

Long story short, Percona added a new variable so one has better control over what gets logged:


If off (default), then the InnoDB Lock Monitor logs the normal INNODB STATUS, and if enabled, then it logs it with the extended lock information.

They also created another variable that goes along with this one (and the InnoDB Lock Monitor), which is:


This variable indicates the number of locks to print that are held for each InnoDB transaction (the default is 10, max is 1000).

For reference, these 2 options are discussed further in Percona’s manual.

Hope this helps. ๐Ÿ™‚


Formatting SHOW ENGINE INNODB STATUS outputs when “\n” are embedded as Text

Lately I’ve received a number of SHOW ENGINE INNODB STATUS outputs with embedded “\n” characters appearing as text, but not true line breaks.

Of course this makes reading a long, INNODB STATUS nearly impossible.

With Linux, you can more easily fix this using sed or awk (or if you have those on Windows w/ gnu or cygwin or the like).

I didn’t have either handy, nor a fancy editor (just using WordPad), but I did have a hex editor handy (necessary for such cases as this).

In the hex editor (I use Freeware Hex Editor XVI32 for Windows), fixing this took about 2 seconds.

I just replaced:

5C6E <-- which is the text "\n"


0D0A <-- which is the true carriage return/newline

I realize this is a rather specific issue, but in case you run into it, here is an easy fix.

Hope this helps, and happy INNODB STATUS parsing. :)


How to Enable the Original InnoDB Plugin in MariaDB 5.5

As I mentioned here, there is a slight change for enabling the [original] InnoDB Plugin in MariaDB 5.5 (as compared to how you would enable it in 5.1).

Remember, in MariaDB 5.5, if you do not “enable” (i.e., add anything to the config file to do so) the InnoDB Plugin in MariaDB 5.5, you’ll end up with XtraDB+ for your InnoDb plugin. However, if you do “enable” the InnoDB plugin, then you end up with the original InnoDB plugin provided by Oracle/InnoDB.

The change is that the plugin file (.dll for Windows, .so file for Linux) which was previously named “ha_innodb_plugin.dll” is now just “ha_innodb.dll”.

Thus, if you previously enabled the plugin with (would have been in a 5.1 instance):


You now need to use:


That is the only difference, so after making that change, just restart your instance, and you’ll be in business.

For reference, if you encounter this, you might see an error similar to the following in your error log:

120524 19:24:56 [ERROR] Can't open shared library 
 'C:\Program Files\MySQL\MariaDB 5.5\lib\plugin\ha_innodb.dll' 
 (errno: 0 The specified module could not be found.)
120524 19:24:56 [ERROR] Couldn't load plugin named 'innodb' with 
 soname 'ha_innodb.dll'.
120524 19:24:56 [ERROR] C:/Program Files/MySQL/MariaDB 5.5/bin/mysqld: 
 unknown variable 'innodb_buffer_pool_size=10M'
120524 19:24:56 [ERROR] Aborting

Hope this helps.


How to Build MariaDB 5.5 on Windows from Source Code

I built MariaDB 5.5.24 on Windows yesterday from source, so I just wanted to share my steps.

Here is the short version:

bzr branch lp:maria/5.5 maria-5.5
cd maria-5.5
mkdir bld
cd bld
cmake .. -G "Visual Studio 10"
cmake --build . --config relwithdebinfo --target package

Done! Nice, neat zip file is created right there.

Here is the longer version with outputs for those interested:

C:\mariadb-5.5>bzr branch lp:maria/5.5 maria-5.5
Connected (version 2.0, client Twisted)
Authentication (publickey) successful!
Secsh channel 1 opened.
Branched 3418 revision(s).

C:\mariadb-5.5>cd maria-5.5

C:\mariadb-5.5\maria-5.5>mkdir bld

C:\mariadb-5.5\maria-5.5>cd bld

C:\mariadb-5.5\maria-5.5\bld>cmake .. -G "Visual Studio 10"
-- Check for working C compiler using: Visual Studio 10
-- Check for working C compiler using: Visual Studio 10 -- works
-- Detecting C compiler ABI info
-- Detecting C compiler ABI info - done
-- Check for working CXX compiler using: Visual Studio 10
-- Check for working CXX compiler using: Visual Studio 10 -- works
-- Detecting CXX compiler ABI info
-- Detecting CXX compiler ABI info - done
-- MariaDB 5.5.24
-- Check size of void *
-- Check size of void * - done
-- Packaging as: mariadb-5.5.24-win32
-- Looking for include files CMAKE_HAVE_PTHREAD_H
-- Looking for include files CMAKE_HAVE_PTHREAD_H - not found.
-- Found Threads: TRUE
-- Looking for pthread_attr_getguardsize
-- Looking for pthread_attr_getguardsize - not found
-- Check size of time_t
-- Check size of time_t - done
-- Could NOT find Boost
-- Cannot find wix 3, installer project will not be generated
-- Looking for MFC
-- Looking for MFC - not found
-- Configuring done
-- Generating done
-- Build files have been written to: C:/mariadb-5.5/maria-5.5/bld

C:\mariadb-5.5\maria-5.5\bld>cmake --build . --config relwithdebinfo --target package
lots of output

    46 Warning(s)
    0 Error(s)

Time Elapsed 00:23:18.16

Success \o/

Package is named:

Full location:


C:\Users\Chris>mysql -uroot -pmysql -P3313 -e"select version()"
| version()      |
| 5.5.24-MariaDB |

With XtraDB+ (i.e., InnoDB plugin is not enabled), it starts fine, and uses InnoDB version 1.1.8-24.1.

If you try to enable the original InnoDB plugin, note the library is now named ha_innodb.dll (or instead of ha_innodb_plugin.dll (or, which is what it was named in MySQL/MariaDB 5.1. I discuss this topic in more detail here, if you’re interested.

Hope this helps.

Further resources:

Full Details on CMake here:

For details on building on Linux, please see here:

With InnoDB’s Transportable Tablespaces, Recovering Data from Stranded .ibd Files is a Thing of the Past

Being a data recovery specialist and having recovered countless GBs of corrupted, and/or stranded, InnoDB data in my days, I am very happy to hear about the new InnoDB Transportable Tablespaces coming in MySQL 5.6!

Back in the day, if you had a stranded .ibd file (the individual InnoDB data file with –innodb-file-per-table option), you basically had nothing (even though that file contained all of the data). This was because unless you had the original instance that that particular .ibd file (table) originated from, there was no way to load it, import, or dump from it. So it was not of much use, though all the data was *right* there.

Thus I created the method of Recovering an InnoDB table from only an .ibd file (I should note that this was before the InnoDB Recovery Tool had been released, which can also be used to recover data from a stranded .ibd file too).

However, if you’ve used either my method or the InnoDB Recovery Tool for such a job, it can be a bit of work to get the data dumped. For those experienced, it goes much faster. But still, you cannot get any faster than just being able to (roughly) import the individual tablespace right into any running MySQL 5.6 instance. ๐Ÿ™‚

Nice work! ๐Ÿ™‚

Note: Again, I must mention this is only in MySQL 5.6, so if you have a stranded .ibd file you need to recover data from pre-5.6, you’ll either need to use my method or the InnoDB Recovery Tool.


Create MariaDB Windows Service

I’d had some difficulty manually creating my own windows service for MariaDB (worked fine from the installer), but it was due to the way I was creating it, so I wanted to share the proper approach:

Old Way:

sc create "maria55" binpath= "\"C:/Program Files/MySQL/MariaDB 5.5/bin/mysqld\" 
\"--defaults-file=C:/Program Files/MySQL/MariaDB 5.5/data/my.ini\"" 
DisplayName= "Maria55" start= "auto"

New Way:

sc create "maria55" binpath= "\"C:/Program Files/MySQL/MariaDB 5.5/bin/mysqld\" 
\"--defaults-file=C:/Program Files/MySQL/MariaDB 5.5/data/my.ini\" maria55" 
DisplayName= "Maria55" start= "auto"

The key is adding the name, maria55, after the –defaults-file=.. option, but still within the “” that belong to “binpath”.

This extra parameter exists so that mysqld knows whether or not it was started as a service or not.

Without it, the server does not know, and therefore didn’t realize it was running as a service, and thus since the service manager got no response from mysqld, it terminated the service after 30 seconds (though I could connect and issue any MySQL command or query within that 30 seconds).

Many thanks to Wlad for helping me to track this down!

For reference, here is my terminal output:

C:\>sc create "maria55" binpath= "\"C:/Program Files/MySQL/MariaDB 5.5/bin/mysqld\" 
\"--defaults-file=C:/Program Files/MySQL/MariaDB 5.5/data/my.ini\" maria55" 
DisplayName= "Maria55" start= "auto"
[SC] CreateService SUCCESS

C:\Users\Chris>net start maria55
The maria55 service is starting.
The maria55 service was started successfully.

With the initial service attempt, the service creates fine, but fails after 30 seconds:

C:\Users\Chris>net start maria55
The service is not responding to the control function.

More help is available by typing NET HELPMSG 2186.

Hope this helps.