At 08:51 AM 9/05/2004 +0100, you wrote:

>OK I'm working on my conversion paper and need the 'current state of play'.
>
>One of the conversion problems from MySQL is FULLTEXT search. This is
>usually ignored, but I need an update on 'preferred practice' with FB1.5 :)
>
>Is it still preferred to use a BLOB over a large VARCHAR, and can we
>actually now search on BLOB - which I seem to remember came in sometime.

Text blobs, yes, after a fashion. You can use STARTING WITH (which is
case-sensitive) and CONTAINING (which isn't). There's a bug with
CONTAINING which I don't think will be fixed by 1.5.1, that it won't find
the search string in the text if it spans a segment boundary; and it seems
to be unreliable beyond 1024 bytes in any event.

One of the stoppers about using large VARCHARS is that they are going to
clog up the wire on a newbie-style query. With BLOBs you only get the
blob_id and call for the BLOB on request.

>Second related problem. With a large VARCHAR can we index on a
>SUBSTRING, or do we have to create a 'trimmed' field to use for the index.

Not yet, though I believe expression indexes are coming in Fb2. For now,
just use a proxy column to store a substring; or a 1:many keywords
relation to store keywords for searching.

/h