Subject RE: [firebird-support] Re: ORDER BY on large VARCHAR columns
Author kevin
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
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:

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?


Yahoo! Groups Links