Hello
KimSeong Loh Wrote:
-------------------------------------------------------
> Your txt column is a TEXT type, you only index the
> first 250 characters.
> Even though it could work but probably the
> optimiser is not smart enough to detect that.
> Maybe submit a feature request.
That makes Sense! Bu unfortunately changing the whole thing to varchar does not change the situation!
CREATE TABLE t2(
lid INT NOT NULL AUTO_INCREMENT,
id INT NOT NULL,
txt VARCHAR(500),
CONSTRAINT pk_t2 PRIMARY KEY (lid),
INDEX ix_id (id),
INDEX ix_txt USING BTREE(txt),
INDEX ix_txt_id USING BTREE(txt,id)
) ENGINE=InnoDB;
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 | 768 | NULL | 80 | Using where |
+----+-------------+------------+--------+---------------+-----------+---------+------+------+-------------+
*/
explain select id from t2 use index (ix_txt_id) where txt like 'Hall%';
/*
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
| 1 | SIMPLE | t2 | range | ix_txt_id | ix_txt_id | 768 | NULL | 80 | Using where |
+----+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
*/
Greetings
3bit
PS. is there any code-highlighting feature in this Forum? (Or should I file a Feature Request?)