Details
Description
--source include/have_innodb.inc
|
|
CREATE TABLE t (a varchar(128), fulltext(a)) engine=InnoDB; |
INSERT INTO t VALUES ('Alabama'),('Washington'); |
SELECT a FROM t FORCE INDEX(a) ORDER BY a; |
ALTER TABLE t ADD PRIMARY KEY(a); |
SELECT a FROM t FORCE INDEX(a) ORDER BY a; |
|
# Cleanup
|
DROP TABLE t; |
10.4 b54e4bf0 |
CREATE TABLE t (a varchar(128), fulltext(a)) engine=InnoDB; |
INSERT INTO t VALUES ('Alabama'),('Washington'); |
SELECT a FROM t FORCE INDEX(a) ORDER BY a; |
a
|
Alabama
|
Washington
|
ALTER TABLE t ADD PRIMARY KEY(a); |
SELECT a FROM t FORCE INDEX(a) ORDER BY a; |
a
|
DROP TABLE t; |
So, on a table without PK the query result is correct, but with a PK it is not.
The plans show that without PK the hint is silently ignored, while with PK the key is used:
INSERT INTO t VALUES ('Alabama'),('Washington'); |
EXPLAIN EXTENDED SELECT a FROM t FORCE INDEX(a) ORDER BY a; |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 SIMPLE t ALL NULL NULL NULL NULL 2 100.00 Using filesort |
Warnings:
|
Note 1003 select `test`.`t`.`a` AS `a` from `test`.`t` FORCE INDEX (`a`) order by `test`.`t`.`a` |
ALTER TABLE t ADD PRIMARY KEY(a); |
EXPLAIN EXTENDED SELECT a FROM t FORCE INDEX(a) ORDER BY a; |
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 SIMPLE t index NULL a 130 NULL 2 100.00 |
Warnings:
|
Note 1003 select `test`.`t`.`a` AS `a` from `test`.`t` FORCE INDEX (`a`) order by `test`.`t`.`a` |
Reproducible on all existing versions and on MySQL 8.0.28.