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')
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!