【SQL】【SELECT】テーブル内のレコードの異なるカラムのフィールド値を比較して一致するデータを抽出したい

Question

前提・実現したいこと

SQLのデータ抽出についての質問です。
同テーブル内のレコードの内、同じカラムのフィールド値が一致するかつ異なるカラムのフィールドの値が一致するレコードを全て抽出したいです。
上記内容をテーブルから取得できるクエリをご教授いただけないでしょうか。

データの取得条件

「CONCAT(test.name1, test.name2)が一致するレコード」
かつ
「CONCAT(test.h_phone1, test.h_phone2, test.h_phone3) とCONCAT(test.m_phone1, test.m_phone2, test.m_phone3)の組み合わせで一致するレコード」

環境

OS：CentOS7
DB：MySQL5.7

※前提としてテーブルには10万件ちかいレコードが入っています。

試したこと・発生している問題

実際にした事としては、サブクエリでテーブル内のレコードを全件取得した後にWHERE句で条件で絞り込む方法です。
テーブルが100件ほどであれば時間がかかる事なく問題なく取得できましたが、
10万件近いデータで試したところパフォーマンスが悪くタイムアウトエラーが発生してしましました。
目安としては10分以内で結果を取得できるクエリが望ましいです。

テーブルのデータ構造

testテーブル
| id | name_1 | name2 | h_phone1 | h_phone2 | h_phone3 | m_phone1 | m_phone2 | m_phone3 |
| ---- | ---- |
| 1 | 佐藤 | 太郎 | 000 | 1111 | 2222 | 000 | 3333 | 4444 |
| 2 | 佐藤 | 太郎 | 000 | 1111 | 2222 | 000 | 1111 | 2222 |
| 3 | 佐藤 | 太郎 | 000 | 3333 | 4444 | 000 | 3333 | 4444 |
| 4 | 佐藤 | 次郎 | 000 | 1111 | 2222 | 000 | 1111 | 2222 |
| 5 | 佐藤 | 三郎 | 000 | 1111 | 2222 | 000 | 3333 | 4444 |

実際に発行したクエリ

SELECT
    main_test.id,
    CONCAT(main_test.name1, main_test.name2) AS main_name,
    CONCAT(sub_test.name1, sub_test.name2) AS sub_name,
    CONCAT(
        main_test.h_phone1,
        main_test.h_phone2,
        main_test.h_phone3
    ) AS main_h_phone,
    CONCAT(
        sub_test.m_phone1,
        sub_test.m_phone2,
        sub_test.m_phone3
    ) AS sub_m_phone,
    CONCAT(
        sub_test.h_phone1,
        sub_test.h_phone2,
        sub_test.h_phone3
    ) AS sub_h_phone,
    CONCAT(
        main_test.m_phone1,
        main_test.m_phone2,
        main_test.m_phone3
    ) AS main_m_phone
FROM
    test AS main_test,
    (
        SELECT
            test.name1,
            test.name2,
            test.h_phone1,
            test.h_phone2,
            test.h_phone3,
            test.m_phone1,
            test.m_phone2,
            test.m_phone3
        FROM
            test
        WHERE
            -- 「h_phoneが全てNULL・空ではない」もしくは「m_phoneが全てNULL・空ではない」
            (
                (
                    (
                        test.h_phone1 IS NOT NULL
                        AND test.h_phone2 IS NOT NULL
                        AND test.h_phone3 IS NOT NULL
                    )
                    AND (
                        test.h_phone1 != ""
                        AND test.h_phone2 != ""
                        AND test.h_phone3 != ""
                    )
                )
                OR (
                    (
                        test.m_phone1 IS NOT NULL
                        AND test.m_phone2 IS NOT NULL
                        AND test.m_phone3 IS NOT NULL
                    )
                    AND (
                        test.m_phone1 != ""
                        AND test.m_phone2 != ""
                        AND test.m_phone3 != ""
                    )
                )
            )
    ) AS sub_test
WHERE
    -- 「main_h_phone、sub_m_phone」もしくは「sub_h_phoneとmain_m_phone」の組み合わせ
    CONCAT(main_test.name1, main_test.name2) = CONCAT(sub_test.name1, sub_test.name2) 
    AND (
        (
            CONCAT(
                main_test.h_phone1,
                main_test.h_phone2,
                main_test.h_phone3
            ) = CONCAT(
                sub_test.m_phone1,
                sub_test.m_phone2,
                sub_test.m_phone3
            )
        )
        OR (
            CONCAT(
                sub_test.h_phone1,
                sub_test.h_phone2,
                sub_test.h_phone3
            ) = CONCAT(
                main_test.m_phone1,
                main_test.m_phone2,
                main_test.m_phone3
            )
        )
    )

Comments

[takanaweb5]

