>I query the mysql database using:
>where OV_Raw.Satellite = "OV-3" and
> OV_Raw.SensorMode = "PAN1" and
> OV_Raw.FullImageCornersUpperLeftLongitude > -89.000000 and
> OV_Raw.FullImageCornersUpperRightLongitude < -81.000000 and
> OV_Raw.FullImageCornersUpperRightLatitude < 48.000000 and
> OV_Raw.FullImageCornersLowerRightLatitude > 40.000000 and
> OV_Raw.FullImageCentreTime between "2000-01-01" and "2000-01-15"
First, I would replace the VARCHAR(255) for satellite and SensorMode
with a TINYINT and have a separate table for names. Then use a join with
the [very small] satellite/SensorMode tables or go in with the numbers
directly (i.e. if you select from a GUI/WWW). This will save a lot of
Second, I'd make sure that the index fits into the keybuffer, i.e. can
be in memory all at once. It's hard to interpret your keybuffer size
without knowing the table size.
Third, your FullImageCorners criteria may require a full table scan (or
your SensorMode/Satellite may select such a large subset of the info
that a table scan is needed. Does increasing record_buffer help for
Fourth, your FullImageCorners criteria are open ended. Thus, an index
may not help for UpLeftLong > -81, because each criterion selects a very
large number of records. I suspect that you could do:
UpLeftLong between -89 AND -81 AND UpRighLong between -89 AND -81 ...
and save a lot of time with an index in the 4 image corners.
I'd also make the UniqueImageId numeric. A VARCHAR(64) as a PRIMARY KEY
makes for slow insert/delete. Do you need to have it as a primary key?
Finally, there are isamcheck things to do to optimize indices depending
on stats and data distribution, but I know nothing of that ;-)
|• tuning query prformance by add index - why no improvements||Frederik Lindberg||6 Oct|