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

extended_keys=on makes eq_ref access be ref access

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5.29
    • 10.0.2, 5.5.30
    • None
    • None

    Description

      If one turns on extended keys, the optimizer starts use ref access where it used to use eq_ref access.

      eq_ref is somewhat slower than 'equivalent' ref access, but that is only part of the problem. ORDER BY optimization checks if eq_ref is used, and if yes, enables certain strategies (see sql_select.cc, eq_ref_table()).

      Testcase:

      --source include/have_innodb.inc
      create table t20 (a int not null) engine=innodb;
       
      insert into t20 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
       
      create table t21 (pk int primary key, a int not null, b int, unique(a))engine=innodb;
       
      insert into t21
      select 
        A.a + 10 * B.a, A.a + 10 * B.a, A.a + 10 * B.a 
      from t20 A, t20 B;
       
      set optimizer_switch='extended_keys=on';
      explain select * from t20, t21 where t21.a=t20.a;
      set optimizer_switch='extended_keys=off';
      explain select * from t20, t21 where t21.a=t20.a;

      its output:

      create table t20 (a int not null) engine=innodb;
      insert into t20 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      create table t21 (pk int primary key, a int not null, b int, unique(a))engine=innodb;
      insert into t21
      select 
      A.a + 10 * B.a, A.a + 10 * B.a, A.a + 10 * B.a 
      from t20 A, t20 B;
      set optimizer_switch='extended_keys=on';
      explain select * from t20, t21 where t21.a=t20.a;
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	SIMPLE	t20	ALL	NULL	NULL	NULL	NULL	10	
      1	SIMPLE	t21	ref	a	a	4	test.t20.a	1	
      set optimizer_switch='extended_keys=off';
      explain select * from t20, t21 where t21.a=t20.a;
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
      1	SIMPLE	t20	ALL	NULL	NULL	NULL	NULL	10	
      1	SIMPLE	t21	eq_ref	a	a	4	test.t20.a	1	

      Attachments

        Activity

          People

            igor Igor Babaev
            psergei Sergei Petrunia
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.