1drop table if exists t0,t1,t2,t3; 2# 3# BUG#38049 incorrect rows estimations with references from preceding table 4# 5create table t0 (a int); 6insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 7create table t1 (a varchar(32)); 8insert into t1 values ('owner'),('requester'),('admincc'),('cc'); 9CREATE TABLE t2 ( 10id int(11) NOT NULL, 11type varchar(32) default NULL, 12PRIMARY KEY (id) 13); 14insert into t2 values (1,'owner'), (2,'admincc'); 15CREATE TABLE t3 ( 16id int(11) NOT NULL, 17domain varchar(32) default NULL, 18type varchar(32) default NULL, 19PRIMARY KEY (id) 20); 21set @domain='system'; 22set @pk=0; 23INSERT INTO t3 select @pk:=@pk+1, 'system', t1.a from t1; 24INSERT INTO t3 select @pk:=@pk+1, 'queue', t1.a from t1, t0 where t0.a<3; 25INSERT INTO t3 select @pk:=@pk+1, 'ticket', t1.a from t1, t0 A, t0 B, t0 C; 26CREATE INDEX groups_d ON t3(domain); 27CREATE INDEX groups_t ON t3(type); 28CREATE INDEX groups_td ON t3(type, domain); 29CREATE INDEX groups_dt ON t3(domain, type); 30For table g this must use ref(groups_dt) and #rows should be around 15 and not 335: 31explain 32SELECT STRAIGHT_JOIN g.id FROM t2 a, t3 g USE INDEX(groups_dt) 33WHERE g.domain = 'queue' AND g.type = a.type; 34id select_type table type possible_keys key key_len ref rows Extra 351 SIMPLE a ALL NULL NULL NULL NULL 2 Using where 361 SIMPLE g ref groups_dt groups_dt 70 const,test.a.type 13 Using index condition 37drop table t0,t1,t2,t3; 38