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

extended_keys=on makes eq_ref access be ref access

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5.29
    • Fix Version/s: 10.0.2, 5.5.30
    • Component/s: None
    • Labels:
      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

            • Assignee:
              igor Igor Babaev
              Reporter:
              psergey Sergei Petrunia
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: