Hi Sergey,
On Sat, Feb 13, 2021 at 5:52 PM Sergey Petrunia <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 > > >
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
