757
757
# Bug #21174: Index degrades sort performance and
758
# optimizer does not honor IGNORE INDEX
758
# optimizer does not honor IGNORE INDEX.
760
CREATE TABLE t1 (a INT, b INT, KEY(a));
761
INSERT INTO t1 VALUES (1, 1), (2, 2), (3,3), (4,4);
763
EXPLAIN SELECT a, SUM(b) FROM t1 GROUP BY a LIMIT 2;
764
EXPLAIN SELECT a, SUM(b) FROM t1 IGNORE INDEX (a) GROUP BY a LIMIT 2;
761
CREATE TABLE t1 (a INT, b INT,
764
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
765
INSERT INTO t1 SELECT a + 8,b FROM t1;
766
INSERT INTO t1 SELECT a + 16,b FROM t1;
767
INSERT INTO t1 SELECT a + 32,b FROM t1;
768
INSERT INTO t1 SELECT a + 64,b FROM t1;
769
INSERT INTO t1 SELECT a + 128,b FROM t1;
771
EXPLAIN SELECT a FROM t1 WHERE a < 2;
772
EXPLAIN SELECT a FROM t1 WHERE a < 2 ORDER BY a;
773
EXPLAIN SELECT a FROM t1 WHERE a < 2 GROUP BY a;
774
EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY,i2);
775
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR JOIN (PRIMARY,i2);
776
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;
777
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
778
EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY)
779
IGNORE INDEX FOR GROUP BY (i2) GROUP BY a;
780
EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY) IGNORE INDEX FOR ORDER BY (i2);
781
EXPLAIN SELECT a FROM t1 FORCE INDEX (i2);
782
EXPLAIN SELECT a FROM t1 USE INDEX ();
783
EXPLAIN SELECT a FROM t1 USE INDEX () USE INDEX (i2);
784
--error ER_WRONG_USAGE
785
EXPLAIN SELECT a FROM t1
786
FORCE INDEX (PRIMARY)
787
IGNORE INDEX FOR GROUP BY (i2)
788
IGNORE INDEX FOR ORDER BY (i2)
790
EXPLAIN SELECT a FROM t1 USE INDEX (i2) USE INDEX ();
791
--error ER_PARSE_ERROR
792
EXPLAIN SELECT a FROM t1 FORCE INDEX ();
793
--error ER_PARSE_ERROR
794
EXPLAIN SELECT a FROM t1 IGNORE INDEX ();
795
EXPLAIN SELECT a FROM t1 USE INDEX FOR JOIN (i2)
796
USE INDEX FOR GROUP BY (i2) GROUP BY a;
797
EXPLAIN SELECT a FROM t1 FORCE INDEX FOR JOIN (i2)
798
FORCE INDEX FOR GROUP BY (i2) GROUP BY a;
799
EXPLAIN SELECT a FROM t1 USE INDEX () IGNORE INDEX (i2);
800
EXPLAIN SELECT a FROM t1 IGNORE INDEX (i2) USE INDEX ();
802
EXPLAIN SELECT a FROM t1
803
USE INDEX FOR GROUP BY (i2)
804
USE INDEX FOR ORDER BY (i2)
805
USE INDEX FOR JOIN (i2);
807
EXPLAIN SELECT a FROM t1
808
USE INDEX FOR JOIN (i2)
809
USE INDEX FOR JOIN (i2)
810
USE INDEX FOR JOIN (i2,i2);
812
EXPLAIN SELECT 1 FROM t1 WHERE a IN
813
(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
815
CREATE TABLE t2 (a INT, b INT, KEY(a));
816
INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);
817
EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2;
818
EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2;
820
EXPLAIN SELECT 1 FROM t2 WHERE a IN
821
(SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
823
SHOW VARIABLES LIKE 'old';
824
--error ER_INCORRECT_GLOBAL_LOCAL_VAR