Index ZipIDX On ZipCode [ Data = (City, State) ];

Here, the index is built on the ZipCode field, and the City and State fields are included in the Data portion of the index structure (i.e., stored as part of the index global). When a query retrieves only City and State based on a ZipCode filter, the SQL engine can perform an index-only scan and avoid accessing the main "Master map". This behavior is similar to a covering index in SQL databases because the requested data is fully available within the index itself.

SELECT City, State FROM Sample.Person WHERE ZipCode = 19904;

In this case, the index global might look like:

^gblI("ZipIDX", ZipCode, ID) = $LB("", City, State)

The query will be satisfied using just the index, avoiding a full table (master map) access.

However, this index does not provide any benefit when the query uses City or State as filter conditions, since those fields are not part of the index key. So, It will search on the "Master map"

Now, consider another index:

Index CityStateIDX On (City, State);

This is a composite index (or multi-column index), where both City and State are stored as part of the index key (subscripts), not as index data. The global would look like:

^gblI("CityStateIDX", City, State, ID) = "

SELECT state FROM SAMPLE.PERSON1 WHERE city ='Newton'

When a query uses City or State in the WHERE clause, the index is used to locate the matching IDs, but the SQL engine must then perform a lookup in the master map to fetch any other columns, even if you're selecting only indexed fields like City or State.

So, composite indexes help filter rows efficiently, but they don't function like covering indexes unless the query involves only index keys and you don't need to retrieve other columns.

Here is the Index types documentation

Thank you, @Dmitry Maslennikov, for your incredible journey and inspiring contributions to the community. Wishing you continued success and endless learning ahead!

Thanks for the quick response on the issue — improving the clarity of the error message will definitely help.