Description:
It's impossible to use optimizer hints for Views
This is could be related to https://bugs.mysql.com/bug.php?id=15344
How to repeat:
CREATE TABLE `t` (
`a` int(11) NOT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `b` (`b`),
KEY `c` (`c`)
) ENGINE=InnoDB;
-- fill the table with values 1-N.
create view v1 as SELECT /*+ NO_ICP(t) */ * FROM t;
mysql> explain SELECT /*+ NO_ICP(t) */ * FROM t WHERE b>=42 AND c<=42 LIMIT 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: range
possible_keys: b,c
key: c
key_len: 5
ref: NULL
rows: 42
filtered: 50.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql> explain SELECT /*+ NO_ICP(t) */ * FROM v1 WHERE b>=42 AND c<=42 LIMIT 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: range
possible_keys: b,c
key: c
key_len: 5
ref: NULL
rows: 42
filtered: 50.00
Extra: Using index condition; Using where
mysql> explain SELECT * FROM v1 WHERE b>=42 AND c<=42 LIMIT 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: range
possible_keys: b,c
key: c
key_len: 5
ref: NULL
rows: 42
filtered: 50.00
Extra: Using index condition; Using where
1 row in set, 1 warning (0.00 sec)
mysql> explain SELECT /*+ NO_ICP(v1) */ * FROM v1 WHERE b>=42 AND c<=42 LIMIT 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: range
possible_keys: b,c
key: c
key_len: 5
ref: NULL
rows: 42
filtered: 50.00
Extra: Using index condition; Using where
1 row in set, 2 warnings (0.00 sec)
As you can see it's not possible to use NO_ICP hint (and other ones like QB_NAME) for views.
