optimization - MySQL, the index of text can`t work -

i create table this

create table `text_tests` (      `id` int(11) not null auto_increment,      `text_st_date` text not null,      `varchar_st_date` varchar(255) not null default '2015-08-25',      `text_id` text not null,      `varchar_id` varchar(255) not null default '0',      `int_id` int(11) not null default '0',      `created_at` datetime default null,      `updated_at` datetime default null,      primary key (`id`),      key `idx_of_text_st_date` (`text_st_date`(50),`id`),      key `idx_of_varchar_st_date` (`varchar_st_date`,`id`),      key `idx_of_text_id` (`text_id`(20),`id`),      key `idx_of_varchar_id` (`varchar_id`,`id`),      key `idx_of_int_id` (`int_id`,`id`) ) engine=innodb default charset=utf8 

then make datas use ruby

(1..10000).each |_i|   item = texttest.new   item.text_st_date = (time.now + _i.days).to_s   item.varchar_st_date = (time.now + _i.days).to_s   item.text_id = _i   item.varchar_id = _i   item.int_id = _i    item.save end 

at last, try use index of text, can`t work, full table scan.

explain select id text_tests order text_st_date desc limit 20\g; *************************** 1. row ***************************            id: 1   select_type: simple         table: text_tests          type:   possible_keys: null           key: null       key_len: null           ref: null          rows: 9797         extra: using filesort 1 row in set (0.02 sec)  explain select id text_tests order text_id desc limit 20\g; *************************** 1. row ***************************            id: 1   select_type: simple         table: text_tests          type:  possible_keys: null           key: null       key_len: null           ref: null          rows: 9797         extra: using filesort 1 row in set (0.00 sec) 

varchar works good

explain select id text_tests order varchar_st_date desc limit 20\g;  *************************** 1. row ***************************            id: 1   select_type: simple         table: text_tests          type: index possible_keys: null           key: idx_of_varchar_st_date `enter code here`       key_len: 771           ref: null          rows: 20         extra: using index 1 row in set (0.00 sec)    explain select id text_tests order varchar_id desc limit 20\g; *************************** 1. row ***************************            id: 1   select_type: simple         table: text_tests          type: index possible_keys: null           key: idx_of_varchar_id        key_len: 771           ref: null          rows: 20         extra: using index 1 row in set (0.00 sec) 

why index of text can`t work, , how use index of text?

indexes don't serve strong purpose satisfy queries return rows of table in result set. 1 of primary purposes accelerate where , join ... on clauses. if query has no clause, don't surprised if query planner decides scan whole table.

also, first query order text_column. index encompasses first fifty characters of column. so, satisfy query, mysql has sort whole thing. what's more, has sort on hard drive, because in-memory table support can't handle blob or text large objects.