During troubleshooting an issue, it is very hard to identify when and who created the DB index on the instance. If ServiceNow create the index, there will be a valid HI change associated with this. But when customer create the index, it is hard to backtrack.
mysql/mariadb doesn't have any easy way to give us this information and it is not there in information_schema as well. There are multiple forums discussing this query on the internet from a DB level.
From an instance perspective, we have an easy way to identify this from one of the tables which will help us with troubleshooting.
PS: if ServiceNow create an index( via Change management) from the DB backend, this might not be recorded here.
Go to the following table on instance.
Search with the table name and the "Alter Type" as "Create Index(es)" as per the screenshot. If multiple indexes were created together, there will be only 1 entry with all the fields.
If it is a UNIQUE index, u: prefix (marked in the screenshot) will be there for the field.
Updated* fields will give information on when and who created this.
If this index creation was part of an update-set or a installed app, it will be with the name "system" and you can backtrack the created timestamp to Commit timestamp of an update-set to figure out how this came over.
Screenshot is attached.
Also something to note:
When creating an index via the UI you will select the field name from the UI, in some cases the field from the front end could be alias to another field on the database and this is stored in they sys_storage_alias table. In this table you will have
Element Name : This is the name of the field on the front end
Storage Alias : this is the field on the backend database that holds this data.
This is important as when you look at the database indexes related list on the table it will show the storage alias name and not the element name.
So in this example the front end field is incident_state but the backend field on the database is a_int_2 so the database indexes list will show the alias name.