List:General Discussion« Previous MessageNext Message »
From:Frederik Lindberg Date:October 5 1999 10:31pm
Subject:tuning query prformance by add index - why no improvements
View as plain text  
>I query the mysql database using:
>
>select         OV_Raw.UniqueImageId
>from            OV_Raw
>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"
>limit   3000

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
time.

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
this?

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 ;-)

--Sincerely, Fred
Thread
tuning query prformance by add index - why no improvementsFrederik Lindberg6 Oct