This project has moved and is read-only. For the latest updates, please go here.

Interesect primary and secondary index

Feb 24, 2011 at 10:32 PM
Edited Feb 24, 2011 at 10:34 PM

I have a table with an ID column (32-bit primary key, autoincrement) and a Data column (64-bit). I would like to select records where the ID column is less than or equal to X and the Data column is between Y and Z (inclusive). There is a primary key index on ID and an index on Data. Neither of the ranges on their own is very restrictive and would result is enumerating a lot of ignored records. Right now I set the range over Data and retrieve the ID value from the primary index and ignore anything greater than X.

What is the best way to accomplish this? Note that I can change the database schema/index definitions as needed.

Feb 25, 2011 at 2:34 AM

If you have two different secondary indexes then you can use JetIntersect indexes. See the IntersectIndexes method in the sample app (http://managedesent.codeplex.com/wikipage?title=StockSample).

In your case one of the columns is the primary key so there is a more efficient way to do this: enumerate the secondary index for all items where the "data" column is less than the defined value and retrieve the ID column. When you retrieve the ID column use RetrieveColumnGrbit.RetrieveFromPrimaryBookmark. That will get the ID column without touching the primary index. The secondary index will be very dense soyou won't do a lot of I/O.

Feb 25, 2011 at 4:46 AM

That's what I figured. That is exactly how I implemented it, but I was wondering if I was missing something. Thank you for your help.