Hello Optimizing-Crew,
here is another one of optimizer effects which I do not understand.
CREATE TABLE t1(
id INT NOT NULL AUTO_INCREMENT,
dummy TEXT NOT NULL,
CONSTRAINT pk_t1 PRIMARY KEY (id)
) ENGINE=InnoDB;
CREATE TABLE t2(
lid INT NOT NULL AUTO_INCREMENT,
id INT NOT NULL,
txt TEXT,
CONSTRAINT pk_t2 PRIMARY KEY (lid),
INDEX ix_id (id),
INDEX ix_txt USING BTREE(txt(250)),
INDEX ix_txt_id USING BTREE(txt(250),id)
) ENGINE=InnoDB;
ANALYZE TABLE t1, t2;
--Query1
explain
select * from t1 join (select id from t2 where txt like 'Hall%' ) as y on y.id = t1.id;
/*
+----+-------------+------------+--------+------------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+------------------+---------+---------+------+------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 80 | |
| 1 | PRIMARY | t1 | eq_ref | PRIMARY | PRIMARY | 4 | y.id | 1 | |
| 2 | DERIVED | t2 | range | ix_txt,ix_txt_id | ix_txt | 753 | NULL | 80 | Using where |
+----+-------------+------------+--------+------------------+---------+---------+------+------+-------------+
*/
--Query2
explain
select * from t1 join ( select id from t2 use index (ix_txt_id) where txt like 'Hall%' ) as y on y.id = t1.id;
/*
+----+-------------+------------+--------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+-----------+---------+------+------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 80 | |
| 1 | PRIMARY | t1 | eq_ref | PRIMARY | PRIMARY | 4 | y.id | 1 | |
| 2 | DERIVED | t2 | range | ix_txt_id | ix_txt_id | 753 | NULL | 80 | Using where |
+----+-------------+------------+--------+---------------+-----------+---------+------+------+-------------+
*/
/* Server info
+-------------------------+----------------+
| Variable_name | Value |
+-------------------------+----------------+
| version | 5.0.26 |
| version_compile_machine | x86_64 |
+-------------------------+----------------+
*/
For Query1 I understand the output. In this case the Optimizer misses to select the better Index and does a range query with ix_txt. Fairly ok so far.
Query2 is a little bit disappointing. Even after letting him use the correct Index he does not serve the subquery completely out of the Index! In the explain Extra column "Using Index" is not showing up. Why?
Nice Greetings
3bit