Skip to content
  • Neeraj Bisht's avatar
    b90bc9b1
    Bug#16296268 : UNEXCEPTED "USING WHERE" IN "EXPLAIN UPDATE/DELETE" · b90bc9b1
    Neeraj Bisht authored
    Problem:-
    In a single-table update, when there is a constant equality predicate
    on a key.The bug reporter is expecting explain output type as const.
    
    Analysis:-
    In query like
    create table tb(id int primary key , c int); 
    
    mysql> explain update tb set c=2 where id=1\G
    id    1
    select_type    SIMPLE
    table    tb
    type    range            
    possible_keys    PRIMARY
    key    PRIMARY
    key_len    4
    ref    NULL
    rows    1                
    Extra    Using where 
    
    EXPLAIN does not print type: eq_ref/const even though we have an equality 
    predicate on a Unique key field.
    
    However, we can't  show const/eq_ref as range access is used, and 
    single-table UPDATE/DELETE "optimizer" does not even consider index 
    lookups. The proper fix for this bug would be to make single-table U/D go 
    through the normal query optimizer, but that's a complex task(for which WL#6367 
    Single code path for multi-table and single-table UPDATE(DELETE) is created).
    
    But to improve readability, we are changing value of the ref column to ref:const.
    b90bc9b1
    Bug#16296268 : UNEXCEPTED "USING WHERE" IN "EXPLAIN UPDATE/DELETE"
    Neeraj Bisht authored
    Problem:-
    In a single-table update, when there is a constant equality predicate
    on a key.The bug reporter is expecting explain output type as const.
    
    Analysis:-
    In query like
    create table tb(id int primary key , c int); 
    
    mysql> explain update tb set c=2 where id=1\G
    id    1
    select_type    SIMPLE
    table    tb
    type    range            
    possible_keys    PRIMARY
    key    PRIMARY
    key_len    4
    ref    NULL
    rows    1                
    Extra    Using where 
    
    EXPLAIN does not print type: eq_ref/const even though we have an equality 
    predicate on a Unique key field.
    
    However, we can't  show const/eq_ref as range access is used, and 
    single-table UPDATE/DELETE "optimizer" does not even consider index 
    lookups. The proper fix for this bug would be to make single-table U/D go 
    through the normal query optimizer, but that's a complex task(for which WL#6367 
    Single code path for multi-table and single-table UPDATE(DELETE) is created).
    
    But to improve readability, we are changing value of the ref column to ref:const.
Loading