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.

6
  • If there can't be more than one JSON for an object, combine the tables ObjectName and object_json.
    – Rick James
    May 3, 2019 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, 2019 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, 2019 at 15:01
  • Multiple JSON strings for an object? Good luck!
    – Rick James
    May 5, 2019 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, 2019 at 6:20

3 Answers 3

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 .

2
  • 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, 2019 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, 2019 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.

9
  • Also, this design will have lot of NULLs, should that be any issue ?
    – j10
    May 6, 2019 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, 2019 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, 2019 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, 2019 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, 2019 at 3:15
-1

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

2
  • If you have any affiliation with the product you have started to recommend frequently, you should disclose it.
    – mustaccio
    Mar 2 at 15:29
  • Sorry about that, I am affiliated but did not link directly, My answer to the question I feel is a better solution than rolling you own EAV, Been there done that not fun, error prone.
    – eric
    Mar 2 at 20:29

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.