Indexing TEXT

I am porting my application which is currently Postgres/MySQL/SQLite over to Firebird and am having a bit of trouble finding a replacement for the TEXT type.

I have a type, answers, with an integer ID and some text. Normally this is implemented using the TEXT type with an index on the column (in the case of MySQL this is on the first 50 bytes).

Firebird does not have a TEXT type, however, and the BLOB sub-type which is recommended for storing TEXT can not be indexed. Although large (32k) varchar columns are supported, I can not find a way of only indexing the first few (50 or so) bytes. Of-course a 32k index is not practical. I have also read that a 32k varchar column is 500+2 bytes when empty.

So I am unsure what to do. I need to remain SQL compatible (so SELECT id FROM answers WHERE response = 'foo') and need 10,000 actual characters of space. What should I do?

Expression indexes looked promising, but would probably require changes to my SELECT query.

Regards, Freddie.

Uh? Why do you think this? It will make explicitly what MySQL makes behind the scenes (as usual ...)
Let me add that I don't see the point of indexing a text/blob field, unless you are using a full-text index, which unfortunately is not supported by Firebird.

If I added an index on HASH(response) then my query would now become (using a varchar for the TEXT):

Since I doubt Firebird would automatically use the index for :val = response. So I would need to change my SQL adding an exception case for Firebird.

The reason for this table is that my database works in terms of id's, while users work in terms of text. Therefore I need to translate between the two. Hence the need to index the column.

Regards, Freddie.

I don't actually know the answer; but why don't you set up a test instead of making assumptions?
Instinctively, there would seem little benefit in suporting an expression index if it is not used by SQL.

Another option might be to add a column that uses the first x characters of the VARCHAR column and is maintained by insert and update triggers.

Clive

It would appear my assumption is correct:



I think that would also require me to change my SQL (so that it first tests the indexed column and then the main one).

It looks as if I am going to have to have a special case for firebird and use a different SELECT query for it.

Regards, Freddie.

Can you clarify for me?
Do you want an exact match on the text; but you want an expression index on the first 50 chars to speed the query or do you want all records where the first 50 chars are a match?

Clive

No problem

I only need exact matches. Only reason I said around 50 chars is because indexing the entire field is not possible (as it goes over the maximum index size) and 50 chars should narrow the possibilities down significantly.

I would like an index which allows for:

Where response is a moderately sized varchar column (too large to be indexed in its entirety). A HASH expression index would work, but would require me to add an exceptional case in my application for this (as a different query is required).

Regards, Freddie.

Personally that is what I would (and typically do) do.

There are so many variations in SQL and Stored Procedure language between databases that, when I have an application that must talk to more than one, I encapsulate all the database access and make sure the rest of my program is ignorant of the specific back end.

IMO that is the best way to both maintain one set of general program code and take advantage of the specific strengths of each database back end.

Clive