Hi Set,

>>> A somewhat related question is how large should the page size be to allow
>>> me to index varchar(2048) column with UTF8 character set?
>>>
>> You won't be able to index that. The formula for the max. index key size
>> is:
>>
>> (Page Size / 4) - 4
>>
>> There is also Ivan's key size calculator you can play around with:
>> http://www.volny.cz/iprenosil/interbase/ip_ib_indexcalculator.htm
>
> You could always use an expression index on, say, 'substring(<MyVarCharField> from 1 for 500)'. I assume this would be selective enough in the vast majority of cases (of course, you may have fields where the start is duplicated in millions of records, but then you could just index other parts of the field).

The thing is, that e.g. a WHERE expression needs to have exactly the
same definition. For example, while:

select * from mytable where substring(<MyVarCharField> from 1 for 500) =
'some text'

will use the index, but the following won't use the index:

select * from mytable where <MyVarCharField> = 'some text'

or even this one won't use the index as well:

select * from mytable where substring(<MyVarCharField> from 1 for 400) =
'some text'


This is something one need to keep in mind if using expression indices.


--
With regards,

Thomas Steinmaurer
Upscene Productions
http://www.upscene.com
http://blog.upscene.com/thomas/

Download LogManager Series, FB TraceManager today!
Continuous Database Monitoring Solutions supporting
Firebird, InterBase, Advantage Database, MS SQL Server
and NexusDB!