{"id":48046,"date":"2020-03-30T16:49:04","date_gmt":"2020-03-30T14:49:04","guid":{"rendered":"https:\/\/mamchenkov.net\/wordpress\/?p=48046"},"modified":"2020-03-30T16:49:04","modified_gmt":"2020-03-30T14:49:04","slug":"mysql-json-indexing-and-generated-columns","status":"publish","type":"post","link":"https:\/\/mamchenkov.net\/wordpress\/2020\/03\/30\/mysql-json-indexing-and-generated-columns\/","title":{"rendered":"MySQL, JSON, indexing and generated columns"},"content":{"rendered":"\n

For quite some time now I wanted to play around with the recently added JSON type in MySQL<\/a>.\u00a0 Finally, I have a project where MySQL version is high enough to support it, and the requirements are such that this choice makes sense.<\/p>\n

The first impression was great – JSON type is basically LONGTEXT type with a bunch of added functionality to manipulate JSON data.\u00a0 It took no time to setup tables and necessary queries to work with it.<\/p>\n

The second iteration though raised a few questions.\u00a0 Large tables, with complex JSON structures were rather slow in some of the more complex queries.\u00a0 The first solution to look at was obviously indexes.\u00a0 Turns out, MySQL does not support indexing of the JSON fields. Bummer.<\/p>\n

But there is a rather elegant work around.\u00a0 It involves another recently added feature, of which I haven’t heard about until today – GENERATED columns<\/a>.\u00a0 Think of table views, but on the column level, not table level.\u00a0 And generated columns can be indexed.<\/p>\n

In fact, there’s a whole lot that you can do with GENERATED columns in general, and JSON data in particular.\u00a0 This blog post – “MySQL for JSON: Generated Columns and Indexing<\/a>” – provides a great starting point with examples and explanations, including a scenario with the primary key of the table being a generated column, with the data from the JSON-typed column.<\/p>\n

Awesomeness!<\/p>\n\n","protected":false},"excerpt":{"rendered":"\n

For quite some time now I wanted to play around with the recently added JSON type in MySQL.\u00a0 Finally, I have a project where MySQL version is high enough to support it, and the requirements are such that this choice makes sense. The first impression was great – JSON type is basically LONGTEXT type with … Continue reading MySQL, JSON, indexing and generated columns<\/span><\/a><\/p>\n\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"_links_to":"","_links_to_target":""},"categories":[1,18,133,62,1334],"tags":[1559,3306,3066,1057,1330],"keyring_services":[],"class_list":["post-48046","post","type-post","status-publish","format-standard","hentry","category-general","category-programming","category-sysadmin","category-technology","category-web-work","tag-databases","tag-json","tag-mysql","tag-performance","tag-web-development"],"aioseo_notices":[],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":28508,"url":"https:\/\/mamchenkov.net\/wordpress\/2018\/04\/20\/mysql-8-0-release\/","url_meta":{"origin":48046,"position":0},"title":"MySQL 8.0 release","author":"Leonid Mamchenkov","date":"April 20, 2018","format":false,"excerpt":"MySQL 8.0 has been released and it brings the following new features, enhancements, and more: SQL\u00a0Window functions, Common Table Expressions, NOWAIT and SKIP LOCKED, Descending Indexes, Grouping, Regular Expressions, Character Sets, Cost Model, and Histograms. JSON\u00a0Extended syntax, new functions, improved sorting, and partial updates. With JSON table functions you can\u2026","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":28653,"url":"https:\/\/mamchenkov.net\/wordpress\/2018\/06\/30\/php-jsonq-a-simple-elegant-php-package-to-query-over-any-type-of-json-data\/","url_meta":{"origin":48046,"position":1},"title":"php-jsonq – a simple, elegant PHP package to query over any type of JSON data","author":"Leonid Mamchenkov","date":"June 30, 2018","format":false,"excerpt":"php-jsonq provides an easy, yet powerful way to build queries for any JSON data (or PHP data structures for that matter, which are a step away).\u00a0 This has a variety of useful applications - data migration, API response filtering, complex configurations manipulation, and so on, and so forth.","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":27408,"url":"https:\/\/mamchenkov.net\/wordpress\/2017\/03\/11\/validating-json-against-schema-in-php\/","url_meta":{"origin":48046,"position":2},"title":"Validating JSON against schema in PHP","author":"Leonid Mamchenkov","date":"March 11, 2017","format":false,"excerpt":"GitHub was rather slow yesterday, which affected the speed of installing composer dependencies (since most of them are hosted on GitHub anyway). \u00a0Staring at a slowly scrolling list of installed dependencies, I noticed something interesting. Of course, I've heard of the seld\/jsonlint before. \u00a0It's a port of zaach\/jsonlint JavaScript tool\u2026","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":22148,"url":"https:\/\/mamchenkov.net\/wordpress\/2014\/07\/08\/json-resume-a-community-driven-open-source-initiative-to-create-a-json-based-standard-for-resumes\/","url_meta":{"origin":48046,"position":3},"title":"Json R\u00e9sum\u00e9 – a community driven open source initiative to create a JSON based standard for r\u00e9sum\u00e9s","author":"Leonid Mamchenkov","date":"July 8, 2014","format":"link","excerpt":"Json R\u00e9sum\u00e9 - a community driven open source initiative to create a JSON based standard for r\u00e9sum\u00e9s. It'd be awesome to see LinkedIn integration with this.","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":27610,"url":"https:\/\/mamchenkov.net\/wordpress\/2017\/05\/23\/announcing-json-feed\/","url_meta":{"origin":48046,"position":4},"title":"Announcing JSON Feed","author":"Leonid Mamchenkov","date":"May 23, 2017","format":false,"excerpt":"Straight from the JSON Feed homepage: We \u2014\u00a0Manton Reece and Brent Simmons \u2014\u00a0have noticed that JSON has become the developers\u2019 choice for APIs, and that developers will often go out of their way to avoid XML. JSON is simpler to read and write, and it\u2019s less prone to bugs. So\u2026","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":28333,"url":"https:\/\/mamchenkov.net\/wordpress\/2018\/01\/16\/json-server\/","url_meta":{"origin":48046,"position":5},"title":"JSON Server","author":"Leonid Mamchenkov","date":"January 16, 2018","format":false,"excerpt":"JSON Server is a handy tool for anybody working with or developing the REST\/JSON APIs.\u00a0 It's a ready-made \"fake\" API server, that is super easy to setup with your end points and your data.\u00a0 And the best part - it's not just read-only.\u00a0 You can work with POST, PUT, PATCH,\u2026","rel":"","context":"In "All"","block_context":{"text":"All","link":"https:\/\/mamchenkov.net\/wordpress\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/posts\/48046","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/comments?post=48046"}],"version-history":[{"count":0,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/posts\/48046\/revisions"}],"wp:attachment":[{"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/media?parent=48046"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/categories?post=48046"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/tags?post=48046"},{"taxonomy":"keyring_services","embeddable":true,"href":"https:\/\/mamchenkov.net\/wordpress\/wp-json\/wp\/v2\/keyring_services?post=48046"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}