WL#3527: Extend IGNORE INDEX so places where index is ignored

         can be specified
Currently MySQL allows one to specify what indexes to ignore during
join optimization. The scope of the current USE/FORCE/IGNORE INDEX 
statement is only the FROM clause, while all other clauses are not 
affected.

However, in certain cases, the optimizer
may incorrectly choose an index for sorting and/or grouping, and
produce an inefficient query plan.

This task provides the means to specify what indexes are
ignored/used for what operation in a more fine-grained manner, thus
making it possible to manually force a better plan. We do this
by extending the current IGNORE/USE/FORCE INDEX syntax to:

IGNORE/USE/FORCE INDEX [FOR {JOIN | ORDER | GROUP BY}]

so that:
- if no FOR is specified, the index hint will apply everywhere.
- if MySQL is started with the compatibility option --old_mode then
  an index hint without a FOR clause works as in 5.0 (i.e, the 
  index will only be ignored for JOINs, but can still be used to
  compute ORDER BY).

See the WL#3527 for further details.


BitKeeper/deleted/.del-mysqld.cc.rej:
  Rename: sql/mysqld.cc.rej -> BitKeeper/deleted/.del-mysqld.cc.rej
BitKeeper/deleted/.del-sql_parse.cc.rej:
  Rename: sql/sql_parse.cc.rej -> BitKeeper/deleted/.del-sql_parse.cc.rej
BitKeeper/deleted/.del-table.cc.rej:
  Rename: sql/table.cc.rej -> BitKeeper/deleted/.del-table.cc.rej
mysql-test/r/endspace.result:
  WL3527 : fixed undeterministic test
mysql-test/r/group_by.result:
  WL#3527: test cases
mysql-test/t/endspace.test:
  WL3527 : fixed undeterministic test
mysql-test/t/group_by.test:
  WL#3527: test cases
sql/item.cc:
  WL#3527: renames
sql/mysql_priv.h:
  WL#3527: corrected initialization
sql/mysqld.cc:
  WL#3527: added old_mode command line option
sql/opt_range.cc:
  WL#3527: renames
sql/sql_base.cc:
  WL#3527: 
   - renames
   - correct initialization
   - extended the processing of USE/FORCE/IGNORE index
sql/sql_class.h:
  WL#3527: added old_mode command line option
sql/sql_delete.cc:
  WL#3527: renames
sql/sql_help.cc:
  WL#3527: renames
sql/sql_lex.cc:
  WL#3527: extended parsing of USE/FORCE/IGNORE index
sql/sql_lex.h:
  WL#3527: extended parsing of USE/FORCE/IGNORE index
sql/sql_parse.cc:
  WL#3527: extended parsing of USE/FORCE/IGNORE index
sql/sql_select.cc:
  WL#3527:
   - renames
   - passing additional info to support the extended 
     USE/FORCE/IGNORE INDEX syntax
   - If there is a covering index, and we have 
     IGNORE INDEX FOR GROUP/ORDER, and this index is 
     used for the JOIN part, then we have to ignore the
     IGNORE INDEX FOR GROUP/ORDER.
sql/sql_show.cc:
  WL#3527: passing additional info to support the extended 
     USE/FORCE/IGNORE INDEX syntax
sql/sql_update.cc:
  WL#3527: renames
sql/sql_yacc.yy:
  WL#3527: extended parsing of USE/FORCE/IGNORE index
sql/table.cc:
  WL#3527: extended the processing of USE/FORCE/IGNORE index
sql/table.h:
  WL#3527: extended the processing of USE/FORCE/IGNORE index
storage/myisam/ha_myisam.cc:
  WL#3527: extended the processing of USE/FORCE/IGNORE index

1 files changed, 62 insertions, 6 deletions

diff --git a/mysql-test/t/group_by.test b/mysql-test/t/group_by.test
index 7f887335753..96ca79e15b5 100644
--- a/mysql-test/t/group_by.test
+++ b/mysql-test/t/group_by.test
@@ -706,10 +706,66 @@ DROP TABLE t1;
 # Bug #21174: Index degrades sort performance and 
 #             optimizer does not honor IGNORE INDEX
 #
