Changing the order of Text Indexing

[davesmithqldhea]

We have an issue when we clone Production to a sub production instance with the regeneration of the text indexes. The Number for the Index on the Task table is before just about every other table and to regenerate the indexes for that task table takes about 3 days.

This means the search function (IR_AND_OR_QUERY) for Knowledge doesn't work for the first 3 days after the clone. Servicenow have locked down changing the Number field to the maint role. We can add an ACL to enable Admins to change the Number but will this have an unknown impact?

[Mwatkins · Accepted solution]

Hi Dave,

        Actually that might cause some issues. The ts_index_name.number field is the field that is used to map the table being searched to the text index "shard" table (the ones that start with ts_c_ that store the actual mapping between documents and word roots). So for the task table, if the number is 7, then it will map to the "shards" that have names starting with "ts_c_7_". If you change the number then the logic will be off and the search engine will look in the wrong shard. I am not sure what this would look like in the UI. Probably the result would be that nothing would be displayed in the search results since it would be trying to query sys_id's in a table that they don't belong to.

        Aside from this there isn't any reason that changing the number would be a problem. So, you should be able to get what you want by changing the numbers and then completely re-indexing everything. So this would mean a re-index on production, since it is the clone source. Once you finish the re-index on production things ought to be good to go. I'd definitely test the procedure on a sub-prod first, but it should work if your users can put up with the search function not working in production for the 3 days it takes to re-index.

Regards, Matthew