サンプルデータに対して欲しい結果はどうなるのでしょうか？

[ShiraPi]

コメントありがとうございます。
欲しい結果としては、「名前が同じで自宅電話番号と携帯電話番号の組み合わせでどちらか片方が一致したデータ」です。
よろしくお願いします。

「名前と名前が一致している」かつ「自宅電話番号と携帯電話番号の組み合わせ2つの内どちらか片方が一致している」レコードです。
※自宅電話番号と携帯電話番号の組み合わせのパターンとしては、「自宅電話番号×携帯電話番号が一致しているか」「携帯電話番号×自宅電話番号が一致しているか」の2つあります。

Answer 1

CREATE VIEW 電話帳 AS
SELECT 
  id
, CONCAT(name1, name2) AS name
, CONCAT(h_phone1,h_phone2,h_phone3) AS h_phone
, CONCAT(m_phone1,m_phone2,m_phone3) AS m_phone
FROM TEST;

CREATE VIEW 重複一覧_sub AS
SELECT name, h_phone as phone, 1 as chk FROM 電話帳
UNION 
SELECT name, m_phone as phone, 2 as chk FROM 電話帳;

CREATE VIEW 重複一覧 AS
SELECT name, phone
FROM 重複一覧_sub 
GROUP BY name, phone
HAVING SUM(chk) = 3;

SELECT * FROM 重複一覧

name	phone
佐藤太郎	011112222
佐藤太郎	033334444
佐藤次郎	011112222

これは、同一人物における自宅電話番号と携帯電話番号に重複して使用されている番号の一覧になります。
欲しい情報は、これではないですか？

上記のSQLの実行にかかった時間はどれぐらいでしょうか？
また、対象となったレコードの件数はいくらでしょうか？

おそらくお望みの、上記の番号の使用されているレコードの一覧は以下のSQLになります。

SELECT
  Z.NAME
, A.ID
, A.h_phone
, A.m_phone
, B.ID
, B.h_phone
, B.m_phone
FROM 重複一覧 AS Z
INNER JOIN 電話帳 AS A
   ON Z.NAME = A.NAME AND Z.phone = A.h_phone
INNER JOIN 電話帳 AS B
   ON Z.NAME = B.NAME AND Z.phone = B.m_phone

Answer 2

こんばんわ。

技術要件だけ書いてあるので、

あなたが何に困っているのか、どういうデータが欲しいのか、

それがいまいちよくわからないのです。

記載されたSQL文を拝見しましたが、

testテーブルの直積になっているので重くて当然です。

概算で 10万件 × 10万件 = 100億件です。

さらに、sub_testは中間表ですから、インデックスが効きません。

こういったところを改善しない限り、期待するパフォーマンスが出ることはないでしょう。

最後に、提供されたテストデータに対してSQLを実行した結果を載せます。

これがあなたの望んだ結果に相違ないでしょうか？

id   main_name  sub_name  main_h_phone   sub_m_phone    sub_h_phone    main_m_phone
1    佐藤太郎    佐藤太郎    00011112222    00011112222    00011112222    00033334444
1    佐藤太郎    佐藤太郎    00011112222    00033334444    00033334444    00033334444
2    佐藤太郎    佐藤太郎    00011112222    00033334444    00011112222    00011112222
2    佐藤太郎    佐藤太郎    00011112222    00011112222    00011112222    00011112222
3    佐藤太郎    佐藤太郎    00033334444    00033334444    00011112222    00033334444
3    佐藤太郎    佐藤太郎    00033334444    00033334444    00033334444    00033334444
4    佐藤次郎    佐藤次郎    00011112222    00011112222    00011112222    00011112222

コメントを受けて考えたクエリ

select 
    main_test.id as main_id,
    sub_test.id as sub_id,
    concat(
        main_test.name1,
        main_test.name2
    ) as main_name,
    concat(
        sub_test.name1,
        sub_test.name2
    ) as sub_name,
    concat(
        main_test.h_phone1,
        main_test.h_phone2,
        main_test.h_phone3
    ) as main_h_phone,   
    concat(
        sub_test.m_phone1,
        sub_test.m_phone2,
        sub_test.m_phone3
    ) as sub_m_phone,
    concat(
        sub_test.h_phone1,
        sub_test.h_phone2,
        sub_test.h_phone3
    ) as sub_h_phone,
    concat(
        main_test.m_phone1,
        main_test.m_phone2,
        main_test.m_phone3
    ) as main_m_phone
from test as main_test inner join test as sub_test 
on (main_test.name1 = sub_test.name1 and main_test.name2 = sub_test.name2 and 
      ( (main_test.h_phone1 = sub_test.m_phone1 and main_test.h_phone2 = sub_test.m_phone2 and main_test.h_phone3 = sub_test.m_phone3 ) or 
        (main_test.m_phone1 = sub_test.h_phone1 and main_test.m_phone2 = sub_test.h_phone2 and main_test.m_phone3 = sub_test.h_phone3 )
      )
    )

