Greetings.
I came to this hesitation when I need for a reprocess to a massive table.
I have an index on (F1, D2) and need to
FOR LAST Table NO-LOCK WHERE Table.Field1 = cVar1 // part of index AND Table.Date2 <= dVar2 // part of index AND Table.FieldX = "SomeValue" // NOT index: ...
I wonder, will it be fast? where will be resolved the query, on server? on client? this is because that table has some millions records..
TIA
Jorge
As I suspected.... It takes a really big time to run...
Investigating, this code is not the culprit....
it is:
for each table
where field1 <= dateX
and field2 <= dateY
and field3 = "somevalue":
....
end.
and have at best those indexes:
A (field1)
B (fiedl2)
C (another-field, field3) .
So found out there is no suitable bracketing on this matter ...
Considering index D (Field3, Field1, Field2)...
What you say???
Thanks...
Hello Octavio, I have never seen formal documentation on this matter, but based on some experience evaluating this stuff I believe the queries you showed us will be resolved on the server. It will envolve lots of reads and residual filtering but the million records won't transfer to the client.
About your proposed D index, make it D(field3, fieldX), where fieldX is the most selective of field1 and field2. No point in adding both columns for this query unless you think the index can be used elsewhere.