私のテーブルには約300万件のレコードがあります。このクエリを実行すると、カウント値をフェッチするのに約15〜30秒かかります
SELECT COUNT(*) AS `neighbours_count` FROM house
WHERE
( 6371 *
acos(
cos( radians( "48.70877900" ) ) *
cos( radians( `map_y` ) ) * cos( radians( `map_x` ) -
radians( "37.49893200" ) ) + sin( radians( "48.70877900" )
)
* sin( radians( `map_y` ) ) )
) <= 0.3
クエリ自体は、特定の座標から300メートル以内の建物をカウントします。6371は地球半径で、残りの部分は座標への近接度を計算する式です。
クエリの説明：
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE house ALL NULL NULL NULL NULL 2442710 Using where
ステートメントの作成：
CREATE TABLE IF NOT EXISTS `house` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`country_id` int(11) NOT NULL,
`state_id` int(11) NOT NULL,
`city_id` int(11) NOT NULL,
`street_id` int(11) NOT NULL,
`name` varchar(250) NOT NULL,
`map_x` decimal(11,8) NOT NULL,
`map_y` decimal(11,8) NOT NULL,
UNIQUE KEY `id` (`id`),
KEY `country_id` (`country_id`),
KEY `city_id` (`city_id`),
KEY `street_id` (`street_id`),
KEY `map_x` (`map_x`),
KEY `map_y` (`map_y`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='map_y - latitude, map_x - longitude' AUTO_INCREMENT=2442769 ;
このクエリを最適化する方法についてアドバイスをお願いします。
クエリを実際に最適化する方法はいくつかあります。1つの方法は、条件を追加することです。
where (max_x between A and B and max_y between C and D) and
. . .
これに伴う問題は、との両方max_yでインデックスを使用できないことですmax_x。バリエーションは、各側に300メートルのグリッドを課し、各ポイントを最も近いグリッドポイントに移動することです。これは実装するのが少し面倒です（トリガーなどが必要です）。しかし、あなたはあなたの状態が物事が隣接するグリッドポイントにあることを意味することを知っています。だから、このようなもの：
where (grid_x, grid_y) in ((grid_A-1, grid_B), (grid_A-1, grid_B-1), (grid_A-1, grid_B+1),
(grid_A, grid_B), (grid_A, grid_B-1), (grid_A, grid_B+1),
(grid_A+1, grid_B), (grid_A+1, grid_B-1), (grid_A+1, grid_B+1)
) and
. . .
これは上のインデックスを利用することができgrid_x, grid_yます-私は思います。そうでない場合は、を使用して同じ効果を得ることができますunion all。
本当に、最良のオプションは地理空間拡張です。GISクエリは、一般的なリレーショナルデータベースデータとは異なるデータ構造を使用するため、拡張機能が必要です。
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加