Subject ORDER BY on large VARCHAR columns
Author robert_difalco
Ok, I have a table that has a VARCHAR column that is too large to be
indexed. This table will have hundreds of thousands of records and
sometimes the user will want to sort on this VARCHAR column when
viewing paged results. Sometimes a paged query will only show 15
records at a time but I don't think this matters since FIRST..SKIP
needs to first create the entire sorted result set so it knows which
records should be returned with FIRST and SKIP. Either way, it takes
a VERY long time to query this table when I am ordering by this
VARCHAR column -- up to a minute or two.

For Firebird 1.03, what options do I have for optimizing this? I
don't mind taking an INSERT hit to make this query faster, if the
INSERT hit is reasonable. I thought about adding a FLOAT or NUMERIC
ORDER_VALUE column and every time I add a new record, calculating
it's ORDER_VALUE (a numeric value between the previous and next
VARCHAR values), possibly updating existing ORDER_VALUE fields, and
then just doing an ORDER BY on that. However, I'm not quite sure how
actually implement it. (I'm using Java/JDBC).

Any suggestions how to do this or better suggestions on how to
improve my ORDER BY performance?