Define JSON Index for Schemaless using MySQL 8.0

Question

We are using MySQL 8.0 and need to implement Schemaless Feature where we allow Vendors to create a Module(ObjectName) and add columns(int, string, currency) to it. Vendor can add objects after defining the Module.

CREATE TABLE `ObjectName` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `vendor_Id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  KEY `vendor_Id_idx` (`vendor_Id`),
  CONSTRAINT `vendor_Id` FOREIGN KEY (`vendor_Id`) REFERENCES `Vendor` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 
COLLATE=utf8mb4_0900_ai_ci

CREATE TABLE `ObjectNameColumn` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `objectname_id` int(11) DEFAULT NULL,
  `name` varchar(45) DEFAULT NULL,
  `dataType` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `objectnamecolumn_id_objectname_id_idx` (`objectname_id`),
  CONSTRAINT `objectnamecolumn_id_objectname_id` FOREIGN KEY (`objectname_id`) 
REFERENCES `ObjectName` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 
COLLATE=utf8mb4_0900_ai_ci



CREATE TABLE `object_json` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `attributes` json DEFAULT NULL,
  `objectname_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `objectname_id_objectnameidjson` (`objectname_id`),
  CONSTRAINT `objectname_id_objectnameidjson` FOREIGN KEY (`objectname_id`) 
REFERENCES `ObjectName` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=400002 DEFAULT CHARSET=utf8mb4 
    COLLATE=utf8mb4_0900_ai_ci

ObjectName :

id | name     | vendor_id
1  | Contact  |   1
2  | Account  |   1
3  | Contact  |   2
4  | Account  |   2

ObjectNameColumn :

id | objectname_id | name              | dataType
 1 |      1        | height            |  int
 2 |      1        | weight            |  int
 3 |      1        | age               |  int
 4 |      1        | name              |  string
 5 |      1        | mobile_number     |  string
 6 |      2        | annual_revenue    |  int
 7 |      2        | establised_year   |  int
 8 |      2        | numbe_of_employees|  int
 9 |      2        | address           |  string
10 |      2        | name              |  string

object_json :

    id | objectname_id | attributes
     1 | 1             |{"age": 3, "name": "jiten1", "height": 1, "weight": 2, "mobile_number": null}
     2 | 2             |{"name": "xyz", "address": null, "annual_revenue": 1, "established_year": 2, "number_of_employees": 3}

---

Now, let's say I want to look for lookup for Contacts of Vendor 1 which satisfy following criteria :

height between 200 and 400
weight between 400 and 800
age between 400 and 800

---

SELECT
   object_json.id AS id,
   attributes 
FROM
   object_json 
where
   object_json.objectname_id = 1 
   AND attributes -> " $ .height" between 200 and 400 
   AND attributes -> " $ .weight" between 400 and 800 
   AND attributes -> " $ .age" between 400 and 800

The Vendor Can do Select operations with the filters as JSON fields. When we run these queries with 100,000 records for 4 ObjectNames

Output of EXPLAIN :

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "21938.55"
    },
    "table": {
      "table_name": "object_json",
      "access_type": "ref",
      "possible_keys": [
        "objectname_id_objectnameidjson"
      ],
      "key": "objectname_id_objectnameidjson",
      "used_key_parts": [
        "objectname_id"
      ],
      "key_length": "4",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 192228,
      "rows_produced_per_join": 192228,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "2715.75",
        "eval_cost": "19222.80",
        "prefix_cost": "21938.55",
        "data_read_per_join": "5M"
      },
      "used_columns": [
        "id",
        "attributes",
        "objectname_id"
      ],
      "attached_condition": "((json_extract(`test`.`object_json`.`attributes`,'$.height') between 200 and 400) and (json_extract(`test`.`object_json`.`attributes`,'$.weight') between 400 and 800) and (json_extract(`test`.`object_json`.`attributes`,'$.age') between 400 and 800))"
    }
  }
}

