2

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

enter image description here

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.

  • If there can't be more than one JSON for an object, combine the tables ObjectName and object_json. – Rick James May 3 at 16:33
  • 1
    What do you mean "search on all attributes"? Perhaps "search on any one attribute"? Or "search on several attributes at the same time"? – Rick James May 3 at 16:34
  • @RickJames : there will be multiple JSON for an object so can not merge ObjectName and object_json. Yeah, I mean search on several attributes at the same time. – j10 May 5 at 15:01
  • Multiple JSON strings for an object? Good luck! – Rick James May 5 at 16:09
  • Rick James : I mean let's say your entity is Contact then there could be multiple Contacts and each will have only one JSON with it but there could be like 10K Contacts (each having its own JSON). Each object will have only one JSON. – j10 May 6 at 6:20
2

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 .

  • Rick James : In our case where Vendors will add multiple Object Names and there will be multiple Vendors. So will it be a good idea to add so many Dynamic Indexes on the object_json table. – j10 May 5 at 15:04
  • 2
    No, don't try to add indexes for every case; pick the popular cases and the popular columns. That way, you help 'most' queries, thereby keeping from overloading the system. – Rick James May 5 at 16:08
0

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.

  • Also, this design will have lot of NULLs, should that be any issue ? – j10 May 6 at 7:49
  • A "Contact" and an "Account" are reasonably well-defined objects -- they should each be a table. The case for using JSON is more like "Product", where some products have F-stop (cameras), some have Cut (dresses), etc. Those go in JSON. Common things like Price can (should) be a column, not in JSON. – Rick James May 6 at 16:42
  • Cols 3-22 are INTs -- That sounds like a nightmare waiting to happen. Don't think that direction. – Rick James May 6 at 16:42
  • Back to the quote; let me rephrase with emphasize two words: "Searchable fields are columns; JSON for the extra fields." – Rick James May 6 at 16:45
  • Rick James : Contact and Account were for example purpose. Completely agree they will be table on their own. "Searchable fields are columns; JSON for the extra fields." --> since the Entities are added dynamically we will use a single table to store all the details. Let's say Vendor add objects like Student, Subjects how do we go about designing the DB. I am a bit lost here. Like, I was thinking to have max limits on int, varchar, etc an Entity can have - then store the searchable fields as columns where columns are named as col[i] i being the column number & store other details as JSON. – j10 May 7 at 3:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.