diff options
Diffstat (limited to 'mysql-test/main/key.test')
| -rw-r--r-- | mysql-test/main/key.test | 23 |
1 files changed, 23 insertions, 0 deletions
diff --git a/mysql-test/main/key.test b/mysql-test/main/key.test index 29e08b88..7ec18ad5 100644 --- a/mysql-test/main/key.test +++ b/mysql-test/main/key.test @@ -227,9 +227,12 @@ drop table t1; CREATE TABLE t1 (numeropost mediumint(8) unsigned NOT NULL default '0', numreponse int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (numeropost,numreponse), UNIQUE KEY numreponse (numreponse)); INSERT INTO t1 (numeropost,numreponse) VALUES ('1','1'),('1','2'),('2','3'),('2','4'); SELECT numeropost FROM t1 WHERE numreponse='1'; +# No 'Using index' EXPLAIN SELECT numeropost FROM t1 WHERE numreponse='1'; FLUSH TABLES; SELECT numeropost FROM t1 WHERE numreponse='1'; +# This one will have 'Using index' +EXPLAIN SELECT numreponse+0 FROM t1 WHERE numreponse='1'; drop table t1; # @@ -608,3 +611,23 @@ drop table t1,t2; --echo # create table t1 (a int, b int, key(a), key(a desc)); drop table t1; + +--echo # Check some issues with FORCE INDEX and full index scans +--echo # (Does FORCE INDEX force an index scan) +--echo # + +create table t1 (a int primary key, b int, c int, d int, +key k1 (b) using BTREE, key k2 (c,d) using btree) engine=heap; +insert into t1 select seq as a, seq as b, seq as c, seq as d +from seq_1_to_100; +explain select sum(a+b) from t1 force index (k1) where b>0 and a=99; +explain select sum(a+b) from t1 force index (k1) where a>0; +explain select sum(a+b) from t1 force index (k1); +explain select sum(a+b) from t1 force index for join (k1); +explain select sum(a+b) from t1 force index for order by (k1); +explain select sum(a+b) from t1 force index (k1,k2); +select sum(a+b) from t1 force index (k1); +explain select sum(a+b) from t1 force index (primary); +select sum(a+b) from t1 force index (primary); +explain select straight_join sum(a+b) from seq_1_to_10 as s, t1 force index (k2) where t1.a=s.seq; +drop table t1; |