How X Plugin Works Under the Hood

X PluginIn this blog post, we’ll look at what MySQL does under the hood to transform NoSQL requests to SQL (and then store them in InnoDB transactional engine) when using the X Plugin.

X Plugin allows MySQL to function as a document store. We don’t need to define any schema or use SQL language while still being a fully ACID database. Sounds like magic – but we know the only thing that magic does is make planes fly! 🙂

Alexander already wrote a blog post exploring how the X Plugin works, with some examples. In this post, I am going to show some more query examples and how they are transformed.

I have enabled the slow query log to see what it is actually being executed when I run NoSQL queries.

Creating our first collection

We start the MySQL shell and create our first collection:

What is a collection in SQL terms? A table. Let’s check what MySQL does by reading the slow query log:

As we correctly guessed, it creates a table with two columns. One is called “doc” and it stores a JSON document. A second column named “_id” and is created as a virtual column from data extracted from that JSON document. _id is used as a primary key, and if we don’t specify a value, MySQL will choose a random UUID every time we write a document.

So, the basics are clear.

  • It stores everything inside a JSON column.
  • Indexes are created on virtual columns that are generated by extracting data from that JSON. Every time we add a new index, a virtual column will be generated. That means that under the hood, an alter table will run adding the column and the corresponding index.

Let’s run a getCollections that would be similar to “SHOW TABLES” in the SQL world:

This is what MySQL actually runs:

This time, the query is a bit more complex. It runs a query on information_schema.tables joining it, with information_schema.columns searching for tables that have “doc” and “_id” columns.

Inserting and reading documents

I am going to start adding data to our collection. Let’s add our first document:

In the background, MySQL inserts a JSON object and auto-assign a primary key value.

Ok, this is supposed to be schemaless. So let’s add someone else using different fields:

Same as before, MySQL just writes another JSON object (with different fields):

Now we are going to read the data we have just inserted. First, we are going to find all documents stored in the collection:

MySQL translates to a simple:

And this is how filters are transformed:

It uses a SELECT with the WHERE clause on data extracted from the JSON object.

Updating documents

Thrall decided that he doesn’t want to belong to the Horde anymore. He wants to join the Alliance. We need to update the document:

MySQL runs an UPDATE, again using a WHERE clause on the data extracted from the JSON. Then, it updates the “Faction”:

Now I want to remove my own document:

As you can already imagine, it runs a DELETE, searching for my name on the data extracted from the JSON object:


The magic that makes our MySQL work like a document-store NoSQL database is:

  • Create a simple InnoDB table with a JSON column.
  • Auto-generate the primary key with UUID values and represent it as a virtual column.
  • All searches are done by extracting data JSON_EXTRACT, and passing that info to the WHERE clause.

I would define the solution as something really clever, simple and clean. Congrats to Oracle! 🙂

Share this post

Comments (9)

  • Paul DuBois

    “I have enabled the slow query log to see what it is actually being executed when I run NoSQL queries.”

    Why would you enable the slow query log (rather than the general query log)? Every NoSQL query is slow enough to show up in the slow log? 🙂

    September 16, 2016 at 7:05 pm
    • Miguel Angel Nieto

      Hehe. To avoid all the “connect – disconnect – connect – disconnect” noise 😛

      September 16, 2016 at 7:07 pm
    • Manjot Singh

      we like to set long_query_time=0; this also works better with a lot of tools

      September 22, 2016 at 5:49 pm
  • jk


    September 18, 2016 at 4:56 am
  • Alex

    Seems that using a UUID without including caveats would negatively impact performance rather quickly .

    September 19, 2016 at 6:16 pm
  • Vasiliy Lyk'yanchikov

    Hi Miguel,
    Good post, thanks for that. I want to translate it on russian language. Please let me know if you have any objection.

    December 29, 2016 at 9:12 am
    • Miguel Angel Nieto

      No objection. Thanks 🙂

      December 29, 2016 at 10:16 am
      • Vasiliy Lyk'yanchikov

        Here is the translation of your article on russian language:

        You write:
        mysql-py> db.getCollections()
        but if previously we have created a collection “people”, then we get:
        mysql-py> db.getCollections()


        Happy new year.

        December 30, 2016 at 2:44 am
        • Vasiliy Lyk'yanchikov

          the system removes the braces 🙂
          must be “Collection:people” in braces

          December 30, 2016 at 2:49 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.