MySQL Bugs: #44201: Performance problem with View

Bug #44201	Performance problem with View
Submitted:	10 Apr 2009 3:04	Modified:	15 Jun 2010 19:10
Reporter:	Akiko Marti	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1.32, 5.1.35-bzr, 6.0.11-bzr, 5.0, 5.6.99	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Optimizer, performance, VIEW

Description:
We are using views to enforce check constraints (triggers are not an option), but one of the queries became very poor because optimizer chooses to do full table scan instead of index.
If the base table is called directly, optimizer picks the right index.
The original query is complex (four table join) and has millions of rows, but
I created a simple test case to show the problem.

How to repeat:
This is just a test case so it might not be relevant regarding table/column definitions but it simulates our tables and demonstrates the problem. The point is an explain plan becomes different when a view is used.

create table pet (pet_id bigint(20) NOT NULL auto_increment,
  pet_c_id decimal(11,0) not null,
  pet_breed_id char(3) not null,
  PRIMARY KEY (pet_id)
) ENGINE=InnoDB ;

create table breed (breed_id char(3) NOT NULL,
  breed_name varchar(12) not null,
  breed_size char(1) not null,
  PRIMARY KEY (breed_id)
) ENGINE=InnoDB;

alter table pet add foreign key (pet_breed_id) references breed (breed_id);

create index pet_c_id_idx on pet (pet_c_id);
create index breed_name_id_idx on breed (breed_name, breed_id);
create index breed_id_name_idx on breed (breed_id, breed_name);

rename table breed to breed_base;
create view breed as select * from breed_base
where breed_size in ('S','M','L') with cascaded check option;

insert into breed values ('ALA', 'Alaskan Malamute', 'L'),
 ('NEW', 'Newfoundland', 'L'), ('MAL', 'Maltese', 'S'),
 ('BUL', 'Bulldog', 'M'), ('CHI', 'Chihuahua', 'S');

insert into pet values (200000000010001, 45000000001, 'ALA');
insert into pet values (null, 45000000001, 'NEW');
insert into pet values (null, 45000000001, 'BUL');
insert into pet values (null, 45000000002, 'ALA');
insert into pet values (null, 45000000002, 'MAL');
insert into pet values (null, 45000000003, 'NEW');
insert into pet values (null, 45000000003, 'BUL');
insert into pet values (null, 45000000004, 'CHI');

select 'using view';
explain select pet_id, breed_name from pet, breed
where pet_c_id=45000000001 and breed_id=pet_breed_id\G

select 'using base table';
explain select pet_id, breed_name from pet, breed_base
where pet_c_id=45000000001 and breed_id=pet_breed_id\G

Output:

+------------+
| using view |
+------------+
| using view |
+------------+
1 row in set (0.00 sec)

--------------
explain select pet_id, breed_name from pet, breed
where pet_c_id=45000000001 and breed_id=pet_breed_id
--------------

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pet
         type: ref
possible_keys: pet_breed_id,pet_c_id_idx
          key: pet_c_id_idx
      key_len: 5
          ref: const
         rows: 3
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: breed_base
         type: ALL
possible_keys: PRIMARY,breed_id_name_idx
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra: Using where; Using join buffer
2 rows in set (0.00 sec)

--------------
select 'using base table'
--------------

+------------------+
| using base table |
+------------------+
| using base table |
+------------------+
1 row in set (0.00 sec)

--------------
explain select pet_id, breed_name from pet, breed_base
where pet_c_id=45000000001 and breed_id=pet_breed_id
--------------

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pet
         type: ref
possible_keys: pet_breed_id,pet_c_id_idx
          key: pet_c_id_idx
      key_len: 5
          ref: const
         rows: 3
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: breed_base
         type: eq_ref
possible_keys: PRIMARY,breed_id_name_idx
          key: PRIMARY
      key_len: 3
          ref: test.pet.pet_breed_id
         rows: 1
        Extra:
2 rows in set (0.00 sec)

Please see if bug: http://bugs.mysql.com/bug.php?id=42259 is related issue (duplicate). Thanks in advance.

I don't think this problem is related to the bug 42259 since we are not using subqueries. I also tried my test case using 6.0.9-alpha on RH 5.2. The behavior is the same as 5.1.32 (i.e. plan becomes different when the view is used).

Thank you for the problem report. Verified just as described with recent 5.1.35 and 6.0.11 
from bzr, even if view is created with explicit MERGE algorithm. 

On 6.0.11 I've got:

mysql> explain select pet_id, breed_name from pet, breed
    -> where pet_c_id=45000000001 and breed_id=pet_breed_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: breed_base
         type: ALL
possible_keys: PRIMARY,breed_id_name_idx
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: pet
         type: ref
possible_keys: pet_breed_id,pet_c_id_idx
          key: pet_breed_id
      key_len: 3
          ref: test.breed_base.breed_id
         rows: 1
        Extra: Using where
2 rows in set (0.09 sec)

mysql> explain select pet_id, breed_name from pet, breed_base
    -> where pet_c_id=45000000001 and breed_id=pet_breed_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: breed_base
         type: index
possible_keys: PRIMARY,breed_id_name_idx
          key: breed_name_id_idx
      key_len: 17
          ref: NULL
         rows: 5
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: pet
         type: ref
possible_keys: pet_breed_id,pet_c_id_idx
          key: pet_breed_id
      key_len: 3
          ref: test.breed_base.breed_id
         rows: 1
        Extra: Using where
2 rows in set (0.00 sec)

mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 6.0.11-alpha-debug | 
+--------------------+
1 row in set (0.00 sec)

I am unable to reproduce this one.
Tried 6.0.14 and 5.6.0 and 5.1.43

Still repeatable for me.

Tor, have you used InnoDB table engine?