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

[Peter Griffioen]

"MySQL will almost never use two INDEXes in a single query."
Now that I never knew! And this may change my approach to liberal indexing.

The flags have all sorts of info such as clouds present, pixel quality, atmospheric corrections applied and things like Land, Shallow Water, Ice, Ocean etc. There are 16 bits present, some in groups.

My software allows the user to select any of these. Eg Quality level 5 or better, No Clouds, Atmospheric correction < 2 out of 4 levels. So that the VIQA 'where' part may end up somthing like (actual numbers may be wrong):

"Where (VIQA && 60 <=20) AND (VIQA && 1024 = 1024) AND ((VIQA && 15359 = 6144) OR (VIQA && 15359 = 8192))

Rather than do all these tests I thought it would be better to work out which, out of all of the unsigned ints that are present (about 150 combos), met these criteria and then use :

"where VIQA IN (1236, 2215, 2671, 2899 etc)"

However I assumed that MYSQL would use the VIQA index (intersected with the PointId index) and thus this would be quite quick even though it is messy. I'll run some tests tommorrow as I've almost finished the code and user interface.