> For the complete documentation index, see [llms.txt](https://eric-zhang-seattle.gitbook.io/mess-around/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://eric-zhang-seattle.gitbook.io/mess-around/non-traditional-db/mysql_keyvalue.md).

# MySQL based key value

* [MySQL key to JSON value](#mysql-key-to-json-value)
  * [Traditional SQL](#traditional-sql)
  * [Attribute column + Json](#attribute-column--json)
  * [Json alone](#json-alone)
* [Reference](#reference)
  * [TODO](#todo)

## MySQL key to JSON value

### Traditional SQL

![](https://1010073591-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-Mk8dv8Mfudl_6ziUzDf%2Fuploads%2Fgit-blob-ceaaacea99a5ea69a666446bb92d5e21f2edda0e%2Fmysql_keyvalue_ifSQL.png?alt=media)

### Attribute column + Json

![](https://1010073591-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-Mk8dv8Mfudl_6ziUzDf%2Fuploads%2Fgit-blob-58d25a4a6c790680d67049307d2240e8c624572a%2Fmysql_keyvalue_naive.png?alt=media)

* Attributes:
  * Here added\_id is the primary key, it makes MySQL write the cells linearly on disk.
  * row\_key is nothing but the entity id say trip\_uuid in trip database.
  * column\_name hold the attribute / key of json data.
  * ref\_key is the version identifier for the particular state of the json data.
  * body actual json data
  * created\_at the time when the row was inserted in the table.
* Index: There is a unique index on row\_key , column\_name & ref\_key
* Pros: it’s a very generic design. We don’t need multiple index any more. If the data store is sharded also, any query just need to go to a single shard as decided by the row\_key & it will get the required data.
* Cons: Here we are using more memory as we might end up storing the same json data along with different required json attributes in the table.

### Json alone

* We will have a table with same row\_key , body , ref\_key , created\_at but we don’t have any column to store attributes of json. Rather, we create different dedicated table for required index. So consider our json is like following:

```json
{
  'city': 'Bangalore', 
  'start_time': '2018-04-01 01:00:23', 
  'driver_id': 'jku6tr56', 
  'passenger_id': 'u12weoe'
}
```

* Index: Now we want to have index on city + driver\_id , passenger. So we create 2 tables. One with the columns — city , driver\_id , entity\_id & another table with columns — passenger\_id , entity\_id. Here entity\_id actually points to the row\_key of the actual domain model.
* Pros: For each index, we have a table, so crating a new index is easy, deleting an existing index is easy, just remove the index table altogether. No index cluttering.
* Cons: Searching might be a little pain though, search different index tables, aggregate them in the application code. Although, in both of the above approaches, application code has to handle a lot in terms of how data is parsed and manipulated in order to prepare the data for processing.

## Reference

* <https://kousiknath.medium.com/mysql-key-value-store-a-schema-less-approach-6d243a3cee5b>

### TODO

* <http://learn.lianglianglee.com/%E6%96%87%E7%AB%A0/%E7%BE%8E%E5%9B%A2%E4%B8%87%E4%BA%BF%E7%BA%A7%20KV%20%E5%AD%98%E5%82%A8%E6%9E%B6%E6%9E%84%E4%B8%8E%E5%AE%9E%E8%B7%B5.md>