Index på Select med Like på Text felt?

Ifølge: http://dev.mysql.com/doc/mysql/en/mysql-indexes.html

"The index also can be used for LIKE comparisons if the argument to LIKE is a constant string that doesn't start with a wildcard character. For example, the following SELECT statements use indexes:

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

In the first statement, only rows with 'Patrick' <= key_col < 'Patricl' are considered. In the second statement, only rows with 'Pat' <= key_col < 'Pau' are considered.

The following SELECT statements do not use indexes:

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

In the first statement, the LIKE value begins with a wildcard character. In the second statement, the LIKE value is not a constant.

MySQL 4.0 and up performs an additional LIKE optimization. If you use ... LIKE '%string%' and string is longer than three characters, MySQL uses the Turbo Boyer-Moore algorithm to initialize the pattern for the string and then use this pattern to perform the search quicker. "

=========================================================

...kan man altså godt bruge indexes til at fungere med LIKE. Men jeg kan kun få det til at fungere med VARCHAR. Er der en måde man også kan få det til at fungere med TEXT.

eg.

=========================================================

create table table_test
(
    col_test_1 varchar(255),
    col_test_2 varchar(255),
    col_test_3 text
);

insert table_test (col_test_1, col_test_2, col_test_3) values ("We"          , "What" , "I");
insert table_test (col_test_1, col_test_2, col_test_3) values ("hold"        , "a"    , "begyndelsen");
insert table_test (col_test_1, col_test_2, col_test_3) values ("these"      , "piece", "var");
insert table_test (col_test_1, col_test_2, col_test_3) values ("truths"      , "of"  , "ordet");
insert table_test (col_test_1, col_test_2, col_test_3) values ("to"          , "work" , "og");
insert table_test (col_test_1, col_test_2, col_test_3) values ("be"          , "is"  , "ordet");
insert table_test (col_test_1, col_test_2, col_test_3) values ("self-evident", "man"  , "var");

create index x1 on table_test (col_test_1, col_test_2);
create index x2 on table_test (col_test_1, col_test_2, col_test_3(100));
create index x3 on table_test (col_test_3(100));

explain SELECT col_test_1, col_test_2
FROM table_test
WHERE col_test_1 LIKE '%test%' OR col_test_2 LIKE '%test%';

+----+-------------+------------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table      | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+------------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | table_test | index | NULL          | x1  |    512 | NULL |    7 | Using where; Using index |
+----+-------------+------------+-------+---------------+------+---------+------+------+--------------------------+

explain SELECT col_test_1, col_test_2, col_test_3
FROM table_test
WHERE col_test_1 LIKE '%test%' OR col_test_2 LIKE '%test%' OR col_test_3 LIKE "%test%";

+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | table_test | ALL  | NULL          | NULL |    NULL | NULL |    7 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

explain SELECT col_test_3
FROM table_test
WHERE col_test_2 LIKE '%test%';

+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | table_test | ALL  | NULL          | NULL |    NULL | NULL |    7 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+------+-------------+

===============================

De sidste to bruger ikke index. men hvis jeg bruger 'test%' eller '%test' bruger den noget index.