It is not new that we can store a JSON content in a normal table text field. This has always been the case in the past. But two key features were missing : filtering based on JSON content attributes and indexing of the JSON content. With MariaDB 10.1 CONNECT storage Engine we offer support for ...continue reading "MariaDB JSON text indexing"

Another post in the EXPLAIN FORMAT=JSON is Cool! series! In this post, we’ll discuss how the EXPLAIN FORMAT=JSON provides optimization details for 

ORDER BY

 and  

GROUP BY

 operations in conjunction with 

order_by_subqueries

 and  

group_by_subqueries

. 

EXPLAIN FORMAT=JSON

 can print details on how a subquery in

ORDER BY

 is optimized:

mysql> explain format=json select emp_no, concat(first_name, ' ', last_name) f2 from employees order by (select emp_no limit 1)G
*************************** 1. row ***************************
EXPLAIN: { …

The JSON format includes the concept of array. A JSON object cant contain an attribute of array type. We have seen that we can use the MariaDB CONNECT Storage Engine provided UDFs (user defined functions) to implement dynamic columns. Let us create a table with a text column containing a a JSON string and let ...continue reading "Using JSON’s Arrays for MariaDB Dynamic Columns"

Join Alexander Rubin, Principal Consultant, Percona as he provides an overview of MySQL 5.7 features. Wednesday, December 9, 2015 10:00AM PST (GMT -08:00).

MySQL® 5.7 is a great release that has a lot to offer, especially in the areas of development and replication. It provides many new optimizer features for developers, a much more powerful GIS function, and a high performance JSON data type – allowing for a more powerful store for semi-structured data. It also features a dramatically improved Performance Schema and Parallel and Multi-Source replication – allowing you to scale much further than ever before.

Primary …

MariaDB CONNECT storage engine handles access to JSON files through standard SQL. It comes with a set of UDFs (user defined functions) to manipulate the JSON format. This JSON content can be stored in a normal text column. This approach can be used to implement dynamic columns. The dynamic column concept was first introduced with ...continue reading "Using JSON as Dynamic Columns with MariaDB"

MySQL 5.7 is GA and has over than 150 new features. One of them is a Native JSON Data Type and JSON Functions: "Allows for efficient and flexible storage, search and manipulation of schema-less data. Enhancements include a new internal binary format, support for easy integration within SQL, and index management on the JSON Documents using generated columns".

MySQL 5.7 comes with built-in JSON support, comprising two major features:

• A native JSON data type
• A set of built-in functions to manipulate values of the JSON type

Despite being added rather recently (in MySQL 5.7.8 to be precise - one point release number before the 5.7.9 GA version), I feel the JSON support so far looks rather useful. Improvements are certainly possible, but compared to for example XML support (added in 5.1 and 5.5), the JSON feature set added to 5.7.8 is reasonably complete, coherent and standards-compliant.

(We can of course also phrase …

MySQL 5.7.9 has a new feature, that simplifies queries that deal with JSON data and makes more human-readable: inlined JSON path expressions. Now you can do following:

mysql> CREATE TABLE employees (data JSON);
Query OK, 0 rows affected (0,01 sec)

mysql> INSERT INTO employees VALUES ('{"id": 1, "name": "Jane"}');
Query OK, 1 row affected (0,00 sec)

mysql> INSERT INTO employees VALUES ('{"id": 2, "name": "Joe"}');
Query OK, 1 row affected (0,00 sec)

mysql> SELECT * FROM employees WHERE data->'$.id'= 2;

+--------------------------+
| data                     |
+--------------------------+
| {"id": 2, "name": "Joe"} |
+--------------------------+
1 row in set (0,01 sec)

Let’s take a closer look at the expression in the WHERE clause.…

I have now released version 1.2 of MyOraDump, my Oracle data extraction tool. This version has one new feature, which is transaction support for MySQL format exports which does speed up loading data a lot! Trust me, it really does! Also I have fixed a bug that did cause a crash at the end of the run, I have no idea why this didn't show up before, but there you go and now it is fixed.

MyOraDump 1.2 can be downloaded sourceforge, and as usual there is also a pdf only download if you want to read up on the tool before using it.

/Karlsson

MySQL recently added support for JSON data with MySQL 5.7.8.  It would be cool to store JSON data in TokuDB tables.

First, I had to get TokuDB running on MySQL 5.7.8.   TokuDB currently runs on Percona Server 5.6, and various flavors of MariaDB.  The only issues porting TokuDB to MySQL 5.7 were adopting the changes to various internal APIs that storage engines use.  Since I did not make any patches to the MySQL code,  some TokuDB features including clustered secondary keys and selection of various compression algorithms …