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)