MySQLのjoin結合でINDEXを有効にさせたい

Question

前提・実現したいこと

MySQL8.0を利用し、下記のテーブルPolyがあります。なお、そのレコード数は約24万です。

# Input table
CREATE TABLE IF NOT EXISTS Poly (
	id INTEGER AUTO_INCREMENT,
	prop int(11),#各ポリゴンの属性
	geom POLYGON NOT NULL,
    PRIMARY KEY (id),
	SPATIAL KEY `geom`(geom));
# Output table
CREATE TABLE IF NOT EXISTS child_parent (
	child INTEGER NOT NULL,
	parent INTEGER NOT NULL,
	geom_c POLYGON NOT NULL,# child のgeom
    PRIMARY KEY (child ,parent),
	SPATIAL KEY `geom_c`(geom_c));

実現したいことは、このテーブルから、ポリゴン属性が互いに同じで且つ隣接しているポリゴンの組み合わせを新たなテーブルchild_parentとして出力することです。
最終的な目的は、ポリゴン属性が互いに同じで且つ隣接しているポリゴンを一つのポリゴンにまとめたテーブルを作ることですが、まず、この段階で時間がかかることを解決したいのです。

発生している問題・エラーメッセージ

問題は、レコード数が大きいので、実行時間が多大（40時間でlost connection）にかかることです。
希望は、数時間で終わることを期待しています。

該当のソースコード

最初に実行したSQLです。（40時間でlost connection）
なお、TEMPORARYテーブルは一つのSQLの中で、2回以上は呼び出すことができないというので、Polyとまったく同じものを二つ（Poly_a、Poly_b）用意し、その二つをjoinしています。

INSERT INTO child_parent(child,parent,geom_c) 
select w.id,w.parent,w.geom from 
(SELECT 
	a.id as id,
	b.id as parent,
	a.geom as geom 
from Poly_a as a
join Poly_b as b
WHERE 
    a.id > b.id and # 自分同士と同じ組み合わせは重複するので不要
    a.prop = b.prop and # ポリゴンの属性が同じものだけ
    ST_GeometryType(ST_UNION(a.geom,b.geom)) = 'POLYGON' # 隣接するものだけ
) as w
group by w.id;#最終的な目的を考えて、w.idでまとめる。

試したこと

時間がかかるので、explainでキーがどのように有効になっているかを以下の通り、確認しました。

explain
select w.id,w.parent,w.geom from 
(SELECT 
	a.id as id,
	b.id as parent,
	a.geom as geom 
from Poly_a as a
join Poly_b as b
WHERE 
    a.id > b.id and 
    a.prop = b.prop and 
    ST_GeometryType(ST_UNION(a.geom,b.geom)) = 'POLYGON' 
) as w
group by w.id;

| id| select_type| table| partitions| type| possible_keys| key| key_len| ref| rows| filtered| Extra|
|'1'| 'SIMPLE'| 'b'| NULL| 'ALL'| 'PRIMARY'| NULL| NULL| NULL| '248008'| '100.00'| 'Using temporary'|
|'1'| 'SIMPLE'| 'a'| NULL| 'ALL'| 'PRIMARY,geom_a'| NULL| NULL| NULL| '248097'| '0.33'| 'Using where; Using join buffer (Block Nested Loop)'|

結果を見ると、テーブルaのpossible_keysは'PRIMARY,geom_a'となっており、PKとSpatialkeyとが含まれています。しかし、テーブルbのpossible_keysにはが含まれていません。filteredも100になっていて、全てが対象になっている模様です。
なお、テーブルaとテーブルbには、それぞれSPATIAL KEYgeom_a,geom_bを設定しています。

そこで、USE INDEX (PRIMARY,geom_a)、USE INDEX (PRIMARY,geom_b)を使ってみましたが、結果は同じでした。
下記のように、FORCE INDEX()を使ったところ、少し結果が異なりましたが、恐らく時間短縮は見込めないと思われます。

explain
select w.id,w.parent,w.geom from 
(SELECT 
	a.id as id,
	b.id as parent,
	a.geom as geom 
from Poly_a as a FORCE INDEX (PRIMARY,geom_a)
join Poly_b as b FORCE INDEX (PRIMARY,geom_b)
WHERE 
    a.id > b.id and 
    a.prop = b.prop and 
    ST_GeometryType(ST_UNION(a.geom,b.geom)) = 'POLYGON' 
) as w
group by w.id;

| id| select_type| table| partitions| type| possible_keys| key| key_len| ref| rows| filtered| Extra|
|'1'| 'SIMPLE'| 'a'| NULL| 'ALL'| 'PRIMARY,geom_a'| 'PRIMARY'|4| NULL| '248097'| '100.00'| NULL|
|'1'| 'SIMPLE'| 'b'| NULL| 'ALL'| 'PRIMARY'| NULL| NULL| NULL| '248008'| '0.33'| 'Range checked for each record (index map: 0x1)'|

補足情報

MySQLのバージョン：GPL 8.0.12
OS:Windows 7 Professional 64bit
RAM:8.0GB

Comments

[yambejp]

> TEMPORARYテーブルは一つのSQLの中で、2回以上は呼び出すことができない

Polyは普通のテーブルのように見えますがテンポラリなのですか？
わざわざテンポラリをつくることなく自己結合してはいけないのでしょうか？

[ochiaishiro]

ありがとうございます。
実際は、Stored Procedureの中で処理することを考えています。この処理は、全体の処理の一部のため、処理中に他のセッションからは見えない、また、終了したら消えるTEMPORARYとしました。ただ、質問に当たっては、普通のテーブルとして書きました。
問題は、TEMPORARYかどうかではなく、効率的なjoinを行えないかということです。

[yambejp]

Polyを各テンポラリに流し込むのですね？
テンポラリを作成するときにきちんとテンポラリ自体にも
Polyと同等のインデックスは貼ってありますか？

[ochiaishiro]

はい、同じようにPKとインデックスを作成しています。

Answer 1

式インデックスを適用されてみてはどうでしょう。
MySQL 8.0.13の式インデックス

Comments

[ochiaishiro]

面白そうですね。試してみます。ありがとうございました。

[sazi]

よく見ると結合した別テーブルの項目使用してるので、使えないですね。
後検討するとしたら再帰ですかね

[ochiaishiro]

再帰クエリー（CTE)は、この後の処理で使用しています。
この段階の結果として隣接関係を取得し、それを親子関係とみなして、CTEでまとめています。

[ochiaishiro]

joinの結果が(n**2)/2とnが大きくなると指数的に大きくなるので、全体を適当な数になるまで分割するしかないですかね？
他に解決策がなければ、分割して処理します。

[sazi]

単なるWITH式ではなくRECURSIVE付の再帰という事です。
https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive

[ochiaishiro]

はい、この後の処理でRECURSIVE付の再帰を行っています。
前処理のこの段階では、CTEとその中のサブクエリー部でのJOINする親子関係の情報をどうしたらいいか、理解できないでいます。
そこで、さらに前処理で、元のテーブルを「ポリゴン属性が互いに同じ」でgroupbyして、各グループのカウントを求め、さらにカウントの積算を求めるのにRECURSIVE付の再帰を利用し、適当なサイズごとに元のテーブルを分割抽出することを考えています。