Month: March 2020

MySQL, JSON, indexing and generated columns

For quite some time now I wanted to play around with the recently added JSON type in MySQL.  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 a bunch of added functionality to manipulate JSON data.  It took no time to setup tables and necessary queries to work with it.

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

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

In fact, there’s a whole lot that you can do with GENERATED columns in general, and JSON data in particular.  This blog post – “MySQL for JSON: Generated Columns and Indexing” – 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.

Awesomeness!

How I asked EVERY countrys embassy for flags [119 packages]

Here’s one of the best YouTube videos I’ve seen in a very long time.  In fact, I love is so much, I’ve decided to take in effort beyond just sharing it.  Watch it first, and then I’ll share a list of reasons why I love it.

So, here we go with my own personal reasons of why I love it.  In no particular order, as always.

  1. It’s not about COVID-19/Coronavirus.
  2. It’s positive, nice and kind.
  3. It’s funny.  Not like stupid funny, or stand-up funny.  But it still is.
  4. It has substance.  It’s not a video for the sake of video.  It’s a video about a rather long and complex project, which took a lot of effort.
  5. It bridges the real world and technology, and shows how one helps the other.
  6. It’s very personal, yet very global.
  7. It demonstrates one of my strong believes, that even simply asking for something, even from total strangers, has merit.  Not everyone will respond.  Not everyone will respond in the way you want them to.  But overall you’ll get more than you thought you would.
  8. Cyprus made it high up the recognition list.
  9. Political neutrality.   A lot of the countries mentioned in this video, have a long, complex, and often violent relationship with each other.  Yet, that doesn’t matter for this particular project.
  10. Free stuff.  Who doesn’t like free stuff.
  11. Production of the video.  It’s not over the top professional production, yet it’s not an amateur talking head, filmed on the mobile phone in the car.
  12. Maps.  I love maps, and especially checking them off on the global map.  Bonus points for more than one color for checked out colors.
  13. Flags.  Even though I don’t collect or study flags, I love flags and their designs.
  14. It’s work and family friendly.  Easily shareable with everyone I know.
  15. Bonus point: it just makes me feel good about the world.