MySQL Document Store building Index Search

MySQL Document Store Building Index Search

This is a tutorial to go through setting Up MySQL 8.0 Document Store, Importing World_x Sample database and building GENERATED COLUMN with Index and lastly to show the QUERY PLAN using collection.find() that uses the INDEX built.

INSTALLATION Pre-requisite
1. MySQL 8.0.11+
2. MySQL Shell 8.0.11+
3. Download  Sample Database "world_x database" from https://dev.mysql.com/doc/index-other.html




MySQL Database Initialization  
Using --initialize-insecure for this tutorial which means it initializes a database with empty password for root@localhost user.
Assuming we have 'mysql', 'mysqld' setup properly.

mysqld --initialize-insecure --datadir=<data directory>


Create my.cnf  (example E:/temp/data1/my.cnf)













Startup MySQL Server
 

Connecting to MySQL Server
Using "mysql" client, connect to Server "3306"
# mysql -uroot -h127.0.0.1 -P3306




Import world_x database to MySQL Server
Assuming we have downloaded the 'world_x database' and unzipped.

Using "source  <the world_x SQL file from the download>"







 Showing the database and Tables
Command> show databases;
Command> show tables;


 Listing the content of the Document (countryinfo) using SQL

COMMAND> select count(*) from countryinfo;
COMMAND> select * from countryinfo limit 2;


 The Table Structure of the countryinfo is as follows



Create GENERATED COLUMN myname referring to the Document "$.Name"
COMMAND> alter table world_x.countryinfo add column myname varchar(100) generated always as (doc->>'$.Name');



Create an INDEX on the GENERATED COLUMN
COMMAND> use world_x;
COMMAND> create index countryinfo_myname on  countryinfo (myname);


Turn On GENERAL LOG for MySQL Server
COMMAND> set global general_log=true;




Using MySQL Shell to connect to the MySQL Server (3306) and Retrieve the Collection 'countryinfo'

1. CONNECTING to defaultt portx (X-Protocol) 33060 for 3306 MySQL Server

 

2. Switch CURRENT db to world_x
MySQL SHELL Command> \use world_x



3. List COLLECTIONS
The "db" variables is defined as CURRENT Schema.
MySQL SHELL Command> db.getCollections()
 

4. Retrieve Document from COLLECTION 'countryinfo' [Only showing 1 documents]
MySQL SHELL Command> db.countryinfo.find().limit(1)



4. Retrieve Document with CRITERIA " Name='Aruba' "
MySQL SHELL Command> db.countryinfo.find(" $.Name='Aruba' ")


5. Check the GENERAL Log on MySQL Server

GENERAL LOG : Under the DataDir folder, there is a <hostname>.log
The SQL statements were logged while MySQL Shell was executing collecction retrieval.



6. Explain the Query Plan due to the db.countryinfo.find( " $.Name='Acruba' ")

SQL COMMAND> explain SELECT doc FROM `world_x`.`countryinfo` WHERE (JSON_EXTRACT(doc,'$.Name') = 'Aruba')




The COLLECTION Retrieval "find" statement is able to take the index created from the GENERATED COLUMN.

VISUALLY, we can also see QUERY PLAN in MySQL WORKBENCH as follows