> 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
> to
> 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?
>
> TIA!!
>
> R.

have you tried adding another field of say 128 characters (I don't know your
collation), on insert/update put the leading substring in this field and
have this field indexed so you use it to return the order you want?
Alan