Difference between single index and combined index

Community Alums
Not applicable

‎09-02-2020 05:57 AM

Hi,

 

I have a requirement to have indexing on 2 fields of a table, but I am not sure should I have use separate indexing for the fields or have combined indexing? How to analyse which indexing should be used when? and if we have separate indexing then is there a need to have combined indexing as well? Please help.

Labels:
0 Helpfuls
  • 4,272 Views
3 REPLIES 3

Prasant Kumar 1
Kilo Sage

‎09-13-2020 01:26 PM

Hi,

You can find Indexes on the "Database Indexes" related list on the Base table sys_db_object record.

Different Type of Indexes in ServiceNow:-
 
a) Unique Index: 
Preserves Data integrity, no two records can have same values on the field where Unique Index is defined.
 
b) Non-Unique Index: 
This doesn't preserve Data Integrity. however, can help in faster search/queries.
 
c) Composite Index: 
We can combine multiple fields to be included as a Single Index. These can be Unique/ Non-Unique indexes.
 
 
If i was able to solve your query, Please mark my answer correct and helpful.
 
Thanks & Regards
Prasant kumar sahu

Hitoshi Ozawa
Giga Sage
Giga Sage

‎09-13-2020 03:21 PM

Whether to use separate index or composite (combined) index depends on your record and your need.

If composite columns needs to be unique, then use composite index and set it to be unique. A composite index will also be faster if one of the column has many duplicate rows. However, composite index will only be used when both columns are included in a query. If only one column is queried, composite index will not be used.

If there is a situation where only one of the column is queried, it would be better to create index for each column. However, as I've stated above, if each column by itself is not unique and have many duplicate entries, having separate index would be slower than having a composite index.

0 Helpfuls