Why is my index not used for this SELECT DISTINCT query on a text column?

Question

I expected either index to be used for my SELECT DISTINCT query below:

CREATE TABLE test(
  value TEXT
);

INSERT INTO test (value) VALUES ('a');
INSERT INTO test (value) VALUES ('b');
INSERT INTO test (value) VALUES ('c');

CREATE INDEX value_i ON test(value(32));
CREATE FULLTEXT INDEX value_i_ft ON test(value);

SELECT DISTINCT value FROM test;

EXPLAIN SELECT DISTINCT value FROM test;

However, it seems not:

--------------
EXPLAIN SELECT DISTINCT value FROM test
--------------

+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using temporary |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
1 row in set (0.00 sec)

I'm using mysql Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (i686) using readline 5.1. This kind of query is taking 1.5s on 70,000 rows with 250 distinct values, versus about 10ms on the same table for indexed integer columns.

Answer 1

If the optimizer determines that it's quicker not to use the index, it won't. Are you sure the index has been populated by the time you run the query?

Try inserting this before the query:

RUNSTATS ON TABLE test

Answer 2

Does it do this for a fully-populated table as well?

In my experience (at least with MS SQL), the optimizer knows when the table is tiny (like your small example), and knows that loading the index would be wasteful, and just loads the entire table.

Answer 3

check out http://dev.mysql.com/doc/refman/5.1/en/fulltext-fine-tuning.html

The minimum and maximum lengths of words to be indexed are defined by the ft_min_word_len and ft_max_word_len system variables. The default minimum length is 3. In your example all records are 1 char long.