--source include/have_innodb.inc

--source include/have_sequence.inc

create table t1 (p int primary key, a int, b int, unique(a),key(b)) engine=innodb;

insert into t1 select seq,seq,seq/10 from seq_1_to_100000;

analyze table t1;

explain select straight_join count(*) from t1 as t3, t1, t1 as t2 where t1.a=t2.a and t2.b between 1 and 1500;

set @now=unix_timestamp(now(6));

select straight_join count(*) from t1 as t3, t1, t1 as t2 where t1.a=t2.a and t2.b between 1 and 1500;

select unix_timestamp(now(6))-@now;

drop table t1;

+------+-------------+-------+---------------+---------------+------+---------+-----------+---------+----------+--------------------------------------------------------------+

| id   | select_type | table | type          | possible_keys | key  | key_len | ref       | rows    | filtered | Extra                                                        |

+------+-------------+-------+---------------+---------------+------+---------+-----------+---------+----------+--------------------------------------------------------------+

|    1 | SIMPLE      | t3    | index         | NULL          | a    | 5       | NULL      | 100256  |   100.00 | Using index                                                  |

|    1 | SIMPLE      | t1    | range         | a             | a    | 5       | NULL      | 50128   |   100.00 | Using where; Using index; Using join buffer (flat, BNL join) |

|    1 | SIMPLE      | t2    | eq_ref|filter | a,b           | a|b  | 5|5     | test.t1.a | 1 (28%) |    27.86 | Using where; Using rowid filter                              |

+------+-------------+-------+---------------+---------------+------+---------+-----------+---------+----------+--------------------------------------------------------------+

create table t1 (

  p int primary key, 

  badcol1 int, 

  badcol2 int, 

  a int, 

  b int, 

  unique(badcol1, badcol2),

  unique(a),

  key(b)

) engine=innodb;

insert into t1 select seq, (seq/30000), seq, seq,seq/10 from seq_1_to_100000;

analyze table t1;

explain 

select straight_join count(*) 

from 

  t1 as t3, t1, t1 as t2 

where 

  t1.a=t2.a and 

  t2.b between 1 and 1500 and 

  t1.badcol1=t2.badcol1 ;