I ran an additional test - created a table in MySQL with the VARCHAR column, not JSON (as i did in MariaDB), and ran the same query. So now MySQL had to parse the JSON and then look for path there. -------------------------------------------------- MySQL [test]> select count(*) from jt2 where json_extract(j, "$.key1[1].key2")='IT'; +----------+ | count(*) | +----------+ | 20000000 | +----------+ 1 row in set (5 min 28.54 sec) --------------------------------------------------- Wow, I was surprised. 5.5 minutes against 7.6 seconds. That i guess answers two questions: how rapid is RapidJSON? why MySQL bothers with the new JSON field type? Best regards. HF 25.07.2016 14:10, Alexey Botchkov wrote:
Hi, Sergei.
I've requested your review on this, here are few additional notes.
I ran some benchmarks comparing against MySQL5.7's JSON field type. And it seems
that the difference in performance is negligible.
For instance here are results for 20M rows with rather complicated JSON:
Space occupied:
MariaDB
-rw-rw----. 1 hf hf 9520000000 Jul 25 13:08 jt1.MYD
MySQL
-rw-r-----. 1 hf hf 9440000000 Jul 25 13:28 jt1.MYD
Query time:
MariaDB [test]> select count(*) from jt1 where json_extract(j, "$.key1[1].key2")='IT'; +----------+ | count(*) | +----------+ | 20000000 | +----------+ 1 row in set (7.65 sec)
MySQL [test]> select count(*) from jt1 where json_extract(j, "$.key1[1].key2")='IT'; +----------+ | count(*) | +----------+ | 20000000 | +----------+ 1 row in set (7.56 sec)
So again i can't think now of usecases where we have to store the JSON in some special
binary representation. Only thing i can figure out is that we'd like to be able to read the MySQL database
without any specific conversion.
Best regards.
HF