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