-CREATE TABLE t1 (a INT, b INT, KEY(a));
-INSERT INTO t1 VALUES (1, 1), (2, 2), (3,3), (4,4);
+CREATE TABLE t1 (a INT, b INT,
+                 PRIMARY KEY (a),
+                 KEY i2(a,b));
+INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);
+INSERT INTO t1 SELECT a + 8,b FROM t1;
+INSERT INTO t1 SELECT a + 16,b FROM t1;
+INSERT INTO t1 SELECT a + 32,b FROM t1;
+INSERT INTO t1 SELECT a + 64,b FROM t1;
+INSERT INTO t1 SELECT a + 128,b FROM t1;
+ANALYZE TABLE t1;
+EXPLAIN SELECT a FROM t1 WHERE a < 2;
+EXPLAIN SELECT a FROM t1 WHERE a < 2 ORDER BY a;
+EXPLAIN SELECT a FROM t1 WHERE a < 2 GROUP BY a;
+EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY,i2);
+EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR JOIN (PRIMARY,i2);
+EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a;
+EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a;
+EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY)
+  IGNORE INDEX FOR GROUP BY (i2) GROUP BY a;
+EXPLAIN SELECT a FROM t1 IGNORE INDEX (PRIMARY) IGNORE INDEX FOR ORDER BY (i2);
+EXPLAIN SELECT a FROM t1 FORCE INDEX (i2);
+EXPLAIN SELECT a FROM t1 USE INDEX ();
+EXPLAIN SELECT a FROM t1 USE INDEX () USE INDEX (i2);
+--error ER_WRONG_USAGE
+EXPLAIN SELECT a FROM t1 
+  FORCE INDEX (PRIMARY) 
+  IGNORE INDEX FOR GROUP BY (i2)
+  IGNORE INDEX FOR ORDER BY (i2)
+  USE INDEX (i2);
+EXPLAIN SELECT a FROM t1 USE INDEX (i2) USE INDEX ();
+--error ER_PARSE_ERROR
+EXPLAIN SELECT a FROM t1 FORCE INDEX ();
+--error ER_PARSE_ERROR
+EXPLAIN SELECT a FROM t1 IGNORE INDEX ();
+EXPLAIN SELECT a FROM t1 USE INDEX FOR JOIN (i2) 
+  USE INDEX FOR GROUP BY (i2) GROUP BY a;
+EXPLAIN SELECT a FROM t1 FORCE INDEX FOR JOIN (i2) 
+  FORCE INDEX FOR GROUP BY (i2) GROUP BY a;
+EXPLAIN SELECT a FROM t1 USE INDEX () IGNORE INDEX (i2);
+EXPLAIN SELECT a FROM t1 IGNORE INDEX (i2) USE INDEX ();
+
+EXPLAIN SELECT a FROM t1 
+  USE INDEX FOR GROUP BY (i2) 
+  USE INDEX FOR ORDER BY (i2)
+  USE INDEX FOR JOIN (i2);
+
+EXPLAIN SELECT a FROM t1 
+  USE INDEX FOR JOIN (i2) 
+  USE INDEX FOR JOIN (i2) 
+  USE INDEX FOR JOIN (i2,i2);
+
+EXPLAIN SELECT 1 FROM t1 WHERE a IN
+  (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
+
+CREATE TABLE t2 (a INT, b INT, KEY(a));
+INSERT INTO t2 VALUES (1, 1), (2, 2), (3,3), (4,4);
+EXPLAIN SELECT a, SUM(b) FROM t2 GROUP BY a LIMIT 2; 
+EXPLAIN SELECT a, SUM(b) FROM t2 IGNORE INDEX (a) GROUP BY a LIMIT 2;
+
+EXPLAIN SELECT 1 FROM t2 WHERE a IN
+  (SELECT a FROM t1 USE INDEX (i2) IGNORE INDEX (i2));
 
-EXPLAIN SELECT a, SUM(b) FROM t1 GROUP BY a LIMIT 2; 
-EXPLAIN SELECT a, SUM(b) FROM t1 IGNORE INDEX (a) GROUP BY a LIMIT 2;
-
-DROP TABLE t1;
+DROP TABLE t1, t2;