Tech Tip: How to identify composite indexes

PRODUCT: 4D | VERSION: 11 | PLATFORM: Mac & Win

Published On: September 24, 2010

In 4D v11 SQL, you can get information about indexes by querying the _USER_INDEXES system table. You will notice though that the INDEX_TYPE column will display the value 1 for both BTree and Composite indexes. So if you are looking for composite indices, this field cannot be used as an indicator.

In order to detect a composite index, you can perform a join with the _USER_IND_COLUMNS system table. In this table, if an index is compisite there will be 2 (or more) records in this table. Also, the records will have the same INDEX_ID and this can be used to identify which records make up a composite index.

For example, here is a query that can be run to gather information about indexes and display the information in a list box.

Begin SQL
   SELECT UI.INDEX_ID, UI.INDEX_NAME, UI.INDEX_TYPE, UIC.COLUMN_ID,       UIC.COLUMN_POSITION
   FROM _USER_INDEXES AS UI, _USER_IND_COLUMNS AS UIC
   WHERE UI.INDEX_ID = UIC.INDEX_ID
   ORDER BY UI.INDEX_NAME
   INTO :lbSysTbl
End SQL

Here is an example of the results:

In these results, you can see that "Index 1" and "Index 4" both have 2 records, which means they are compisite indices. Also, notice that they share the same INDEX_ID.

Commented by Jody Bevan on September 26, 2011 at 3:35 PM

This has little value to me from what I can see. I need to know what composite indexes I have in the system. Since these are so important in v12. We need something where we can easily look to see what composite indexes we have.

We also need a way to learn what searches our users are performing so that we can know where we need to create composite indexes.

Jody