Table Indexes - fdlGitHub/ServiceNow GitHub Wiki

TBD

Limited to 64 indexes, growing up to 128 indexes with recent MariaDB version

Quantity of fields Maximum number of indexes
1 1
2 2
3 6
4 24
5 120
6 720

Combined indexes

Why combining indexes?

Single indexed fields could to not provide the required efficiency in query execution, due to the huge amount of data matching the condition.

In SQL words, it means that the cardinality is too low.

To increase the cardinality and so the efficiency of queries, we have to make combined indexes.

In example, for a given table with a million of data, if the condition to retrieve data is something like "Type is Automation AND Created on This month", where type is a choice list with Automation and Manual as options and where "Type is Automation" is representing 95% of all the data, if we are having a single index on Type, the query will not be more efficient than without index because 95% of the data are having the same type value.

However, if we are indexing Type And Created together, then, the query will be efficient

No matter of the query, the user... the query will always be efficient

Combine 2 fields

List of indexes:

  • field_name_1, field_name_2
  • field_name_2, field_name_1

Combine 3 fields

List of indexes:

  • field_name_1, field_name_2, field_name_3
  • field_name_1, field_name_3, field_name_2
  • field_name_2, field_name_1, field_name_3
  • field_name_2, field_name_3, field_name_1
  • field_name_3, field_name_1, field_name_2
  • field_name_3, field_name_2, field_name_1

Combine 4 fields

List of indexes:

  • field_name_1, field_name_2, field_name_3, field_name_4
  • field_name_1, field_name_2, field_name_4, field_name_3
  • field_name_1, field_name_3, field_name_2, field_name_4
  • field_name_1, field_name_3, field_name_4, field_name_2
  • field_name_1, field_name_4, field_name_2, field_name_3
  • field_name_1, field_name_4, field_name_3, field_name_2
  • field_name_2, field_name_1, field_name_3, field_name_4
  • field_name_2, field_name_1, field_name_4, field_name_3
  • field_name_2, field_name_3, field_name_1, field_name_4
  • field_name_2, field_name_3, field_name_4, field_name_1
  • field_name_2, field_name_4, field_name_1, field_name_3
  • field_name_2, field_name_4, field_name_3, field_name_1
  • field_name_3, field_name_1, field_name_2, field_name_4
  • field_name_3, field_name_1, field_name_4, field_name_2
  • field_name_3, field_name_2, field_name_1, field_name_4
  • field_name_3, field_name_2, field_name_4, field_name_1
  • field_name_3, field_name_4, field_name_1, field_name_2
  • field_name_3, field_name_4, field_name_2, field_name_1
  • field_name_4, field_name_1, field_name_2, field_name_3
  • field_name_4, field_name_1, field_name_3, field_name_2
  • field_name_4, field_name_2, field_name_1, field_name_3
  • field_name_4, field_name_2, field_name_3, field_name_1
  • field_name_4, field_name_3, field_name_1, field_name_2
  • field_name_4, field_name_3, field_name_2, field_name_1