Configuration Item lookup on Incident form is slow to load

[Chris K1]

Our organisation introduced a custom field (true/false) on the cmdb_ci table named u_selectable. This was created so that CI owners can set this value to 'false' (via self-service catalogue) to avoid it being selectable in an Incident, Outage, Change, etc.

This field is also used in a few Dictionary Entry Overrides on the Task table, so when the Task is an Incident, the CI lookup uses the following...

Reference Qualifier: operational_status!=2^operational_status!=6^u_selectable=true

Currently, when end users click the magnifying glass on the Incident's cmdb_ci field, the time it takes the Configuration Item popup to load is ~45 seconds. This was previously at ~60 seconds but has been reduced with help on indexing from Now Support. They've told us that the query and indexing cannot be improved further. Admittedly our cmdb_ci table contains over 10 million records.

However, we've noticed that when we remove the u_selectable field in the condition, like the following...

Reference Qualifier: operational_status!=2^operational_status!=6

...the popup loads within 8 seconds and returns more records.

Does anyone have any insight as to why this is occurring? 

Do I need to tell those who rely on this custom field that its responsible for the slow load times and potentially a process re-design is needed?

[Weird]

Hmm, sounds peculiar. Perhaps Servicenow didn't include the u_selectable field in the indexing?
You should check with them again.
There's also a way to handle indexing yourself, thought I'm not too familiar with it.

Create a table index (servicenow.com)

[Chris K1]

Regarding the indexing, the query was previously using a composite index on Columns: (u_selectable,operational_status,sys_class_path) which was taking more time. 

Currently this query is using index only on column: u_selectable which has slightly reduced (60 sec down to 45 sec) the SQL processing time.