Comments

[ShiraPi]

コメントありがとうございます。
欲しい結果としては、「名前が同じで自宅電話番号と携帯電話番号の組み合わせでどちらか片方が一致したレコード」です。
よろしくお願いします。

「名前と名前が一致している」かつ「自宅電話番号と携帯電話番号の組み合わせ2つの内どちらか片方が一致している」レコードです。
※自宅電話番号と携帯電話番号の組み合わせのパターンとしては、「自宅電話番号×携帯電話番号が一致しているか」「携帯電話番号×自宅電話番号が一致しているか」の2つあります。

[srsnsts]

返信ありがとうございます。
要件は理解できました。
testテーブルの定義を変更することはできないとのことですが、
同じスキーマに別のテーブルを追加することはできますか？

[ShiraPi]

同じスキーマに別テーブルを追加する事は可能です。
一時的に使用するテーブル追加するイメージでしょうか。
また大変図々しく恐縮ですが、
同じスキーマ内に別テーブルを追加せずに取得する方法もご検討いただく事可能でしょうか。
よろしくお願いします。

[srsnsts]

お返事ありがとうございます。

> 一時的に使用するテーブル追加するイメージでしょうか。

そうですね、一時テーブルを作ってインデックスを貼って、ってことを考えてました。
まぁ一時テーブルの利用はデータの二重持ちの原因になりますので、
最後の手段かなぁぐらいに考えてました。
別テーブルを追加しない方向性了解です。
もう少し考えてみます。

[srsnsts]

10万件のデータに対し有効かどうかはわかりませんが、
考えたクエリがあるので回答に追記します。

[ShiraPi]

ありがとうございます！
よろしくお願い致します。

[srsnsts]

私の回答の
「コメントを受けて考えたクエリ 」
というのがそれです。
ご確認下さい。

[ShiraPi]

解決できました。
因みに10万件ほどデータがある環境で実行したところ、ONの中でカラムをCONCATして条件に加えると処理が遅くなる事確認できました。
ありがとうございました。

[srsnsts]

解決したようで何よりです。

> ONの中でカラムをCONCATして条件に加えると処理が遅くなる事確認できました。

そうですね、インデックスが効かなくなるので遅くなるでしょうね。

Answer 3

10万件近いデータで試したところパフォーマンスが悪くタイムアウトエラーが発生してしましました。
目安としては10分以内で結果を取得できるクエリが望ましいです。

インデックスを適用してみるというのが、一番の方法ですが、それが出来ない場合、時間の短縮は難しいと思います。

なので、
・タイムアウトにならない程度に件数を区切って処理する
・一時テーブルに一旦データを出力し、その一時テーブルにインデックスを適用して処理する。
など。
後者の方が、早くなる可能性は高いです

Answer 4

テーブル構成はいじれるのでしょうか？
生成列を使えばインデックスを貼れるので効率的に検索ができそうですが

追記

テーブルがいじれないのでは意味がありませんが、参考までに

create table tbl(
id int,
name_1 varchar(10),
name_2 varchar(10),
h_phone1 varchar(10),
h_phone2 varchar(10),
h_phone3 varchar(10),
m_phone1 varchar(10),
m_phone2 varchar(10),
m_phone3 varchar(10)
);
insert into tbl(id,name_1,name_2,h_phone1,h_phone2,h_phone3,m_phone1,m_phone2,m_phone3) values
(1,'佐藤','太郎','000','1111','2222','000','3333','4444'),
(2,'佐藤','太郎','000','1111','2222','000','1111','2222'),
(3,'佐藤','太郎','000','3333','4444','000','3333','4444'),
(4,'佐藤','次郎','000','1111','2222','000','1111','2222'),
(5,'佐藤','三郎','000','1111','2222','000','3333','4444');

に対して生成列を追加してインデックスを貼る

alter table tbl add name varchar(20) as (concat(name_1,name_2));
alter table tbl add h_phone varchar(20) as (concat(h_phone1,h_phone2,h_phone3));
alter table tbl add m_phone varchar(20) as (concat(m_phone1,m_phone2,m_phone3));
alter table tbl add  index(name,h_phone,m_phone);

Comments

[ShiraPi]

テーブル定義は変更できませんが、
SELECTする際に列を作成したりすることはOKです。
※知識不足で見当違いな事を言っていたら申し訳ありません。

[yambejp]

追記しましたがそもそもが元のテーブルをいじれないならインデックスも設定できないでしょうし
クエリーだけでは高速化はきびしいでしょうね