Hi Sergey, On Sat, Feb 13, 2021 at 5:52 PM Sergey Petrunia <sergey@mariadb.com> wrote:
Hi Varun,
(This email is not the complete input, and not the most important part of the input. More to follow. I think it's better to split input into multiple smaller pieces, it's easier to track, and you get to see the first portions of the input sooner)
EXPLAIN EXTENDED SELECT t3.b, t2.a, t1.b, t1.a FROM t1,t2,t3 WHERE t1.b=t3.b ORDER BY t1.b DESC, t2.a DESC LIMIT 3;
+------+-------------+-------------+------+---------------+------+---------+------+------+----------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------------+------+---------------+------+---------+------+------+----------+------------------------------------+ | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | | | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using join buffer (flat, BNL join) | | 1 | SIMPLE | <sort-nest> | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using filesort | | 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL | NULL | 100 | 5.00 | Using where |
+------+-------------+-------------+------+---------------+------+---------+------+------+----------+------------------------------------+
So, here <sort-nest> includes t2 and t1, and then t3 is joined with it.
However in EXPLAIN FORMAT=JSON output they all look like peers:
{ "query_block": { "select_id": 1, "table": { "table_name": "t2", "access_type": "ALL", "rows": 10, "filtered": 100 }, "block-nl-join": { "table": { "table_name": "t1", "access_type": "ALL", "rows": 20, "filtered": 100 }, "buffer_type": "flat", "buffer_size": "65", "join_type": "BNL" }, "read_sorted_file": { "filesort": { "sort_key": "`sort-nest`.b desc, `sort-nest`.a desc", "table": { "table_name": "<sort-nest>", "access_type": "ALL", "rows": 3, "filtered": 100 } } }, "table": { "table_name": "t3", "access_type": "ALL", "rows": 100, "filtered": 5, "attached_condition": "t3.b = `sort-nest`.b" } } }
which is counter-intuitive.
Well i didn't make changes to how to display the sort-nest in EXPLAIN/ANALYZE FORMAT=JSON. But i think we can use do it in the way that inside : "table": { "table_name": "<sort-nest>", "access_type": "ALL", "rows": 3, "filtered": 100 } we put in the inner tables too as it is done with materialization.
Also, `sort-nest` is quoted. I assume it is because of the '-' in it? Maybe we should change the name to avoid the quoting?
Well i think we can have just *sortnest* in the naming.
BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net
On Sat, Feb 13, 2021 at 5:52 PM Sergey Petrunia <sergey@mariadb.com> wrote:
Hi Varun,
(This email is not the complete input, and not the most important part of the input. More to follow. I think it's better to split input into multiple smaller pieces, it's easier to track, and you get to see the first portions of the input sooner)
EXPLAIN EXTENDED SELECT t3.b, t2.a, t1.b, t1.a FROM t1,t2,t3 WHERE t1.b=t3.b ORDER BY t1.b DESC, t2.a DESC LIMIT 3;
+------+-------------+-------------+------+---------------+------+---------+------+------+----------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------------+------+---------------+------+---------+------+------+----------+------------------------------------+ | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | | | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using join buffer (flat, BNL join) | | 1 | SIMPLE | <sort-nest> | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using filesort | | 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL | NULL | 100 | 5.00 | Using where |
+------+-------------+-------------+------+---------------+------+---------+------+------+----------+------------------------------------+
So, here <sort-nest> includes t2 and t1, and then t3 is joined with it.
However in EXPLAIN FORMAT=JSON output they all look like peers:
{ "query_block": { "select_id": 1, "table": { "table_name": "t2", "access_type": "ALL", "rows": 10, "filtered": 100 }, "block-nl-join": { "table": { "table_name": "t1", "access_type": "ALL", "rows": 20, "filtered": 100 }, "buffer_type": "flat", "buffer_size": "65", "join_type": "BNL" }, "read_sorted_file": { "filesort": { "sort_key": "`sort-nest`.b desc, `sort-nest`.a desc", "table": { "table_name": "<sort-nest>", "access_type": "ALL", "rows": 3, "filtered": 100 } } }, "table": { "table_name": "t3", "access_type": "ALL", "rows": 100, "filtered": 5, "attached_condition": "t3.b = `sort-nest`.b" } } }
which is counter-intuitive.
Also, `sort-nest` is quoted. I assume it is because of the '-' in it? Maybe we should change the name to avoid the quoting?
BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net
On Sat, Feb 13, 2021 at 5:51 PM Sergey Petrunia <sergey@mariadb.com> wrote:
Hi Varun,
(This email is not the complete input, and not the most important part of the input. More to follow. I think it's better to split input into multiple smaller pieces, it's easier to track, and you get to see the first portions of the input sooner)
EXPLAIN EXTENDED SELECT t3.b, t2.a, t1.b, t1.a FROM t1,t2,t3 WHERE t1.b=t3.b ORDER BY t1.b DESC, t2.a DESC LIMIT 3;
+------+-------------+-------------+------+---------------+------+---------+------+------+----------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+-------------+-------------+------+---------------+------+---------+------+------+----------+------------------------------------+ | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | | | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using join buffer (flat, BNL join) | | 1 | SIMPLE | <sort-nest> | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using filesort | | 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL | NULL | 100 | 5.00 | Using where |
+------+-------------+-------------+------+---------------+------+---------+------+------+----------+------------------------------------+
So, here <sort-nest> includes t2 and t1, and then t3 is joined with it.
However in EXPLAIN FORMAT=JSON output they all look like peers:
{ "query_block": { "select_id": 1, "table": { "table_name": "t2", "access_type": "ALL", "rows": 10, "filtered": 100 }, "block-nl-join": { "table": { "table_name": "t1", "access_type": "ALL", "rows": 20, "filtered": 100 }, "buffer_type": "flat", "buffer_size": "65", "join_type": "BNL" }, "read_sorted_file": { "filesort": { "sort_key": "`sort-nest`.b desc, `sort-nest`.a desc", "table": { "table_name": "<sort-nest>", "access_type": "ALL", "rows": 3, "filtered": 100 } } }, "table": { "table_name": "t3", "access_type": "ALL", "rows": 100, "filtered": 5, "attached_condition": "t3.b = `sort-nest`.b" } } }
which is counter-intuitive.
Also, `sort-nest` is quoted. I assume it is because of the '-' in it? Maybe we should change the name to avoid the quoting?
BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net