Multi-column index when part 1 is a TEXT column

[Nicolas Turgeon]

Hi,

I have a problem using part 2 of a multi-column index for ordering the
results of a query. If index part 1 is a VARCHAR, the results are
ordered using part 2 without a filesort, but if part 1 is a TEXT column,
filesort is performed, degrading performance. Here's a simplified
example to show the problem (I'm using MySQL 4.1.9):

Here's the working scenario (with column 'a' VARCHAR):

CREATE TABLE `test` (
`uid` bigint(20) NOT NULL auto_increment,
`a` varchar(50) default NULL,
`b` varchar(50) default NULL,
`c` varchar(50) default NULL,
PRIMARY KEY (`uid`),
KEY `a` (`a`(20),`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

mysql> select * from test;
+-----+------+------+------+
| uid | a | b | c |
+-----+------+------+------+
| 1 | aa1 | bb1 | cc1 |
| 2 | aa2 | bb2 | cc2 |
| 3 | aa3 | bb3 | cc3 |
| 4 | aa4 | bb4 | cc4 |
| 5 | aa1 | bb1 | cc2 |
+-----+------+------+------+

mysql> explain select c from test where a='aa1' order by b;
type: ref
key: a
key_len: 21
ref: const
rows: 2
Extra: Using where

You see in the Extra column that filesort is not performed, it is using the
index on column b for the ORDER BY b, as expected.


Now, changing column 'a' into a TEXT column:

alter table test drop index a, modify a text, add index (a(20), b);

CREATE TABLE `test` (
`uid` bigint(20) NOT NULL auto_increment,
`a` text,
`b` varchar(50) default NULL,
`c` varchar(50) default NULL,
PRIMARY KEY (`uid`),
KEY `a` (`a`(20),`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

mysql> explain select c from test where a='aa1' order by b;
type: range
key: a
key_len: 23
ref: NULL
rows: 2
Extra: Using where; Using filesort

You see that filesort is performed in this case! It is not using part 2
of the (a(20), b) index anymore to give me a "free" ORDER BY. I couldn't
find anything about TEXT column restrictions of this kind either on the MySQL
site or on the Web in general.

This hurts performance in 2 ways (at least): first, the extra sorting can
be expensive with large result sets. Second, when using the LIMIT N keyword,
all the rows have to be fetched for the subsequent filesort, instead of just
fetching N rows.

The problem is that I need more than 255 characters on column 'a', so
I can't use VARCHAR. Anybody knows what I'm doing wrong, and if there is
a workaround that would allow me to use the part 2 index when part 1 is
a TEXT column?

Many thanks,

-Nicolas