maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

Hi Varun,

On Sat, Jun 17, 2017 at 05:35:41PM +0530, Varun wrote:
> revision-id: ee73b331d97d33ae7aa1403a4ac7312f2d261754 (mariadb-10.1.20-323-gee73b33)
> parent(s): 056bab0880544d91ea67d18fe8db65b4f6625482
> author: Varun Gupta
> committer: Varun Gupta
> timestamp: 2017-06-17 17:34:07 +0530
> message:
> 
> MDEV-8840: ANALYZE FORMAT=JSON produces wrong data with BKA
> 
> The issue was that r_loops, r_rows and r_filtered in ANALYZE FORMAT= JSON were not
> calculated for the table on which we were performing the MRR scan in the BKA join
> Fixed this by adding respective counter in the JOIN_CACHE_MRR::open and JOIN_CACHE::next
> 

So I apply the patch and run this example (the dataset is from
explain_json.test, also pasted below):

MariaDB [j5]> analyze format=json select * from t3,t4 where t3.a=t4.a and (t4.c+1 < t3.b+1)\G
*************************** 1. row ***************************
ANALYZE: {
  "query_block": {
    "select_id": 1,
    "r_loops": 1,
    "r_total_time_ms": 0.8859,
    "table": {
      "table_name": "t3",
      "access_type": "ALL",
      "r_loops": 1,
      "rows": 10,
      "r_rows": 10,
      "r_total_time_ms": 0.0202,
      "filtered": 100,
      "r_filtered": 100,
      "attached_condition": "t3.a is not null"
    },
    "block-nl-join": {
      "table": {
        "table_name": "t4",
        "access_type": "ref",
        "possible_keys": ["a"],
        "key": "a",
        "key_length": "5",
        "used_key_parts": ["a"],
        "ref": ["j5.t3.a"],
        "r_loops": 1,
        "rows": 1,
        "r_rows": 11,
        "r_total_time_ms": 0.0379,
        "filtered": 100,
        "r_filtered": 90.909
      },
      "buffer_type": "flat",
      "buffer_size": "256Kb",
      "join_type": "BKA",
      "mrr_type": "Rowid-ordered scan",
      "attached_condition": "t4.c + 1 < t3.b + 1",
      "r_filtered": 0
    }
  }
}

I think, these value are wrong:
>        "r_rows": 11,
>        "r_filtered": 90.909

r_rows is actually 10,  r_filtered=100 (no attached condition)
I guess wrong r_filtered is caused by r_rows being wrong.


## Dataset:

set storage_engine=myisam;
set default_storage_engine=myisam;
create table t1(a int);
insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
create table t2(a int);
insert into t2 select A.a + B.a* 10 + C.a * 100 from t1 A, t1 B, t1 C;
create table t3(a int, b int);
insert into t3 select a,a from t1;
create table t4(a int, b int, c int, filler char(100), key (a,b));
insert into t4 select a,a,a, 'filler-data' from t2;
set optimizer_switch='mrr=on';
set join_cache_level=6;


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