SQLite fts search with join slow

Hi all,

I am doing a little testing with SQLite and fts -

I have a table with data I want (NODE) and a table with data/field I need to search(NODEMETADATA), linked by an integer field (METAID).

The data I am searching is within a text field of between 50 and 1000 characters (NODEMETADATA.ATTRIOBUTES). but the data I want to display is in a linked table (the NODE table).

I have tried the "normal " way -

SELECT NODEMETADATA.ATTRIBUTES, NODE.NODENUM FROM NODEMETADATA INNER JOIN NODE ON NODEMETADATA.METAID = NODE.METAID WHERE (((NODEMETADATA.ATTRIBUTES) Like '%LV10%'));

Which gives me 9 rows in 0.228 seconds (not bad)

So I tried creating an fts virtual table which I called (DocSearch) and added the NODEMETADATA.METAID and NODEMETADATA.ATTRIBUTES fields, then tried the following query thinking it should be quicker -

SELECT DocSearch.ATTRIBUTES, NODE.NODENUM FROM DocSearch INNER JOIN NODE ON DocSearch.METAID = NODE.METAID WHERE (((DocSearch.ATTRIBUTES) Match 'LV10'));

This gave me a result of 7 rows in 3.712 seconds !!!

I know why there were a different number of rows returned (because 7 records had LV10 separate from other text whilst 3 had it in the middle of some text).

Do fts tables not play well as part of a join? Should I have a different type of join?

Any pointers appreciated.

Cheers,

Paul