We want to speed up the query using indexes. We found there is an option for Secondary Index by adding Virtual Columns and then defining an index on the Virtual Columns in case of JSON type.

1. Since Vendor can search on all attributes of an ObjectName --> how do we go about defining index in such a case

2. Also, in our case, since we don't know what kind of ObjectName(entities) the Vendor will add --> and I don't think it is a good idea to define Indexes on a table at run time, the approach does not seem to be scalable. How can we go about solving this problem when we don't know the nature of entities.

Answer 1

I recommend you collect the queries that users use. Periodically go through them to see what json 'columns' are most commonly filtered on and build indexes (with Virtual, etc).

Be sure to also look for pairs of columns that are filtered on. In this case, be sure to put the = column first. In your example, each of the filters is a 'range', so there is no benefit in building a 'composite' index.

I touch on some of this here .

Answer 2

Sorry, had to post this as Answer due to the length of the comment.

That link was very helpful. Thank you Rick. From the website "The solution uses one table for all the EAV stuff. The columns include the searchable fields plus one TEXT/BLOB. Searchable fields are declared appropriately (INT, TIMESTAMP, etc). The BLOB contains JSON-encoding of all the extra fields. "

So if I understand it correctly, can we implement the above as : let's say we have a limitation of 100 columns/fields per entity and we plan to support Text, Integer, Decimal, Date, DateTime fields. So our Entity Table will have :

1. Entity_Id
2. JSON (instead of the blob as we are using MySQL 8.0)

From Col 3 to Col 102: so we divide these Data Types into 5 groups : so :

Col 3 - Col 22 - Int

Col 23 - Col 42 - String

Col 43 - Col 62 - Date

Col62 - Col81 - Date time

Col82 - Col102 - Decimal

So let's say we have Entity Contact where searchable fields (age, name) - we will store them as :

Col 2  |  Col 3    |  Col 23
{"age": 3, "name": "jiten1", "height": 1, "weight": 2, "mobile_number": null}   | 3 |  jiten1

rest of all columns will be null.

Entity Account where searchable fields (annual_revenue, established_year, name)` will store them as :

Col 2   |  Col 3   |  Col 4   |  Col 23
{"name": "xyz", "address": null, "annual_revenue": 1, "established_year": 2, "number_of_employees": 3}   | 1  | 2   |  xyz

rest of all columns will be null.

Table Structure will be like :

Col 1(Entity_Id) |  Col 2(JSON) | Col 3 - Col 22 Int | Col 23 - Col 42 String | Col 43 - Col 62 Date | Col 62 - Col 81 Date Time | Col 82- Col 102 Decimal

Also for each Entity, we will have a mapping of attribute/field --> column number

in case of Entity Contact, we make an index on (col 3, col 23)

in case of Entity Account, we make an index on (col 3, col4, col 23)

Is our interpretation correct? We won't repeat the attribute in the JSON and in Column in case of searchable field (though in the example I have)

Also, there is a limitation of 64 Secondary Index per table. So as we grow out of space for Index, we create a similar table and then route our queries accordingly based on some condition.

Answer 3

take a look at schemafreesql. it has auto indexing and adhoc querying using a sql like language. Will store deeply nested object structures. its schemaless so no tables or schemas to manage.

it's backend is currently mysql 8 so that fits your use case. Hosted only for now self hosted on the horizon.

it does not allow for select * on an object. So to get the attributes of an object you call "showattr" function passing the object id and depth in this case depth would be Ithink 0 maybe 1. that would return the attributes of the object. now you can create a query selecting the object attributes. This assumes vendors can add additional attributes to an object since it schemales that's possible if thats not the case and you know the object attributes then showattr is not needed. Indexing is taken care of for you no limit on the number of attributes will handle all those data types.

meant to respond to entire thread this addresses the EAV structure. All the details of rolling your own EAV are handled by Schemafreesql plus in comes with sql like query language