When specifying an index why the expression is used?

[frenyibeni]

Hi All!

I was checking the ChooseIndex function and i noticed, that the expression is used when adding index by using either groupBy or OrderBy. (QueryOptimization.cs l. 282)
Wouldn't it make sense to use the name of the index itself instead?

BR,
F.

[mbdavid]

Hi @frenyibeni,

The ideia behind expressions (created in v5) is create a generic way to create index based in any document field, method call or any transformation you need to be indexed. But in SQL, you create SELECTs and engine will choose best index to use. To select the best index, index name are not used because are just "the name of index" and not "what data are indexed". Like this:

If you create an index like this

engine.EnsureIndex("mycollection", "Name", "UPPER($.Name)"

All index keys will contains only UPPER strings of Name field. If you try run a query like this:

SELECT $ FROM mycollection WHERE $.Name = 'JOHN'

Index (Name) will not be used. Only if you run

SELECT $ FROM mycollection WHERE UPPER($.Name) = 'JOHN'

Now, index expression are the same are query expression: UPPER($.Name). So, this will be the best index to be use in this query.

In this code:

            // if no index found, try use same index in orderby/groupby/preferred
            if (lowest == null && (_query.OrderBy != null || _query.GroupBy != null || preferred != null))
            {
                var index =
                    indexes.FirstOrDefault(x => x.Expression == _query.GroupBy?.Source) ??
                    indexes.FirstOrDefault(x => x.Expression == _query.OrderBy?.Source) ??
                    indexes.FirstOrDefault(x => x.Expression == preferred);

                if (index != null)
                {
                    lowest = new IndexCost(index);
                }
            }

The variable lowest is "the best index I found". But could be null. If null, let's check if this query has GroupBy or OrderBy and collection contains an index for this GroupBy/OrderBy expression, use this index. If not, returns null and engine will use default index: PK (_id field).

And the last try is use preferred index (if exists). Prefered index are valid to avoid unnecessey data document lookup. Like this query:

SELECT UPPER($.Name) FROM mycollection

If I have an index in UPPER($.Name) I can resolve this query without load any document (use only index lookup).