Re: Simple JOIN is extremely slow.
I like your test case.
Could you try one more:
select a,b,c,d from t1 force index (key4) where a<2 and b<2 and c<2 and d<2;
(Note that it can perform it "Using index".)
When it says "key_len = 4" does that necessarily mean that it has not looked beyond the 4-byte `a`? I think this will give you key_len=4, but will use `b` for ordering -- with no clues:
select * from t1 force index (key4) where a=2 ORDER BY b; -- uses b nicely
select * from t1 force index (key4) where a=2 ORDER BY b, c; -- can use both b,c; probably fails to
select * from t1 force index (key4) where a=2 ORDER BY b, d; -- can't use d
select * from t1 force index (key4) where a=2 ORDER BY NULL; -- probably returns same as ORDER BY b (coincidentally)
While we are on the topic of using more of the index...
select * from t1 force index (key4) where a IN (2,5) AND b = 3;
select * from t1 force index (key4) where a IN (2,5) AND b IN (3,7);
select * from t1 force index (key4) where a IN (2,5) AND b > 3;
I suspect it will use both a and b from the index in the above, but won't use all the indicated fields in the following:
select * from t1 force index (key4) where a IN (2,5) ORDER BY b;
select * from t1 force index (key4) where a IN (2,5) AND b IN (3,7) AND c=1;
These are frustrating:
select * from t1 force index (key4) where a=2 ORDER BY b DESC LIMIT 1;
select * from t1 force index (key4) where a>2 LIMIT 1;
I found that, if there are a million rows with a=2, they will hit a million rows.
The simple workaround for the second one is to reformulate into
select * from t1 force index (key4) where a>=3 LIMIT 1;
The first one is harder to reformulate without knowing if there any rows with a=3.