| List: | General Discussion | « Previous MessageNext Message » | |
| From: | Wendy Liu | Date: | October 5 1999 10:08pm |
| 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
-------end of query statement----------------------------------------------------
the table OV_Raw is as follows:
---------------------+----------------------------------------------------+--------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default
| Extra |
+----------------------------------------------------+--------------+------+-----+---------------------+-------+
| Originator | varchar(32) | YES | | NULL
| |
| StationId | varchar(16) | YES | | NULL
| |
| OwnerDistributorId | varchar(16) | YES | | NULL
| |
| DownlinkLocation | varchar(255) | YES | | NULL
| |
| DataCollectionUnitId | varchar(255) | YES | | NULL
| |
| ImageAccessClassification | varchar(255) | YES | | NULL
| |
| Satellite | varchar(255) | | |
| |
| Sensor | varchar(255) | YES | | NULL
| |
| SensorMode | varchar(255) | | |
| |
| RadiometricOption | varchar(255) | YES | | NULL
| |
| UniqueImageId | varchar(64) | | PRI |
| |
| MediaId | varchar(16) | YES | | NULL
| |
| FileId | varchar(8) | YES | | NULL
| |
| DatasetId | varchar(64) | YES | | NULL
| |
| FileNumber | int(11) | YES | | NULL
| |
| AcquisitionStartTime | datetime | YES | | NULL
| |
| AcquisitionEndTime | datetime | YES | | NULL
| |
| OrbitNumber | int(11) | YES | | NULL
| |
| OrbitSense | char(1) | YES | | NULL
| |
| OrbitElements | varchar(255) | YES | | NULL
| |
| MapProjection | varchar(255) | YES | | NULL
| |
| MapZone | int(11) | YES | | NULL
| |
| Hemisphere | char(1) | YES | | NULL
| |
| EarthEllipsoid | varchar(255) | YES | | NULL
| |
| SampleSpacing | double(16,4) | YES | | NULL
| |
| BrowseImageFilePath | varchar(255) | YES | | NULL
| |
| FullImagePath | int(11) | YES | | NULL
| |
| FullImageRow | int(11) | YES | | NULL
| |
| FullImageStartTime | datetime | | MUL |
0000-00-00 00:00:00 | |
| FullImageEndTime | datetime | | MUL |
0000-00-00 00:00:00 | |
| FullImageCentreTime | datetime | | |
0000-00-00 00:00:00 | |
| FullImageCentreLocationLatitude | double(16,4) | YES | | NULL
| |
| FullImageCentreLocationLongitude | double(16,4) | YES | | NULL
| |
| FullImageCornersUpperLeftLatitude | double(16,4) | | | 0.0000
| |
| FullImageCornersUpperLeftLongitude | double(16,4) | | | 0.0000
| |
| FullImageCornersUpperRightLatitude | double(16,4) | | | 0.0000
| |
| FullImageCornersUpperRightLongitude | double(16,4) | | | 0.0000
| |
| FullImageCornersLowerRightLatitude | double(16,4) | | | 0.0000
| |
| FullImageCornersLowerRightLongitude | double(16,4) | | | 0.0000
| |
| FullImageCornersLowerLeftLatitude | double(16,4) | | | 0.0000
| |
| FullImageCornersLowerLeftLongitude | double(16,4) | | | 0.0000
| |
| FullImageImageOrientation | double(16,4) | YES | | NULL
| |
| FullImageDataQualityPercentMissingData | double(16,4) | YES | | NULL
| |
| FullImageOpticalInfoOffNadirAngle | double(16,4) | YES | | NULL
| |
| FullImageOpticalInfoIncidenceAngle | double(16,4) | YES | | NULL
| |
| FullImageOpticalInfoAzimuthAngle | double(16,4) | YES | | NULL
| |
| FullImageOpticalInfoSunAzimuth | double(16,4) | YES | | NULL
| |
| FullImageOpticalInfoSunElevation | double(16,4) | YES | | NULL
| |
| FullImageOpticalInfoPixelSpacing | double(16,4) | YES | | NULL
| |
| FullImageOpticalInfoLineSpacing | double(16,4) | YES | | NULL
| |
| FullImageOpticalInfoCloudCoverStatisticsPercentage | int(11) | YES | | NULL
| |
| FullImageOpticalInfoCloudCoverStatisticsConfidence | int(11) | YES | | NULL
| |
+----------------------------------------------------+--------------+------+-----+---------------------+-------+
There is almost no time difference whether or not I added some index ( I tried to add
index to various number of columns.)
Here is the variables:
+----------------------------+------------------------------------------------------------------------------+
| Variable_name | Value
|
+----------------------------+------------------------------------------------------------------------------+
| back_log | 5
|
| connect_timeout | 5
|
| basedir | /user/people/jjobson/projecthome/cots/POTS/mysql/bin/../
|
| datadir | /cudos_dev/data/database/
|
| delayed_insert_limit | 100
|
| delayed_insert_timeout | 300
|
| delayed_queue_size | 1000
|
| join_buffer | 131072
|
| flush_time | 0
|
| key_buffer | 8388600
|
| language |
/user/people/jjobson/projecthome/cots/POTS/mysql/bin/../share/mysql/english/ |
| log | OFF
|
| log_update | OFF
|
| long_query_time | 10
|
| low_priority_updates | OFF
|
| max_allowed_packet | 1048576
|
| max_connections | 100
|
| max_connect_errors | 10
|
| max_delayed_insert_threads | 20
|
| max_join_size | 4294967295
|
| max_sort_length | 1024
|
| net_buffer_length | 16384
|
| pid_file | /cudos_dev/data/database/dds01.pid
|
| port | 3316
|
| protocol_version | 10
|
| record_buffer | 131072
|
| skip_locking | OFF
|
| skip_networking | OFF
|
| socket | /tmp/mysql.sock2
|
| sort_buffer | 2097144
|
| table_cache | 64
|
| thread_stack | 65536
|
| tmp_table_size | 1048576
|
| tmpdir | /var/tmp/
|
| version | 3.22.23b
|
| wait_timeout | 28800
|
+----------------------------+------------------------------------------------------------------------------+
Any advice or clue on how to improve the query performance by adding index or any other
ways.
Wenhong Liu
www.mda.ca
| Thread | ||
|---|---|---|
| • tuning query prformance by add index - why no improvements | Wendy Liu | 6 Oct |
| • Re: tuning query prformance by add index - why no improvements | Tonu Samuel | 6 Oct |