Re: .ini settings for ADD KEY to 30Gb table on 96Gb machine?

[Peter Griffioen]

Ah yes, the Blue Rd etc. Range from -3000 to 10000. Really are Floats, divide by 10000 for value.

And for the flags (VIQA), there are quite a few flags consisting of single, 2, 3, and 4 bits. eg; here are some values:
Name bits start Value &Value Meaning
--------------------------------------------------------
VI Usefulness 4 3 0000 0 Highest quality
VI Usefulness 4 3 0001 4 Lower quality 1
VI Usefulness 4 3 0010 8 Lower quality 2
VI Usefulness 4 3 0011 12 Lower quality 3
VI Usefulness 4 3 0100 16 Lower quality 4
VI Usefulness 4 3 0101 20 Lower quality 5
VI Usefulness 4 3 0110 24 Lower quality 6
VI Usefulness 4 3 0111 28 Lower quality 7
VI Usefulness 4 3 1000 32 Lower quality 8
VI Usefulness 4 3 1001 36 Lower quality 9
VI Usefulness 4 3 1010 40 Lower quality 10
VI Usefulness 4 3 1011 44 Lower quality 11
VI Usefulness 4 3 1100 48 Lowest Quality
VI Usefulness 4 3 1101 52 Quality so low that it is not useful
VI Usefulness 4 3 1110 56 L1B data faulty
VI Usefulness 4 3 1111 60 Not useful for any other reason/not processed


I'm thinking instead of querying:

Select PointId, AVG(Red), AVG(Blue)... from ModisPixelStack
Where PointId >= NN and PointId <= MM
AND ModisTileLoadId >= T1 AND ModisTileLoadID <= T2
AND (VIQA & WantedFlags = WantedFlags)
Group by PointId Order by PointId;

I thought that once all the data is loaded to make a table of all VIQA unsigned Int values using:

Insert INTO TABLE FlagsList Select VIQA from ModisPixelStack Group by VIQA;

and then using that unique list to determine which VIQA values are required:

Select VIQA From FlagList where (VIQA & WantedFlags = WantedFlags);

and then using that List of VIQA values as a IN statement in the above query. This would avoid the "AND (VIQA & WantedFlags = WantedFlags)", which could be complex and involve quite a few ORs and ANDs, on every row in the huge ModisPixelStack table. This would then make use of the VIQA index that has been created for that table.