What if you had another indexed varchar field that holds the first 252 chars
of the field in question (providing your using standard collation)? You can
then create a trigger for insert/update that updates the indexed field. Of
course, if the first 252 chars are the same in the selected records, this
won't help much...

Kevin Lingofelter

-----Original Message-----
From: robert_difalco [mailto:rdifalco@...]
Sent: Thursday, October 21, 2004 4:26 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: ORDER BY on large VARCHAR columns

>
> How about an algorithm to shorten the filename to 8.3 format, like
> Win95 used to, then index that to point to the real path?
> ie: C:\My Documents\Crappy Long Filename\Stupid Long Path\That Some
> Users Insist\On Creating for Lack\Of imagination
> becomes:
> C:\MYDOCU~1\CRAPPY~1\STUPID~1\THATSO~1\ONCREA~1\OFIMAG~1
>

Unfortunately that won't work either. There really is no way to
shorten this stuff. I really need to figure out how to ORDER BY this
VARCHAR field that is too long to index; it's a very large table so
queries with an ORDER BY on this field take VERY long -- minutes.

Keeping that in mind, the only solution I can think of is to create
a new column in that table that is a NUMERIC, a NUMERIC that will
contain the ordinal position of that record when ORDERED BY that
VARCHAR field. The only problem is that I need to find out where it
should be inserted, what is that VARCHAR's sort order. I can't
figure out a good way to do that besides iterating through the
records one by one and doing a "Compare". Sure someone must have
done this before?

R.








Yahoo! Groups Links