Hi,
I'm trying to work out how multiple indexes work and wondered if I could have some guidance.
e.g. Say you have records A, B, C, D and E in a table.
You run a for each using an index 1 and the index information already knows that A, B and C satisfy the query.
You run a second for each using index 2 and you get back A and E.
Now...
I know that if you use index 1 and a random field on the end of the query then the query will go through A and B and C and only pass back the ones that satisfy the last record query.
If you then run a for each using index 1 and index 2 what happens?
Does it go through A, B and C (index 1) and only return A (becuase A is in index 2)?
Does it go through A and E (index 2) and only return A (because A is in index 1)?
Or does it return A with no iteration at all (i.e. it cross references both indexes to get the intersect of them)?
Thanks for any help
When multiple indices are used ... and read the rules *carefully* about when that happens ... it is selecting record ids, not records, and comparing which are in both lists.
Ah, that makes sense.
Thanks!
Don't get too excited about multiple indexes... they have their uses but they are not a panacea. In particular don't fall into the trap of thinking that you can replace all of your composite indexes with single field indexes that will magically combine to provide the same results. (They won't.)
Don't worry
I didn't have any plans about remodelling etc...
Just got into a discussion about how it would work with a few colleagues and wanted some answers
Thanks!
Oliver
P.S. I saw someone asking about combining single field indexes in another thread so didn't bother asking again