I’m curious about what the ODB 3.0 query optimizer would do in a case like this:
- The target class has multiple non-unique indexes defined that cover various combinations of fields:
index1 includes fields a, b, c; index2 includes fields d, e, f, and index3 includes fields g, h, i
- The query includes fields in the MATCH or WHERE clause that include these fields:
WHERE (a=1 AND b=2) AND (d=11 AND e=22 AND F=33) AND (g=111 AND H=222 and I>333)(parenthesis shown only for clarity)
- Does the optimizer:
- a) Choose one of the three indices, use it to filter down the list of possible records, and then scan that list of records, comparing the remaining values (i.e. ignoring the other indices)?
- b) Run queries on each of the three indices, then “map/reduce” the RIDs in each of the three result sets to get the final set of records?
- c) Something entirely different?
What if additional fields were included in the MATCH or WHERE clause that were not covered by any index?
I’m assuming that if index2 was a unique index (the only index that is complete, with all-equals operators), the optimizer would choose it and go find the record, then check to make sure it also satisfied the other fields in the query, but otherwise ignore the other indices?
The purpose of my question is to better understand how to optimize query performance by building a useful index strategy. If ODB 3.0 will ever only use one particular index in a given query, that would lead me in a different direction than if it will make use of multiple indices “at once”.