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
_______________________________________________
Mailing list: https://launchpad.net/~maria-developers
Post to : maria-developers@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-developers
More help : https://help.launchpad.net/ListHelp
