Skip to content
  • Sujatha Sivakumar's avatar
    514a13fc
    Bug#22510353: UNNECESSARY USING TEMPORARY FOR UPDATE · 514a13fc
    Sujatha Sivakumar authored
    Problem:
    =======
    when the binlog_row_image is FULL, we set read_set and
    write_set all before update. This leads to setting
    used_key_is_modified true, which lead to mysql_update use
    temporary at last. Actually there is no need to use
    temporary in most cases. The root cause is that we set
    read_set and write_set too early.
    
    Analysis:
    =========
    As reported in problem description table's read_set and
    write_set are set earlier.
    'mark_columns_per_binlog_row_image' is the function call
    which sets these read and write sets. But this is called
    at an earlier stage during execution and in the case of
    binlog_row_image=FULL it will set all the bits of read and
    write sets. This will make the update query to think that
    a key is being modified by the existing update query. Even
    though it is not modifying the key field. This will force
    the update query to use a temporary table even though it may
    not be required. The same problem exists even in the case of
    binlog_row_image=NOBLOB. This problem is specific to single
    table updates.
    
    This issue is not present in the case of multi table update.
    As in the multi table update case, the check for 'used key
    is modified or not' is done before marking
    read_set/write_set as per binlog_row_image. Hence binary log
    specific bits are not influencing query to use temporary
    table.
    
    Fix:
    ===
    Implemented single table update to follow the same mechanism
    that multi table update follows. i.e Mark the columns in
    table's read_set/write_set as the binlog_row_image after the
    'used key is modified or not' decision is taken.
    514a13fc
    Bug#22510353: UNNECESSARY USING TEMPORARY FOR UPDATE
    Sujatha Sivakumar authored
    Problem:
    =======
    when the binlog_row_image is FULL, we set read_set and
    write_set all before update. This leads to setting
    used_key_is_modified true, which lead to mysql_update use
    temporary at last. Actually there is no need to use
    temporary in most cases. The root cause is that we set
    read_set and write_set too early.
    
    Analysis:
    =========
    As reported in problem description table's read_set and
    write_set are set earlier.
    'mark_columns_per_binlog_row_image' is the function call
    which sets these read and write sets. But this is called
    at an earlier stage during execution and in the case of
    binlog_row_image=FULL it will set all the bits of read and
    write sets. This will make the update query to think that
    a key is being modified by the existing update query. Even
    though it is not modifying the key field. This will force
    the update query to use a temporary table even though it may
    not be required. The same problem exists even in the case of
    binlog_row_image=NOBLOB. This problem is specific to single
    table updates.
    
    This issue is not present in the case of multi table update.
    As in the multi table update case, the check for 'used key
    is modified or not' is done before marking
    read_set/write_set as per binlog_row_image. Hence binary log
    specific bits are not influencing query to use temporary
    table.
    
    Fix:
    ===
    Implemented single table update to follow the same mechanism
    that multi table update follows. i.e Mark the columns in
    table's read_set/write_set as the binlog_row_image after the
    'used key is modified or not' decision is taken.
Loading