Full text is not working for me

Hi all,

I havent found any time difference between the FTS and normal search.

I have created Full text search successfully in wrk_contact table.

CREATE UNIQUE INDEX UI_UKContact ON wrk_contact(id_contact)

CREATE FULLTEXT CATALOG contact_cat AS DEFAULT;

CREATE FULLTEXT INDEX ON wrk_contact(lastname) KEY INDEX UI_UKContact;

I have inserted 1.5 lac of records in that wrk_contact table.

I am trying to do following query

SELECT distinct *

FROM wrk_contact

WHERE CONTAINS(

lastname, '"g*"'

)

Same set of records i have inserted in wrk_contact_tmp table without creating full text search.

I was doing the following query

SELECT distinct *

FROM wrk_contact_tmp

WHERE lastname like 'g%'

I ran both queries seperatly but the execution time for the both queries are same..

Any mistake i made please help me.

Regards

gomaz

Hi Gomaz,

If using CONTAINS you are finding all the records you are looking for, then you did everything correct and FTS is working for you properly. I did not fully understand how many records has your table, 1.5 M? 1.5 thousands?

Also, how many records are returned after search for g*?

In easy queries as this one, with small number of records, the advantage in performance of FTS is not so obvious. You will notice how powerful is FTS this when you will query large amount of data (e.g: 100M) and/or using more complex criteria.

Other advantages of FTS is that it is language aware (instead of pattern based only as LIKE is). in FTS you can look for inflectional forms, Thesaurus,etc... per a given language. Algo you can use NEAR of weights in your queries, etc.... Another important advantage is that FTS supports binary data and special types as XML, etc.. while LIKE only works for text data and it always performs a SCAN of the table

Let me know please the performance you are experiencing in your scenario and we can try to see if it is expected in FTS or not.

Thanks!

|||Hi Fernando,

Thanks for your reply.
Total number of records are 150 thousands ,
In that 148 thousands records having the maching text for g*.
So my total number of records are 148000 (approx).

Is this enough or Do I need to insert more like millions of records.?
regards
GOMAZ