Subject Re: ORDER BY on large VARCHAR columns
Author robert_difalco
> 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?