MySQL Forums
Forum List  »  Performance

Multi-column index when part 1 is a TEXT column
Posted by: Nicolas Turgeon
Date: March 04, 2005 09:31AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Multi-column index when part 1 is a TEXT column
2545
March 04, 2005 09:31AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.