Indexing the MySQL Document Store

Indexing and the MySQL Document Store

The MySQL Document Store allows developers who do not know Structured Query Language (SQL) to use MySQL as a high efficient NoSQL document store. It has several great features but databases, NoSQL and SQL, have a problem searching through data efficiently. To help searching, you can add an index on certain fields to go directly to certain records. Traditional databases, like MySQL, allow you to add indexes and NoSQL databases, for example MongoDB, lets you add indexes. The MySQL Document Store also allows indexing.

So lets take a quick look at some simple data and then create an index.

mysql-js> db.foo.find()
[
    {
        "Name": "Carrie",
        "_id": "888881f14651e711940d0800276cdda9",
        "age": 21
    },
    {
        "Name": "Alex",
        "_id": "cc8a81f14651e711940d0800276cdda9",
        "age": 24
    },
    {
        "Last": "Stokes",
        "Name": "Dave",
        "_id": "davestokes"
    }
]
3 documents in set (0.01 sec)

mysql-js> db.foo.createIndex("ageidx").field("age","INTEGER", false).execute()
Query OK (0.01 sec)

The _id field was already indexed by default and I chose the age key for a new index. By the way you can crate UNIQUE and NON UNIQUE indexes. The arguments for the createIndex function are as follows. The first is the key in the JSON data to index. Second comes the index data type and age is an integer. And the third specifies if NOT NULL is supported and setting it to false means the column can contain NULL. BTW note that the last record has no age key which would be noted as a null; so if some of your records do not have the key to be indexed you should have this set to false.

So What Happened?

So lets take a look at what happened behind the scenes, using SQL.

mysql> DESC foo;
+------------------------------------------------+-------------+------+-----+---------+-------------------+
| Field                                          | Type        | Null | Key | Default | Extra             |
+------------------------------------------------+-------------+------+-----+---------+-------------------+
| doc                                            | json        | YES  |     | NULL    |                   |
| _id                                            | varchar(32) | NO   | PRI | NULL    | STORED GENERATED  |
| $ix_i_F177B50B40803DD7D3962E25071AC5CAA3D1139C | int(11)     | YES  | MUL | NULL    | VIRTUAL GENERATED |
+------------------------------------------------+-------------+------+-----+---------+-------------------+
3 rows in set (0.00 sec)

A VIRTUAL generated column was created. You may recall that virtual generated columns are not created until referenced, hence do not take up the space of a stored generated column. So what if $ix_i_F177B50B40803DD7D3962E25071AC5CAA3D1139C is not as human friendly as ageidx.

So lets try a search.

mysql-js> db.foo.find("age = 24")
[
    {
        "Name": "Alex",
        "_id": "cc8a81f14651e711940d0800276cdda9",
        "age": 24
    }
]
1 document in set (0.00 sec)

Sadly there is no corresponding function to the EXPLAIN SQL command. Which means there is no nice and easy way to see how much the index gains us in terms of performance.

Drop the index

But what if you want to remove that new index? Well, it is as simple as creating the index in the first place.

mysql-js> db.foo.dropIndex("ageidx").execute()
Query OK (0.01 sec)

Update on MySQL Document Store and Node.JS

Opps!

I admit my JavaScript skills are rusty and dusty. Plus I am new to Node.JS. So yesterdays blog drew some very helpful comments from two of MySQL's best. Johannes Schlüter and Rui Quelhas let me know there was a better way to code the example. Much thanks to them.

Better Example

// Simple example to grap one record and print it
const mysqlx = require('@mysql/xdevapi');
const options = {
  host: 'localhost',
  port: 33060, // should be a number
  dbUser: 'root',
  dbPassword: 'Hell0Dave!'
};

mysqlx
  .getSession(options)
  .then (session => {
  var schema = session.getSchema('world_x');

//equivilent of SELECT doc FROM countryinfo where _id = 'USA'
  var coll = schema.getCollection('countryinfo');
  var query = "$._id == 'USA'";

      // Print doc 
     return Promise.all([
       coll.find(query).execute(function (doc) {
       console.log(doc);
      }),
      session.close()
     ]);
    })
    .catch(err => {
        console.log(err.message);
        console.log(err.stack);
    });

Modern Code

This cleans up the error catching and the Promise.all helps put all the heavy lifting together.

