08-06-12, 11:14 #1Registered User
- Join Date
- Aug 2012
Unanswered: FullTextSearch not returning results after re-building Full text index
we have a table that is Full Text Search index enabled on one column.This table has 200 lakhs of rows(20000000) . ContainsTable() function is searching data with in these 200 lakhs of rows(20000000), if any new rows are inserted then the ContainsTable is not going to search in these recent inserted rows.
we observed when we try for a data to search. it is returning the rows till the rows that are inserted date is less than 30th of march 2012. but not searching in the records that are created after April month , if even the data we are searching is available .
TableFulltextItemCount is around 2.2 crores.
Then we done rebuilt the FT catalog Index. then the TableFulltextItemCount became 0.Again we run the containstable query ,but still it is not getting results.
As the no of rows are very more . so i am not able to show the actual rows from which the data is not coming.
the below query gives 2 results that are from actual base table
select * from g_case_action_log where cas_details like '%235355%' and product_id = 38810
SELECT Distinct top 50 cal.case_id,cal.cas_details From g_case_action_log cal (READUNCOMMITTED)inner join containstable(es.g_case_action_log, cas_details, ' ("235355" OR "<br>235355" OR "235355<br> ") ') as key_tbl on cal.log_id = key_tbl.[key] Where cal.product_id = 38810 ORDER By cal.case_id DESC
Can anyone suggest any idea why it is not returning results all the time.
08-06-12, 15:11 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
This is pure "air code", but give it a try and let me know if it works:Code:
SELECT DISTINCT TOP 50 cal.case_id, cal.cas_details FROM g_case_action_log cal (READUNCOMMITTED) INNER JOIN ContainsTable (es.g_case_action_log , cas_details, '(235355)') as key_tbl on cal.log_id = key_tbl.[key] WHERE cal.product_id = 38810 ORDER BY cal.case_id DESCIn theory, theory and practice are identical. In practice, theory and practice are unrelated.