maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

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