And the PHP XDevAPI is in PECL

And you can access the XDevAPI from PHP using the PECL Extension!

MySQL Document Store and Node.JS

JavaScript and MySQL

Last time I looked at using the new MySQL Shell in JavaScript mode to access documents. I promised that I would look at indexes this time but a few folks wanted to see how to use the MySQL Document Store with Javascript -- the language not the MySQL Shell mode-- specifically with the Node.JS connector. And they ask for a very simple example. So Indexes in the future, Node.JS now.

So this is a simple on using the MySQL Document Store from Node.JS.

Install

Download the Node.JS connector and then follow the very simple installation instructions. And use your OS's instictions for installing Node.JS.

The documentation on using the Node.JS Connector with the XDevAPI is not written for folks just starting with either the API or Node.JS.

Example

const mysqlx = require('@mysql/xdevapi');

mysqlx.getSession({             // Create session
  host: 'localhost',
  port: '33060',
  dbUser: 'root',
  dbPassword: 'Hell0Dave!'
}).then(function (session) {    // USE world_x
  var schema = session.getSchema('world_x');

//equivilent of SELECT doc FROM countryinfo where _id = 'USA'
  var coll = schema.getCollection('countryinfo');
  var query = "$._id == 'USA'";

      // Print doc 
  coll.find(query).execute(function (doc) {
    console.log(doc);
  })
  .catch(function (err) {
    console.log(err.message);
    console.log(err.stack);
  });
  session.close();
}).catch(function (err) {
  console.log(err.message);
  console.log(err.stack);
});

Output

node countryinfo.js
{ GNP: 8510700,
  _id: 'USA',
  Name: 'United States',
  IndepYear: 1776,
  geography: 
   { Region: 'North America',
     Continent: 'North America',
     SurfaceArea: 9363520 },
  government: 
   { HeadOfState: 'George W. Bush',
     GovernmentForm: 'Federal Republic' },
  demographics: { Population: 278357000, LifeExpectancy: 77.0999984741211 } }

Wrap-up

So that is a simple of an example that I could think of. I plan to explore more of using the XDevAPI (and fingers crossed the PHP version appears soon) and let me know if there are anything you want to see. And I will get to indexes while I am in Glasgow next week.

MySQL Document Store: Getting Started

MySQL's new NoSQL database is quite a big change for all used to using a SQL speaking relational database. The Document Store makes use of the new MySQL Shell, the new X Protocol, and the new X Devapi to provide a NoSQL document storage service. This provides a way for developers to have a schema-less, NoSQL data store to build services and applications is a quick fashion without having to design a schema, normalize data, develop queries in SQL, or any of the rest of the overhead needed to create a relational databases. Behind the scenes and invisible to the developer is that the schema-less data is stores in a JSON type column in a MySQL instance.

Please note that I am using JavaScript Mode (instead of SQL or Python) for these examples. No special reason.

First Step

The new MySQL Shell is a separate install from the server software. Once installed it will provide the ability to use MySQL as a document store.

The db object is how we can communicate to the server and the world_x data. Now we can create a simple collection.

Whoa! Something is wrong, isn't it?

The Document Store adds an _id column automatically. You can specify your own _id but be warned it is defined by default as an UNIQUE index, so no duplicates. The InnoDB storage engine desires a index and will pick a column to index if you do not specify one (usually not a great choice in a column BTW). But JSON columns can not be directly indexed. But do not worry about the details but do note that the _id column is there.

Also notice the order of the data returned. The MySQL JSON data type organizes the data in a special binary format for faster look ups and the side effect is the alphabetizing of the keys in the key/value pairs.

Filters

Filters can be added to find specific records and to return just the desired information (default is like a SELECT *).

You can bin values to variables to separate the data from the condition.

Modify Data

If you are used to UPDATE set x=y WHERE z=n then the next part may look a little odd.

You can add more than simple key/value pairs. Here is an example of adding an array.

And of course you need the compliment to set, unset.

We can also add to or remove items for arrays within the data. The $ is short hand for the 'current document'.

And of course db.foobar.remove() would delete all the records in the collection.

Next Time: Indexes and Documents

This time we skimmed the surface of the MySQL Document Store but next time we will look at getting some extra performance by using indexes.