Howto do efficent fulltext search in Teiid

[cjohn001]

Hello together,

I am trying to implement a full text search with Teiid, preferable in a materialized table. I have a mysql database below Teiid which supports FULLTEXT indexes which are used with a MATCH ... AGAINST syntax. When extracting the db schema with Teiid Designer, Designer seems to generate a standard index for the relevant text field instead of a FULLTEXT index.

from the extracted table definition:

CREATE FOREIGN TABLE FDBProducts_SRC (

product_name string(256) OPTIONS(NAMEINSOURCE '"product_name"', NATIVE_TYPE 'VARCHAR'),

CONSTRAINT product_nameInFDBProducts_idx INDEX(product_name),

)OPTIONS(NAMEINSOURCE '"FDBProducts"', UPDATABLE 'TRUE');

I  also have a materialized table for the above

CREATE View FDBProducts (

...

)

OPTIONS (MATERIALIZED 'TRUE', "teiid_rel:ALLOW_MATVIEW_MANAGEMENT" 'true', "teiid_rel:MATVIEW_UPDATABLE" 'true')

AS

SELECT

...

FROM FDBProducts_SRC p;

Well, there seems to be no MATCH AGAINST syntax supported in Teiid according to the BNF for SQL grammar documentation. The match predicate LIKE seems closest to what I am searching for. Now I have several questions regarding an efficient implementation:

1. as I need to search for substrings in the product_name string, does a normal index in the materialized Teiid table deliver any benefit for the text search?

2. If I specifiy a standard index rather than a FULLTEXT index on the FOREING TABLE FDBProducts_SRC (seems like only available option at the moment) does this have any negative side effects on the Teiid optimizer?

3. I am now wondering how to proceed:

- better go with materialized table and a defined standard index

- better go with materialized table and without index

- search directly on foreign table rather than materialized table with LIKE (MATCH AGAINST seems to be not available) and hope that Teiid pushes down the search query. I would than probably not use the FULLTEXT index in the pushed down query on the mysql database, hence than probably using the materialized table with Teiid would at least offload processing from the mysql db.

- create a stored procedure on the mysql database which does the search with MATCH AGAINST

4. Is there another way on how to implement an efficient text search with Teiid which I am not aware of yet?

Thanks for your help!

Best regards,

Christoph

[shawkins]

> Well, there seems to be no MATCH AGAINST syntax supported in Teiid according to the BNF for SQL grammar documentation.

That is correct.  Many vendors all have different syntax for full-text search, so we hadn't yet added anything for that in the grammar.

>  1. as I need to search for substrings in the product_name string, does a normal index in the materialized Teiid table deliver any benefit for the text search?

It's a simple sorted index, so unless you are only searching based upon a prefix, it won't help.

> 2. If I specifiy a standard index rather than a FULLTEXT index on the FOREING TABLE FDBProducts_SRC (seems like only available option at the moment) does this have any negative side effects on the Teiid optimizer?

The short answer is no.  As we aren't deeply introspecting source level optimizer information, but rather making general assumptions about cardinality, the type of index isn't that important to us.

> 3. I am now wondering how to proceed:

If you need full text search - that is something beyond like or regex like searches - then you probably should rely on the source full-text search.  One approach would be to create a function to represent the search predicate:

create foreign function match_against (col varchar, expr varchar) returns boolean options ("teiid_rel:native-query" 'MATCH ($1) AGAINST ($2)');

In your Teiid sql you would use "select ... from t where match_against(t.col, 'some text')"

Note that supporting varargs for the cols and the search modifier option are possible, but it can't currently be expressed with the native-query option.  That would require creating some custom handling in the translator.

> 4. Is there another way on how to implement an efficient text search with Teiid which I am not aware of yet?

There is no built-in logic for maintaining a full search text index.  Generally the guidance is that internal materialization is for smaller data sets such that a linear regex scan shouldn't be a problem.  For everything else you are looking at some form of pushdown to have a source handle the search.  You can certainly log an issue that we add some general full-text search predicate to our grammar with push down where supported - but that will likely not look like nor capture all of the options of any one vendors search functionality.

[cjohn001]

Hello Steven,

thanks for the explanation, than I will go the route via using a stored procedure on the data source or the match_against variant that you just described.