Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7950

Item_func::type() takes 0.26% in OLTP RO

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.1(EOL)
    • 10.1.6
    • Optimizer
    • None

    Description

      Data comes from Sandy Bridge system running sysbench OLTP RO in 1 thread against 1 table.

      Call graphs:

      -   0.26%  mysqld  mysqld
         - Item_func::type() const
            + 18.31% JOIN::optimize_inner()
            + 17.94% MYSQLparse(THD*)
            + 11.70% update_ref_and_keys(THD*, st_dynamic_array*, st_join_table*, unsigned int, Item*, unsigned long long, st_select_lex*, st_sargable_param**) [clone .isra.253]
            + 10.26% build_equal_items(JOIN*, Item*, COND_EQUAL*, List<TABLE_LIST>*, bool, COND_EQUAL**, bool) [clone .constprop.262]
            + 6.81% internal_remove_eq_conds(THD*, Item*, Item::cond_result*)
            + 5.92% JOIN::prepare(Item***, TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*)
            + 5.32% join_read_const_table(st_join_table*, st_position*) [clone .isra.248]
            + 4.84% JOIN::optimize()
            + 3.57% make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*)
            + 3.01% SQL_SELECT::test_quick_select(THD*, Bitmap<64u>, unsigned long long, unsigned long long, bool, bool, bool)
            + 3.00% remove_eq_conds(THD*, Item*, Item::cond_result*)
            + 2.92% Item_func::compile(bool (Item::*)(unsigned char**), unsigned char**, Item* (Item::*)(unsigned char*), unsigned char*)
            + 2.52% Item_equal::add_const(Item*, Item*)
            + 2.01% Item_func::setup_args_and_comparator(THD*, Arg_comparator*)
            + 1.31% make_join_select(JOIN*, SQL_SELECT*, Item*)

      This is virtual method which can't be inlined. Most time spent for function call convention. Check if we can revoke it's virtual status.

      Attachments

        Issue Links

          Activity

            bar Alexander Barkov added a comment - - edited

            In this SQL script:

            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (
              id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
               k INTEGER UNSIGNED DEFAULT '0' NOT NULL,
               c CHAR(120) DEFAULT '' NOT NULL,
               pad CHAR(60) DEFAULT '' NOT NULL,
               PRIMARY KEY (id)
            ) ENGINE=MyISAM;
            INSERT INTO t1 (k, c, pad) VALUES (10, 'a', 'xxx');
            INSERT INTO t1 (k, c, pad) VALUES (11, 'a', 'xxx');
            INSERT INTO t1 (k, c, pad) VALUES (12, 'a', 'xxx');
            INSERT INTO t1 (k, c, pad) VALUES (13, 'a', 'xxx');
            INSERT INTO t1 (k, c, pad) VALUES (14, 'a', 'xxx');
            INSERT INTO t1 (k, c, pad) VALUES (15, 'a', 'xxx');
            INSERT INTO t1 (k, c, pad) VALUES (16, 'a', 'xxx');
            SELECT * FROM t1 WHERE id=13;

            Item_func::type() is called 16 times from JOIN::optimizer() and its callees during the above SELECT query:

            #0  0x00000000005b4a70 in Item_func::type() const ()
            #1  0x00000000006357ec in build_equal_items_for_cond(THD*, Item*, COND_EQUAL*, bool) ()
            #2  0x0000000000636333 in build_equal_items(JOIN*, Item*, COND_EQUAL*, List<TABLE_LIST>*, bool, COND_EQUAL**, bool) [clone .constprop.266] ()
            #3  0x0000000000636890 in JOIN::optimize_inner() ()
            #4  0x0000000000638eed in JOIN::optimize() ()
             
            #0  0x00000000005b4a70 in Item_func::type() const ()
            #1  0x0000000000635802 in build_equal_items_for_cond(THD*, Item*, COND_EQUAL*, bool) ()
            #2  0x0000000000636333 in build_equal_items(JOIN*, Item*, COND_EQUAL*, List<TABLE_LIST>*, bool, COND_EQUAL**, bool) [clone .constprop.266] ()
            #3  0x0000000000636890 in JOIN::optimize_inner() ()
            #4  0x0000000000638eed in JOIN::optimize() ()
             
            #0  0x00000000005b4a70 in Item_func::type() const ()
            #1  0x0000000000635853 in build_equal_items_for_cond(THD*, Item*, COND_EQUAL*, bool) ()
            #2  0x0000000000636333 in build_equal_items(JOIN*, Item*, COND_EQUAL*, List<TABLE_LIST>*, bool, COND_EQUAL**, bool) [clone .constprop.266] ()
            #3  0x0000000000636890 in JOIN::optimize_inner() ()
            #4  0x0000000000638eed in JOIN::optimize() ()
             
            #0  0x00000000005b4a70 in Item_func::type() const ()
            #1  0x000000000063634f in build_equal_items(JOIN*, Item*, COND_EQUAL*, List<TABLE_LIST>*, bool, COND_EQUAL**, bool) [clone .constprop.266] ()
            #2  0x0000000000636890 in JOIN::optimize_inner() ()
            #3  0x0000000000638eed in JOIN::optimize() ()
             
            #0  0x00000000005b4a70 in Item_func::type() const ()
            #1  0x0000000000636361 in build_equal_items(JOIN*, Item*, COND_EQUAL*, List<TABLE_LIST>*, bool, COND_EQUAL**, bool) [clone .constprop.266] ()
            #2  0x0000000000636890 in JOIN::optimize_inner() ()
            #3  0x0000000000638eed in JOIN::optimize() ()
             
            #0  0x00000000005b4a70 in Item_func::type() const ()
            #1  0x0000000000615b8a in propagate_cond_constants(THD*, I_List<COND_CMP>*, Item*, Item*) ()
            #2  0x00000000006368a3 in JOIN::optimize_inner() ()
            #3  0x0000000000638eed in JOIN::optimize() ()
             
            #0  0x00000000005b4a70 in Item_func::type() const ()
            #1  0x0000000000620d54 in remove_eq_conds(THD*, Item*, Item::cond_result*) ()
            #2  0x00000000006368b5 in JOIN::optimize_inner() ()
            #3  0x0000000000638eed in JOIN::optimize() ()
             
            #0  0x00000000005b4a70 in Item_func::type() const ()
            #1  0x000000000061fe5a in internal_remove_eq_conds(THD*, Item*, Item::cond_result*) ()
            #2  0x0000000000620d69 in remove_eq_conds(THD*, Item*, Item::cond_result*) ()
            #3  0x00000000006368b5 in JOIN::optimize_inner() ()
            #4  0x0000000000638eed in JOIN::optimize() ()
             
            #0  0x00000000005b4a70 in Item_func::type() const ()
            #1  0x000000000061fd4d in cond_is_datetime_is_null(Item*) ()
            #2  0x000000000061fe6d in internal_remove_eq_conds(THD*, Item*, Item::cond_result*) ()
            #3  0x0000000000620d69 in remove_eq_conds(THD*, Item*, Item::cond_result*) ()
            #4  0x00000000006368b5 in JOIN::optimize_inner() ()
            #5  0x0000000000638eed in JOIN::optimize() ()
             
            #0  0x00000000005b4a70 in Item_func::type() const ()
            #1  0x00000000006368c9 in JOIN::optimize_inner() ()
            #2  0x0000000000638eed in JOIN::optimize() ()
             
            #0  0x00000000005b4a70 in Item_func::type() const ()
            #1  0x0000000000615e97 in add_key_fields(JOIN*, key_field_t**, unsigned int*, Item*, unsigned long long, st_sargable_param**) ()
            #2  0x000000000061773a in update_ref_and_keys(THD*, st_dynamic_array*, st_join_table*, unsigned int, Item*, unsigned long long, st_select_lex*, st_sargable_param**) [clone .isra.257] ()
            #3  0x000000000053f1e7 in make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*) ()
            #4  0x0000000000636b00 in JOIN::optimize_inner() ()
            #5  0x0000000000638eed in JOIN::optimize() ()
             
            #0  0x00000000005b4a70 in Item_func::type() const ()
            #1  0x0000000000615ea9 in add_key_fields(JOIN*, key_field_t**, unsigned int*, Item*, unsigned long long, st_sargable_param**) ()
            #2  0x000000000061773a in update_ref_and_keys(THD*, st_dynamic_array*, st_join_table*, unsigned int, Item*, unsigned long long, st_select_lex*, st_sargable_param**) [clone .isra.257] ()
            #3  0x000000000053f1e7 in make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*) ()
            #4  0x0000000000636b00 in JOIN::optimize_inner() ()
            #5  0x0000000000638eed in JOIN::optimize() ()
             
            #0  0x00000000005b4a70 in Item_func::type() const ()
            #1  0x0000000000615ebf in add_key_fields(JOIN*, key_field_t**, unsigned int*, Item*, unsigned long long, st_sargable_param**) ()
            #2  0x000000000061773a in update_ref_and_keys(THD*, st_dynamic_array*, st_join_table*, unsigned int, Item*, unsigned long long, st_select_lex*, st_sargable_param**) [clone .isra.257] ()
            #3  0x000000000053f1e7 in make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*) ()
            #4  0x0000000000636b00 in JOIN::optimize_inner() ()
            #5  0x0000000000638eed in JOIN::optimize() ()
             
            #0  0x00000000005b4a70 in Item_func::type() const ()
            #1  0x0000000000620d54 in remove_eq_conds(THD*, Item*, Item::cond_result*) ()
            #2  0x000000000053facf in make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*) ()
            #3  0x0000000000636b00 in JOIN::optimize_inner() ()
            #4  0x0000000000638eed in JOIN::optimize() ()
             
            #0  0x00000000005b4a70 in Item_func::type() const ()
            #1  0x000000000061fe5a in internal_remove_eq_conds(THD*, Item*, Item::cond_result*) ()
            #2  0x0000000000620d69 in remove_eq_conds(THD*, Item*, Item::cond_result*) ()
            #3  0x000000000053facf in make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*) ()
            #4  0x0000000000636b00 in JOIN::optimize_inner() ()
            #5  0x0000000000638eed in JOIN::optimize() ()
             
            #0  0x00000000005b4a70 in Item_func::type() const ()
            #1  0x000000000061fd4d in cond_is_datetime_is_null(Item*) ()
            #2  0x000000000061fe6d in internal_remove_eq_conds(THD*, Item*, Item::cond_result*) ()
            #3  0x0000000000620d69 in remove_eq_conds(THD*, Item*, Item::cond_result*) ()
            #4  0x000000000053facf in make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*) ()
            #5  0x0000000000636b00 in JOIN::optimize_inner() ()
            #6  0x0000000000638eed in JOIN::optimize() ()

            bar Alexander Barkov added a comment - - edited In this SQL script: DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, k INTEGER UNSIGNED DEFAULT '0' NOT NULL , c CHAR (120) DEFAULT '' NOT NULL , pad CHAR (60) DEFAULT '' NOT NULL , PRIMARY KEY (id) ) ENGINE=MyISAM; INSERT INTO t1 (k, c, pad) VALUES (10, 'a' , 'xxx' ); INSERT INTO t1 (k, c, pad) VALUES (11, 'a' , 'xxx' ); INSERT INTO t1 (k, c, pad) VALUES (12, 'a' , 'xxx' ); INSERT INTO t1 (k, c, pad) VALUES (13, 'a' , 'xxx' ); INSERT INTO t1 (k, c, pad) VALUES (14, 'a' , 'xxx' ); INSERT INTO t1 (k, c, pad) VALUES (15, 'a' , 'xxx' ); INSERT INTO t1 (k, c, pad) VALUES (16, 'a' , 'xxx' ); SELECT * FROM t1 WHERE id=13; Item_func::type() is called 16 times from JOIN::optimizer() and its callees during the above SELECT query: #0 0x00000000005b4a70 in Item_func::type() const () #1 0x00000000006357ec in build_equal_items_for_cond(THD*, Item*, COND_EQUAL*, bool) () #2 0x0000000000636333 in build_equal_items(JOIN*, Item*, COND_EQUAL*, List<TABLE_LIST>*, bool, COND_EQUAL**, bool) [clone .constprop.266] () #3 0x0000000000636890 in JOIN::optimize_inner() () #4 0x0000000000638eed in JOIN::optimize() ()   #0 0x00000000005b4a70 in Item_func::type() const () #1 0x0000000000635802 in build_equal_items_for_cond(THD*, Item*, COND_EQUAL*, bool) () #2 0x0000000000636333 in build_equal_items(JOIN*, Item*, COND_EQUAL*, List<TABLE_LIST>*, bool, COND_EQUAL**, bool) [clone .constprop.266] () #3 0x0000000000636890 in JOIN::optimize_inner() () #4 0x0000000000638eed in JOIN::optimize() ()   #0 0x00000000005b4a70 in Item_func::type() const () #1 0x0000000000635853 in build_equal_items_for_cond(THD*, Item*, COND_EQUAL*, bool) () #2 0x0000000000636333 in build_equal_items(JOIN*, Item*, COND_EQUAL*, List<TABLE_LIST>*, bool, COND_EQUAL**, bool) [clone .constprop.266] () #3 0x0000000000636890 in JOIN::optimize_inner() () #4 0x0000000000638eed in JOIN::optimize() ()   #0 0x00000000005b4a70 in Item_func::type() const () #1 0x000000000063634f in build_equal_items(JOIN*, Item*, COND_EQUAL*, List<TABLE_LIST>*, bool, COND_EQUAL**, bool) [clone .constprop.266] () #2 0x0000000000636890 in JOIN::optimize_inner() () #3 0x0000000000638eed in JOIN::optimize() ()   #0 0x00000000005b4a70 in Item_func::type() const () #1 0x0000000000636361 in build_equal_items(JOIN*, Item*, COND_EQUAL*, List<TABLE_LIST>*, bool, COND_EQUAL**, bool) [clone .constprop.266] () #2 0x0000000000636890 in JOIN::optimize_inner() () #3 0x0000000000638eed in JOIN::optimize() ()   #0 0x00000000005b4a70 in Item_func::type() const () #1 0x0000000000615b8a in propagate_cond_constants(THD*, I_List<COND_CMP>*, Item*, Item*) () #2 0x00000000006368a3 in JOIN::optimize_inner() () #3 0x0000000000638eed in JOIN::optimize() ()   #0 0x00000000005b4a70 in Item_func::type() const () #1 0x0000000000620d54 in remove_eq_conds(THD*, Item*, Item::cond_result*) () #2 0x00000000006368b5 in JOIN::optimize_inner() () #3 0x0000000000638eed in JOIN::optimize() ()   #0 0x00000000005b4a70 in Item_func::type() const () #1 0x000000000061fe5a in internal_remove_eq_conds(THD*, Item*, Item::cond_result*) () #2 0x0000000000620d69 in remove_eq_conds(THD*, Item*, Item::cond_result*) () #3 0x00000000006368b5 in JOIN::optimize_inner() () #4 0x0000000000638eed in JOIN::optimize() ()   #0 0x00000000005b4a70 in Item_func::type() const () #1 0x000000000061fd4d in cond_is_datetime_is_null(Item*) () #2 0x000000000061fe6d in internal_remove_eq_conds(THD*, Item*, Item::cond_result*) () #3 0x0000000000620d69 in remove_eq_conds(THD*, Item*, Item::cond_result*) () #4 0x00000000006368b5 in JOIN::optimize_inner() () #5 0x0000000000638eed in JOIN::optimize() ()   #0 0x00000000005b4a70 in Item_func::type() const () #1 0x00000000006368c9 in JOIN::optimize_inner() () #2 0x0000000000638eed in JOIN::optimize() ()   #0 0x00000000005b4a70 in Item_func::type() const () #1 0x0000000000615e97 in add_key_fields(JOIN*, key_field_t**, unsigned int*, Item*, unsigned long long, st_sargable_param**) () #2 0x000000000061773a in update_ref_and_keys(THD*, st_dynamic_array*, st_join_table*, unsigned int, Item*, unsigned long long, st_select_lex*, st_sargable_param**) [clone .isra.257] () #3 0x000000000053f1e7 in make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*) () #4 0x0000000000636b00 in JOIN::optimize_inner() () #5 0x0000000000638eed in JOIN::optimize() ()   #0 0x00000000005b4a70 in Item_func::type() const () #1 0x0000000000615ea9 in add_key_fields(JOIN*, key_field_t**, unsigned int*, Item*, unsigned long long, st_sargable_param**) () #2 0x000000000061773a in update_ref_and_keys(THD*, st_dynamic_array*, st_join_table*, unsigned int, Item*, unsigned long long, st_select_lex*, st_sargable_param**) [clone .isra.257] () #3 0x000000000053f1e7 in make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*) () #4 0x0000000000636b00 in JOIN::optimize_inner() () #5 0x0000000000638eed in JOIN::optimize() ()   #0 0x00000000005b4a70 in Item_func::type() const () #1 0x0000000000615ebf in add_key_fields(JOIN*, key_field_t**, unsigned int*, Item*, unsigned long long, st_sargable_param**) () #2 0x000000000061773a in update_ref_and_keys(THD*, st_dynamic_array*, st_join_table*, unsigned int, Item*, unsigned long long, st_select_lex*, st_sargable_param**) [clone .isra.257] () #3 0x000000000053f1e7 in make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*) () #4 0x0000000000636b00 in JOIN::optimize_inner() () #5 0x0000000000638eed in JOIN::optimize() ()   #0 0x00000000005b4a70 in Item_func::type() const () #1 0x0000000000620d54 in remove_eq_conds(THD*, Item*, Item::cond_result*) () #2 0x000000000053facf in make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*) () #3 0x0000000000636b00 in JOIN::optimize_inner() () #4 0x0000000000638eed in JOIN::optimize() ()   #0 0x00000000005b4a70 in Item_func::type() const () #1 0x000000000061fe5a in internal_remove_eq_conds(THD*, Item*, Item::cond_result*) () #2 0x0000000000620d69 in remove_eq_conds(THD*, Item*, Item::cond_result*) () #3 0x000000000053facf in make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*) () #4 0x0000000000636b00 in JOIN::optimize_inner() () #5 0x0000000000638eed in JOIN::optimize() ()   #0 0x00000000005b4a70 in Item_func::type() const () #1 0x000000000061fd4d in cond_is_datetime_is_null(Item*) () #2 0x000000000061fe6d in internal_remove_eq_conds(THD*, Item*, Item::cond_result*) () #3 0x0000000000620d69 in remove_eq_conds(THD*, Item*, Item::cond_result*) () #4 0x000000000053facf in make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*) () #5 0x0000000000636b00 in JOIN::optimize_inner() () #6 0x0000000000638eed in JOIN::optimize() ()
            bar Alexander Barkov added a comment - - edited

            Plan:

            1. Fix "MDEV-8024 Remove excessive update_used_tables() calls".
            Move the call for update_used_tables() from build_equal_items() to build_equal_items_for_cond().

            2a. Introduce a new class Item_ident_or_func_or_sum, common parent for Item_func_or_sum and Item_ident (which covert Item_field and Item_ref), as they behave in very similar ways in the function build_equal_items_for_cond.

            2b. Change the function build_equal_items_for_cond() into virtual method in Item and split the function into at least:

            • Item::build_equal_items_for_cond()
            • Item_cond::build_equal_items_for_cond()
            • Item_cond_and::build_equal_items_for_cond()
            • Item_ident_or_func_or_sum::build_equal_items_for_cond()

            2c. Update: After discussion with Igor, it appeared that Item_field and Item_ref cannot appear in this context in the function build_equal_item_for_cond:

              else if (cond->type() == Item::FUNC_ITEM ||
                       cond->real_item()->type() == Item::FIELD_ITEM)

            because conditions of type:

              WHERE/HAVING field

            are already replaced to:

              WHERE/HAVING field<>0

            by the time of a build_eqial_items() call. See normilize_cond().

            The part of the condition checking for Item_field::FIELD_ITEM was a dead code.

            Therefore the implementation of Item_ident_or_func_or_sum::build_equal_items() can be moved to Item_func, and Item_ident_or_func_or_sum itself is not really needed.

            3. Split the function check_equality() into a method in Item. Implement
            Item::check_equality() and Item_func_eq::check_equality().

            This will replace two virtual calls inside the former function check_equality():

              if (item->type() == Item::FUNC_ITEM &&
                     ((Item_func*) item)->functype() == Item_func::EQ_FUNC)

            into a single virtual call in Item_cond_and::build_equal_items :

              if (item->check_equality(thd, &cond_equal, &eq_list))

            Additionally, implement Item_func_eq::build_equal_items() in addition to Item_func::build_equal_items() and move the call for check_equality() from Item_func::build_equal_items() to Item_func_eq::build_equal_items().

            4. Change this piece of code in the function build_equal_items():

              if (cond)
              {
                cond= build_equal_items_for_cond(thd, cond, inherited, link_equal_fields);
                if (cond->type() == Item::COND_ITEM &&
                    ((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC)
                  cond_equal= &((Item_cond_and*) cond)->cond_equal;
             
                else if (cond->type() == Item::FUNC_ITEM &&
                         ((Item_cond*) cond)->functype() == Item_func::MULT_EQUAL_FUNC)
                {
                  cond_equal= new COND_EQUAL;
                  cond_equal->current_level.push_back((Item_equal *) cond);
                }
              }
             

            to get rid of virtual calls by adding a new out parameter to Item::build_equal_items() of type COND_EQUAL**:

              virtual COND *build_equal_items(THD *thd, COND_EQUAL *inherited,
                                              bool link_item_fields,
                                              COND_EQUAL **cond_equal_ref)
             

            so Item::build_equal_items() can create cond_equal internally.

            This will replace two virtual calls, type() followed by functype(), to a single virtual call for build_equal_cond().

            5. Split the functions remove_eq_conds() and internal_remove_eq_conds() into a virtual method Item::remove_eq_conds(). This will replace about 6 calls for item->type() into a single call for item->remove_eq_conds().

            6. Split the function add_key_fields() into a method in Item. This will remove about 3 virtual calls item->type(), as well as some virtual calls item_func->functype(), and will add one virtual call item->add_key_fields() instead.

            7. Split the function get_mm_parts() into a virtual method in Item. This will replaces one virtual call item->type() to another virtual call, item->get_mm_tree(), but also will remove a virtual call for functype(), which used to distinguish between COND_AND_FUNC and COND_OR_FUNC:

             
              if (((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC)

            8. Split the code in Item::get_mm_tree() responsible for functions into methods in Item_func and its descendants:

            • Item_func
            • Item_func_between
            • Item_func_in
            • Item_equal
              This will remove a virtual call:

                if (type() != Item::FUNC_ITEM)

              as well as remove virtual call checking functype():

                switch (cond_func->functype()) {
                case Item_func::BETWEEN:
                ..

              9. Add get_mm_tree() in Item_func_like, Item_rowready_func2, Item_func_spatial_rel to get rid of the select_optimize() and have_rev_func() virtual calls.
              Remove virtual methods Item_func::select_optimize(), Item_func::have_rev_func(), as well as enum Item_func::optimize_type, because they won't be needed any more.

            bar Alexander Barkov added a comment - - edited Plan: 1. Fix " MDEV-8024 Remove excessive update_used_tables() calls". Move the call for update_used_tables() from build_equal_items() to build_equal_items_for_cond(). 2a. Introduce a new class Item_ident_or_func_or_sum, common parent for Item_func_or_sum and Item_ident (which covert Item_field and Item_ref), as they behave in very similar ways in the function build_equal_items_for_cond. 2b. Change the function build_equal_items_for_cond() into virtual method in Item and split the function into at least: Item::build_equal_items_for_cond() Item_cond::build_equal_items_for_cond() Item_cond_and::build_equal_items_for_cond() Item_ident_or_func_or_sum::build_equal_items_for_cond() 2c. Update: After discussion with Igor, it appeared that Item_field and Item_ref cannot appear in this context in the function build_equal_item_for_cond: else if (cond->type() == Item::FUNC_ITEM || cond->real_item()->type() == Item::FIELD_ITEM) because conditions of type: WHERE/HAVING field are already replaced to: WHERE/HAVING field<>0 by the time of a build_eqial_items() call. See normilize_cond(). The part of the condition checking for Item_field::FIELD_ITEM was a dead code. Therefore the implementation of Item_ident_or_func_or_sum::build_equal_items() can be moved to Item_func, and Item_ident_or_func_or_sum itself is not really needed. 3. Split the function check_equality() into a method in Item. Implement Item::check_equality() and Item_func_eq::check_equality(). This will replace two virtual calls inside the former function check_equality(): if (item->type() == Item::FUNC_ITEM && ((Item_func*) item)->functype() == Item_func::EQ_FUNC) into a single virtual call in Item_cond_and::build_equal_items : if (item->check_equality(thd, &cond_equal, &eq_list)) Additionally, implement Item_func_eq::build_equal_items() in addition to Item_func::build_equal_items() and move the call for check_equality() from Item_func::build_equal_items() to Item_func_eq::build_equal_items(). 4. Change this piece of code in the function build_equal_items(): if (cond) { cond= build_equal_items_for_cond(thd, cond, inherited, link_equal_fields); if (cond->type() == Item::COND_ITEM && ((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC) cond_equal= &((Item_cond_and*) cond)->cond_equal;   else if (cond->type() == Item::FUNC_ITEM && ((Item_cond*) cond)->functype() == Item_func::MULT_EQUAL_FUNC) { cond_equal= new COND_EQUAL; cond_equal->current_level.push_back((Item_equal *) cond); } }   to get rid of virtual calls by adding a new out parameter to Item::build_equal_items() of type COND_EQUAL**: virtual COND *build_equal_items(THD *thd, COND_EQUAL *inherited, bool link_item_fields, COND_EQUAL **cond_equal_ref)   so Item::build_equal_items() can create cond_equal internally. This will replace two virtual calls, type() followed by functype(), to a single virtual call for build_equal_cond(). 5. Split the functions remove_eq_conds() and internal_remove_eq_conds() into a virtual method Item::remove_eq_conds(). This will replace about 6 calls for item->type() into a single call for item->remove_eq_conds(). 6. Split the function add_key_fields() into a method in Item. This will remove about 3 virtual calls item->type(), as well as some virtual calls item_func->functype(), and will add one virtual call item->add_key_fields() instead. 7. Split the function get_mm_parts() into a virtual method in Item. This will replaces one virtual call item->type() to another virtual call, item->get_mm_tree(), but also will remove a virtual call for functype(), which used to distinguish between COND_AND_FUNC and COND_OR_FUNC: if (((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC) 8. Split the code in Item::get_mm_tree() responsible for functions into methods in Item_func and its descendants: Item_func Item_func_between Item_func_in Item_equal This will remove a virtual call: if (type() != Item::FUNC_ITEM) as well as remove virtual call checking functype(): switch (cond_func->functype()) { case Item_func::BETWEEN: .. 9. Add get_mm_tree() in Item_func_like, Item_rowready_func2, Item_func_spatial_rel to get rid of the select_optimize() and have_rev_func() virtual calls. Remove virtual methods Item_func::select_optimize(), Item_func::have_rev_func(), as well as enum Item_func::optimize_type, because they won't be needed any more.

            Item_func_type() calls that remain after step4 (14 calls)

            #1  0x0000555555a1db8a in normalize_cond (cond=0x7fff3c005be8)
             
            #1  0x00005555559b4a93 in setup_conds (thd=0x55555a92e650, 
             
            #1  0x0000555555c85637 in Arg_comparator::is_owner_equal_func (
            #2  0x0000555555c73051 in Arg_comparator::set_compare_func (
            #3  0x0000555555c73e7f in Arg_comparator::set_cmp_func (this=0x7fff3c005cb0, 
            #4  0x0000555555a8bd84 in Arg_comparator::set_cmp_func (this=0x7fff3c005cb0, 
            #5  0x0000555555c72f83 in Item_func::setup_args_and_comparator (
            #6  0x0000555555c72ffb in Item_bool_func2::fix_length_and_dec (
             
            #1  0x0000555555a6ca56 in propagate_cond_constants (thd=0x55555a92e650, 
            #2  0x0000555555a6e365 in optimize_cond (join=0x7fff3c005e48, 
            #3  0x0000555555a4a818 in JOIN::optimize_inner (this=0x7fff3c005e48)
             
            #1  0x0000555555a6f85f in remove_eq_conds (thd=0x55555a92e650, 
            #2  0x0000555555a6e3b3 in optimize_cond (join=0x7fff3c005e48, 
            #3  0x0000555555a4a818 in JOIN::optimize_inner (this=0x7fff3c005e48)
             
            #1  0x0000555555a6eabd in internal_remove_eq_conds (thd=0x55555a92e650, 
            #2  0x0000555555a6fa56 in remove_eq_conds (thd=0x55555a92e650, 
            #3  0x0000555555a6e3b3 in optimize_cond (join=0x7fff3c005e48, 
            #4  0x0000555555a4a818 in JOIN::optimize_inner (this=0x7fff3c005e48)
             
            #1  0x0000555555a6e989 in cond_is_datetime_is_null (cond=0x7fff3c006968)
            #2  0x0000555555a6f457 in internal_remove_eq_conds (thd=0x55555a92e650, 
            #3  0x0000555555a6fa56 in remove_eq_conds (thd=0x55555a92e650, 
            #4  0x0000555555a6e3b3 in optimize_cond (join=0x7fff3c005e48, 
            #5  0x0000555555a4a818 in JOIN::optimize_inner (this=0x7fff3c005e48)
             
            #1  0x0000555555a6e3d7 in optimize_cond (join=0x7fff3c005e48, 
            #2  0x0000555555a4a818 in JOIN::optimize_inner (this=0x7fff3c005e48)
            #3  0x0000555555a49fe4 in JOIN::optimize (this=0x7fff3c005e48)
            #4  0x0000555555a5205a in mysql_select (thd=0x55555a92e650, 
             
            #1  0x0000555555a55ff1 in add_key_fields (join=0x7fff3c005e48, 
            #2  0x0000555555a5804b in update_ref_and_keys (thd=0x55555a92e650, 
            #3  0x0000555555a5303a in make_join_statistics (join=0x7fff3c005e48, 
            #4  0x0000555555a4b050 in JOIN::optimize_inner (this=0x7fff3c005e48)
             
            #1  0x0000555555a5624d in add_key_fields (join=0x7fff3c005e48, 
            #2  0x0000555555a5804b in update_ref_and_keys (thd=0x55555a92e650, 
            #3  0x0000555555a5303a in make_join_statistics (join=0x7fff3c005e48, 
             
            #1  0x0000555555a56407 in add_key_fields (join=0x7fff3c005e48, 
            #2  0x0000555555a5804b in update_ref_and_keys (thd=0x55555a92e650, 
            #3  0x0000555555a5303a in make_join_statistics (join=0x7fff3c005e48, 
            #4  0x0000555555a4b050 in JOIN::optimize_inner (this=0x7fff3c005e48)
             
            #1  0x0000555555a6f85f in remove_eq_conds (thd=0x55555a92e650, 
            #2  0x0000555555a53f78 in make_join_statistics (join=0x7fff3c005e48, 
            #3  0x0000555555a4b050 in JOIN::optimize_inner (this=0x7fff3c005e48)
             
            #1  0x0000555555a6eabd in internal_remove_eq_conds (thd=0x55555a92e650, 
            #2  0x0000555555a6fa56 in remove_eq_conds (thd=0x55555a92e650, 
            #3  0x0000555555a53f78 in make_join_statistics (join=0x7fff3c005e48, 
            #4  0x0000555555a4b050 in JOIN::optimize_inner (this=0x7fff3c005e48)
             
            #1  0x0000555555a6e989 in cond_is_datetime_is_null (cond=0x7fff3c006968)
            #2  0x0000555555a6f457 in internal_remove_eq_conds (thd=0x55555a92e650, 
            #3  0x0000555555a6fa56 in remove_eq_conds (thd=0x55555a92e650, 
            #4  0x0000555555a53f78 in make_join_statistics (join=0x7fff3c005e48, 
            #5  0x0000555555a4b050 in JOIN::optimize_inner (this=0x7fff3c005e48)

            bar Alexander Barkov added a comment - Item_func_type() calls that remain after step4 (14 calls) #1 0x0000555555a1db8a in normalize_cond (cond=0x7fff3c005be8)   #1 0x00005555559b4a93 in setup_conds (thd=0x55555a92e650,   #1 0x0000555555c85637 in Arg_comparator::is_owner_equal_func ( #2 0x0000555555c73051 in Arg_comparator::set_compare_func ( #3 0x0000555555c73e7f in Arg_comparator::set_cmp_func (this=0x7fff3c005cb0, #4 0x0000555555a8bd84 in Arg_comparator::set_cmp_func (this=0x7fff3c005cb0, #5 0x0000555555c72f83 in Item_func::setup_args_and_comparator ( #6 0x0000555555c72ffb in Item_bool_func2::fix_length_and_dec (   #1 0x0000555555a6ca56 in propagate_cond_constants (thd=0x55555a92e650, #2 0x0000555555a6e365 in optimize_cond (join=0x7fff3c005e48, #3 0x0000555555a4a818 in JOIN::optimize_inner (this=0x7fff3c005e48)   #1 0x0000555555a6f85f in remove_eq_conds (thd=0x55555a92e650, #2 0x0000555555a6e3b3 in optimize_cond (join=0x7fff3c005e48, #3 0x0000555555a4a818 in JOIN::optimize_inner (this=0x7fff3c005e48)   #1 0x0000555555a6eabd in internal_remove_eq_conds (thd=0x55555a92e650, #2 0x0000555555a6fa56 in remove_eq_conds (thd=0x55555a92e650, #3 0x0000555555a6e3b3 in optimize_cond (join=0x7fff3c005e48, #4 0x0000555555a4a818 in JOIN::optimize_inner (this=0x7fff3c005e48)   #1 0x0000555555a6e989 in cond_is_datetime_is_null (cond=0x7fff3c006968) #2 0x0000555555a6f457 in internal_remove_eq_conds (thd=0x55555a92e650, #3 0x0000555555a6fa56 in remove_eq_conds (thd=0x55555a92e650, #4 0x0000555555a6e3b3 in optimize_cond (join=0x7fff3c005e48, #5 0x0000555555a4a818 in JOIN::optimize_inner (this=0x7fff3c005e48)   #1 0x0000555555a6e3d7 in optimize_cond (join=0x7fff3c005e48, #2 0x0000555555a4a818 in JOIN::optimize_inner (this=0x7fff3c005e48) #3 0x0000555555a49fe4 in JOIN::optimize (this=0x7fff3c005e48) #4 0x0000555555a5205a in mysql_select (thd=0x55555a92e650,   #1 0x0000555555a55ff1 in add_key_fields (join=0x7fff3c005e48, #2 0x0000555555a5804b in update_ref_and_keys (thd=0x55555a92e650, #3 0x0000555555a5303a in make_join_statistics (join=0x7fff3c005e48, #4 0x0000555555a4b050 in JOIN::optimize_inner (this=0x7fff3c005e48)   #1 0x0000555555a5624d in add_key_fields (join=0x7fff3c005e48, #2 0x0000555555a5804b in update_ref_and_keys (thd=0x55555a92e650, #3 0x0000555555a5303a in make_join_statistics (join=0x7fff3c005e48,   #1 0x0000555555a56407 in add_key_fields (join=0x7fff3c005e48, #2 0x0000555555a5804b in update_ref_and_keys (thd=0x55555a92e650, #3 0x0000555555a5303a in make_join_statistics (join=0x7fff3c005e48, #4 0x0000555555a4b050 in JOIN::optimize_inner (this=0x7fff3c005e48)   #1 0x0000555555a6f85f in remove_eq_conds (thd=0x55555a92e650, #2 0x0000555555a53f78 in make_join_statistics (join=0x7fff3c005e48, #3 0x0000555555a4b050 in JOIN::optimize_inner (this=0x7fff3c005e48)   #1 0x0000555555a6eabd in internal_remove_eq_conds (thd=0x55555a92e650, #2 0x0000555555a6fa56 in remove_eq_conds (thd=0x55555a92e650, #3 0x0000555555a53f78 in make_join_statistics (join=0x7fff3c005e48, #4 0x0000555555a4b050 in JOIN::optimize_inner (this=0x7fff3c005e48)   #1 0x0000555555a6e989 in cond_is_datetime_is_null (cond=0x7fff3c006968) #2 0x0000555555a6f457 in internal_remove_eq_conds (thd=0x55555a92e650, #3 0x0000555555a6fa56 in remove_eq_conds (thd=0x55555a92e650, #4 0x0000555555a53f78 in make_join_statistics (join=0x7fff3c005e48, #5 0x0000555555a4b050 in JOIN::optimize_inner (this=0x7fff3c005e48)

            Item_func_type() calls that remain after step#5 (8 calls)

            #1  0x0000555555a1db8a in normalize_cond (cond=0x7fff3c005be8)
             
            #1  0x00005555559b4a93 in setup_conds (thd=0x55555a92e650, 
             
            #1  0x0000555555c85637 in Arg_comparator::is_owner_equal_func (
            #2  0x0000555555c73051 in Arg_comparator::set_compare_func (
            #3  0x0000555555c73e7f in Arg_comparator::set_cmp_func (this=0x7fff3c005cb0, 
            #4  0x0000555555a8bd84 in Arg_comparator::set_cmp_func (this=0x7fff3c005cb0, 
            #5  0x0000555555c72f83 in Item_func::setup_args_and_comparator (
            #6  0x0000555555c72ffb in Item_bool_func2::fix_length_and_dec (
             
            #1  0x0000555555a6ca56 in propagate_cond_constants (thd=0x55555a92e650, 
            #2  0x0000555555a6e365 in optimize_cond (join=0x7fff3c005e48, 
            #3  0x0000555555a4a818 in JOIN::optimize_inner (this=0x7fff3c005e48)
             
            #1  0x0000555555a6e3d7 in optimize_cond (join=0x7fff3c005e48, 
            #2  0x0000555555a4a818 in JOIN::optimize_inner (this=0x7fff3c005e48)
            #3  0x0000555555a49fe4 in JOIN::optimize (this=0x7fff3c005e48)
            #4  0x0000555555a5205a in mysql_select (thd=0x55555a92e650, 
             
            #1  0x0000555555a55ff1 in add_key_fields (join=0x7fff3c005e48, 
            #2  0x0000555555a5804b in update_ref_and_keys (thd=0x55555a92e650, 
            #3  0x0000555555a5303a in make_join_statistics (join=0x7fff3c005e48, 
            #4  0x0000555555a4b050 in JOIN::optimize_inner (this=0x7fff3c005e48)
             
            #1  0x0000555555a5624d in add_key_fields (join=0x7fff3c005e48, 
            #2  0x0000555555a5804b in update_ref_and_keys (thd=0x55555a92e650, 
            #3  0x0000555555a5303a in make_join_statistics (join=0x7fff3c005e48, 
             
            #1  0x0000555555a56407 in add_key_fields (join=0x7fff3c005e48, 
            #2  0x0000555555a5804b in update_ref_and_keys (thd=0x55555a92e650, 
            #3  0x0000555555a5303a in make_join_statistics (join=0x7fff3c005e48, 
            #4  0x0000555555a4b050 in JOIN::optimize_inner (this=0x7fff3c005e48)

            bar Alexander Barkov added a comment - Item_func_type() calls that remain after step#5 (8 calls) #1 0x0000555555a1db8a in normalize_cond (cond=0x7fff3c005be8)   #1 0x00005555559b4a93 in setup_conds (thd=0x55555a92e650,   #1 0x0000555555c85637 in Arg_comparator::is_owner_equal_func ( #2 0x0000555555c73051 in Arg_comparator::set_compare_func ( #3 0x0000555555c73e7f in Arg_comparator::set_cmp_func (this=0x7fff3c005cb0, #4 0x0000555555a8bd84 in Arg_comparator::set_cmp_func (this=0x7fff3c005cb0, #5 0x0000555555c72f83 in Item_func::setup_args_and_comparator ( #6 0x0000555555c72ffb in Item_bool_func2::fix_length_and_dec (   #1 0x0000555555a6ca56 in propagate_cond_constants (thd=0x55555a92e650, #2 0x0000555555a6e365 in optimize_cond (join=0x7fff3c005e48, #3 0x0000555555a4a818 in JOIN::optimize_inner (this=0x7fff3c005e48)   #1 0x0000555555a6e3d7 in optimize_cond (join=0x7fff3c005e48, #2 0x0000555555a4a818 in JOIN::optimize_inner (this=0x7fff3c005e48) #3 0x0000555555a49fe4 in JOIN::optimize (this=0x7fff3c005e48) #4 0x0000555555a5205a in mysql_select (thd=0x55555a92e650,   #1 0x0000555555a55ff1 in add_key_fields (join=0x7fff3c005e48, #2 0x0000555555a5804b in update_ref_and_keys (thd=0x55555a92e650, #3 0x0000555555a5303a in make_join_statistics (join=0x7fff3c005e48, #4 0x0000555555a4b050 in JOIN::optimize_inner (this=0x7fff3c005e48)   #1 0x0000555555a5624d in add_key_fields (join=0x7fff3c005e48, #2 0x0000555555a5804b in update_ref_and_keys (thd=0x55555a92e650, #3 0x0000555555a5303a in make_join_statistics (join=0x7fff3c005e48,   #1 0x0000555555a56407 in add_key_fields (join=0x7fff3c005e48, #2 0x0000555555a5804b in update_ref_and_keys (thd=0x55555a92e650, #3 0x0000555555a5303a in make_join_statistics (join=0x7fff3c005e48, #4 0x0000555555a4b050 in JOIN::optimize_inner (this=0x7fff3c005e48)

            Item_func_type() calls that remain after step#6 (5 calls)

            #1  0x0000555555a1db8a in normalize_cond (cond=0x7fff3c005be8)
            #2  0x0000555555bc8ed0 in MYSQLparse (thd=0x555557f5c360)
             
            #1  0x00005555559b4a93 in setup_conds (thd=0x55555a92e650, 
             
            #1  0x0000555555c85637 in Arg_comparator::is_owner_equal_func (
            #2  0x0000555555c73051 in Arg_comparator::set_compare_func (
            #3  0x0000555555c73e7f in Arg_comparator::set_cmp_func (this=0x7fff3c005cb0, 
            #4  0x0000555555a8bd84 in Arg_comparator::set_cmp_func (this=0x7fff3c005cb0, 
            #5  0x0000555555c72f83 in Item_func::setup_args_and_comparator (
            #6  0x0000555555c72ffb in Item_bool_func2::fix_length_and_dec (
             
            #1  0x0000555555a6ca56 in propagate_cond_constants (thd=0x55555a92e650, 
            #2  0x0000555555a6e365 in optimize_cond (join=0x7fff3c005e48, 
            #3  0x0000555555a4a818 in JOIN::optimize_inner (this=0x7fff3c005e48)
             
            #1  0x0000555555a6e3d7 in optimize_cond (join=0x7fff3c005e48, 
            #2  0x0000555555a4a818 in JOIN::optimize_inner (this=0x7fff3c005e48)
            #3  0x0000555555a49fe4 in JOIN::optimize (this=0x7fff3c005e48)
            #4  0x0000555555a5205a in mysql_select (thd=0x55555a92e650, 

            bar Alexander Barkov added a comment - Item_func_type() calls that remain after step#6 (5 calls) #1 0x0000555555a1db8a in normalize_cond (cond=0x7fff3c005be8) #2 0x0000555555bc8ed0 in MYSQLparse (thd=0x555557f5c360)   #1 0x00005555559b4a93 in setup_conds (thd=0x55555a92e650,   #1 0x0000555555c85637 in Arg_comparator::is_owner_equal_func ( #2 0x0000555555c73051 in Arg_comparator::set_compare_func ( #3 0x0000555555c73e7f in Arg_comparator::set_cmp_func (this=0x7fff3c005cb0, #4 0x0000555555a8bd84 in Arg_comparator::set_cmp_func (this=0x7fff3c005cb0, #5 0x0000555555c72f83 in Item_func::setup_args_and_comparator ( #6 0x0000555555c72ffb in Item_bool_func2::fix_length_and_dec (   #1 0x0000555555a6ca56 in propagate_cond_constants (thd=0x55555a92e650, #2 0x0000555555a6e365 in optimize_cond (join=0x7fff3c005e48, #3 0x0000555555a4a818 in JOIN::optimize_inner (this=0x7fff3c005e48)   #1 0x0000555555a6e3d7 in optimize_cond (join=0x7fff3c005e48, #2 0x0000555555a4a818 in JOIN::optimize_inner (this=0x7fff3c005e48) #3 0x0000555555a49fe4 in JOIN::optimize (this=0x7fff3c005e48) #4 0x0000555555a5205a in mysql_select (thd=0x55555a92e650,

            The goal of this task has been reached.

            We just need to do some clean-up after the MDEV-7950.

            Separate clean-up tasks have been created:

            • MDEV-8330 Get rid of Item_func::select_optimize() and Item_func::optimize_type
            • MDEV-8332 Get rid of Item_func::sargable
            bar Alexander Barkov added a comment - The goal of this task has been reached. We just need to do some clean-up after the MDEV-7950 . Separate clean-up tasks have been created: MDEV-8330 Get rid of Item_func::select_optimize() and Item_func::optimize_type MDEV-8332 Get rid of Item_func::sargable

            People

              bar Alexander Barkov
              svoj Sergey Vojtovich
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.