MySQL

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!

Calculating distance using MySQL

“Calculating distance using MySQL” is a very useful blog post for everyone who works with geographical location data and MySQL. It shows a simple example of how to calculate the distance between two coordinates on a sphere (Earth in particular) within the MySQL itself.

SELECT ST_Distance_Sphere(
    point(-87.6770458, 41.9631174),
    point(-73.9898293, 40.7628267)
);

The above will return 1148978.6738241839, which is the distance between the two points in meters.

This functionality is available since MySQL 5.7. Have a look at the documentation of the spacial convenience functions.

MariaDB has similar functionality, but with a slightly different function names. Use ST_DISTANCE() instead of ST_Distance_Sphere(). Have a look at this blog post for more details.

UUIDs in MySQL are really not random

Jouke Waleson points out to an interesting fact about UUIDs in MySQL, which you might have missed in the documentation:

Warning: Although UUID() values are intended to be unique, they are not necessarily unguessable or unpredictable. If unpredictability is required, UUID values should be generated some other way.

Make a note!

MySQL High Availability at GitHub

Shlomi Noach, GitHub’s Senior Infrastructure Engineer, shares some details on both the current and future high availability setup of MySQL databases at GitHub.

This is probably way too far out for most people using MySQL for their web applications. But it does highlight the technical complexity of running high load web applications, and how some of the issues can be solved or worked around.

Pretty fascinating stuff there …

mycli – MySQL command line client with auto-completion

mycli is a MySQL / MariaDB / Pecona database command line client with syntax highlighting and auto-completion. Have a look at some of the screenshots and documentation for features.

Found via this StackOverflow discussion.