maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

Hello Igor,

A followup to yesterday's discussion:

== Short ==
MySQL-8 does store JSON_TABLE's rows into a temporary table. It doesn't seem to
allow any "caching", neither when JSON_TABLE's argument depends on some table,
nor when it is independent.

== Long ==

First, construct the example you've described:

create table t1 (a int, js json);

insert into t1 values
(1, '[1,2,3,4,5,6,7,8,9,10]');
insert into t1 select * from t1;
insert into t1 select * from t1;
select count(*) from t1;
4

create table t2 (a int, key(a));
insert into t2 values (0),(0),(0),(1),(1),(1),(2),(2),(2);
analyze table t2;

explain 
select * 
from 
  t1 join t2 on t2.a=t1.a,
  json_table(t1.js,
             '$[*]' COLUMNS(a INT PATH '$')
             ) as jt;

+----+-------------+-------+------------+--------+---------------+---------+---------+---------+------+----------+---------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref     | rows | filtered | Extra                                       |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------+------+----------+---------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL    |    4 |   100.00 | Using where                                 |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | j8.t1.a |    1 |   100.00 | Using index                                 |
|  1 | SIMPLE      | jt    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL    |    2 |   100.00 | Table function: json_table; Using temporary |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------+------+----------+---------------------------------------------+

Running the SELECT produces 120 rows in output ( 4 rows in table t1, t2 has 3
matching rows, JSON_TABLE produces 10 matches)

In debugger one can see that:
- Table_function::empty_table() is called 12 times.
- Table_function::write_row() is called 120 times.

There's no caching.

== Independent table ==

set optimizer_switch='hash_join=off,block_nested_loop=off';
explain
select *
from
  t1, json_table('[1,2,3,4,5,6,7,8,9,10]',
             '$[*]' COLUMNS(a INT PATH '$')
             ) as jt;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                    |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL                                                     |
|  1 | SIMPLE      | jt    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Table function: json_table; Using temporary; Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------------+

Running the select, I get 40 output rows.
I see 4 calls to Table_function::empty_table and 40 calls to
Table_function::write_row().


BR
 Sergei
-- 
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net