

検索結果のクエリ速度を上げるため、DistinctをExistに置き換えたいのですが、自分には複雑でうまく置き換えができませんでした。  
2つあるのですが、下記となります。  
尚、計測したところ合計で約2.5秒ほどかかっています。(wp_postmetaは30万件)

1つ目

[code]

    SELECT DISTINCT
       PM.meta_value AS madorisu,
       PM_2.meta_value AS madorisyurui 
    FROM
       (
           (
               (
                    wp_posts AS P 
                    INNER JOIN wp_postmeta AS PM 
                    ON P.ID = PM.post_id
               ) 
               INNER JOIN wp_postmeta AS PM_1
               ON P.ID = PM_1.post_id
           ) 
           INNER JOIN wp_postmeta AS PM_2
           ON P.ID = PM_2.post_id
       )
    WHERE
        P.post_status = 'publish' 
    AND P.post_password = '' 
    AND P.post_type = 'fudo' 
    AND PM_1.meta_key = 'bukkenshubetsu' 
    AND CAST( PM_1.meta_value AS SIGNED ) > 3000 
    AND PM.meta_key = 'madorisu' 
    AND PM_2.meta_key = 'madorisyurui'
    
[/code]

2つ目

[code]

    SELECT DISTINCT
       DTR.rosen_name,
       DTR.rosen_id,
       DTS.station_name,
       DTS.station_id,
       DTS.station_ranking  
    FROM
       (
           (
               (
                   (
                       (
                           (
                               wp_posts AS P
                            )
                            INNER JOIN wp_postmeta AS PM 
                            ON P.ID = PM.post_id
                        )  
                        INNER JOIN wp_postmeta AS PM_1
                        ON P.ID = PM_1.post_id
                    )
                    INNER JOIN wp_postmeta AS PM_2 
                    ON P.ID = PM_2.post_id
                )  
                INNER JOIN wp_train_rosen AS DTR
                ON CAST( PM_1.meta_value AS SIGNED ) = DTR.rosen_id
            )  
            INNER JOIN wp_train_station AS DTS
            ON  DTS.rosen_id = DTR.rosen_id 
            AND CAST( PM.meta_value AS SIGNED ) = DTS.station_id
       )
    WHERE
        (
            P.post_status = 'publish'  
            AND P.post_password = ''  
            AND P.post_type = 'fudo'  
            AND PM.meta_key = 'koutsueki1'  
            AND PM_1.meta_key = 'koutsurosen1'  
            AND PM_2.meta_key = 'bukkenshubetsu'  
            AND PM_2.meta_value > 3000 
        )
    OR  (
            P.post_status = 'publish'  
            AND P.post_password = ''  
            AND P.post_type = 'fudo'  
            AND PM.meta_key = 'koutsueki2'  
            AND PM_1.meta_key = 'koutsurosen2'  
            AND PM_2.meta_key = 'bukkenshubetsu'  
            AND PM_2.meta_value > 3000 
        )
    
[/code]

どうぞよろしくお願いいたします。

post_metaの構造ですが、ワードプレス標準のものをそのまま利用しており下記となっています。

【post_metaテーブル】（１つ目の参考に抜粋しました。meta_idがプライマリとなります。）

| meta_id | post_id | meta_key | meta_value |  
| 100000 | 3120 | mardorisu | 4 |  
| 100001 | 3120 | mardorisyurui | 10 |

【期待する結果】（１つ目の抜粋）

| mardorisu | mardorisyurui |  
| 1 | 50 |  
| 2 | 50 |  
| 1 | 10 |  
| 1 | 55 |  
| 3 | 30 |


CC BY-SA 3.0
2018-01-06T03:10:45.917
40763
2018-06-24T02:53:27.973
2018-01-10T15:57:53.310
23921
23921
question
mysql sql phpmyadmin
SQLでDistinctをExistに置き換えたい


`Exists` 句の使用は相関副問合せとセットとなる事が多いのですが、そうするとパフォーマンスは悪化する事の方が多いです。それよりも `OR`
を避ける事に注力する方が良いプランになるように思います。

ところで、インデックスは使用されているでしょうか。

> (wp_postmetaは30万件)

との事でネックは `wp_postmeta` の方だと思いますので、`wp_postmeta` に `post_id` 及び `meta_key`
のカバリングインデックスを作成するとクエリパフォーマンスの向上が見込めると思います。


CC BY-SA 3.0
2018-01-07T13:41:02.357
40800
2018-01-07T13:41:02.357
26808
40763
answer


_返信をいただいたので回答を更新。_

# ver 0.2

想定条件: meta_key
madorisyuruiとmadorisuはpost一つにつきそれぞれ一度しか現れない。publishされているpostが全体のうち大半を占める。

ポイントとしては  
1\. 質問者の元のクエリではwp_posts x wp_postmeta x wp_postmeta x
wp_postmetaとなって行数が爆発するから、先にサブSELECTで、wp_postmetaを一度だけ読み込んでbukkenshubetsuの条件に従ってmadirisyurui,madorisuだけ抽出した。  
2\.
ほとんどのpostsはpublishされているので上で抽出した行で捨てるものはほとんど無い想定。もし想定が逆なら先にpost_idでメタデータのSELECTを絞ることも考えると良い。  
3\. group_concatしているのは縦のテーブルを横のテーブルに変換するため。

[code]

    select
            pm3.madorisyurui,
            pm3.madorisu
    from
            wp_posts as p join
            (
            select
                    pm2.post_id as post_id,
                    group_concat(case when meta_key='madorisyurui' then meta_value end) as madorisyurui,
                    group_concat(case when meta_key='madorisu' then meta_value end) as madorisu
            from
                    wp_postmeta as pm2 join
                    (
                    select
                            post_id
                    from
                            wp_postmeta
                    where
                            meta_key='bukkenshubetsu' AND
                            CAST( meta_value AS SIGNED ) > 3000
                    ) as pm1
                    ON pm1.post_id=pm2.post_id
            where
                    meta_key in ('madorisyurui', 'madorisu')
            group by 1
            ) as pm3
            ON (
               p.id = pm3.post_id and
               p.post_status = 'publish' and
               p.post_password = '' AND
               p.post_type = 'fudo'
               )
    group by 1,2
    ;
    
[/code]

# ver 0.1

最適なクエリーはデータの行数、性質によりますし、検証のしようがないので、目的にあっているのかもわかりませんが、とりあえずの回答です。目的に合わない、速度が遅いと言うことであればご指摘ください。返信があればその2についても考えてみます。

[code]

    select
            p.id,
            group_concat(case when pm3.meta_key='madorisyurui' then pm3.meta_value end order by 1) as madorisyurui,
            group_concat(case when pm3.meta_key='madorisu' then pm3.meta_value end order by 1) as madorisu
    from
            wp_posts as p join
            (
            select
                    pm2.post_id as post_id,
                    meta_key,
                    meta_value
            from
                    wp_postmeta as pm2 join
                    (
                    select
                            post_id
                    from
                            wp_postmeta
                    where
                            meta_key='bukkenshubetsu' AND
                            CAST( meta_value AS SIGNED ) > 3000
                    ) as pm1
                    ON pm1.post_id=pm2.post_id
            where
                    meta_key in ('madorisyurui', 'madorisu')
            ) as pm3
            ON (
               p.id = pm3.post_id and
               p.post_status = 'publish' and
               p.post_password = '' AND
               p.post_type = 'fudo'
               )
    group by p.id
    ;
    
[/code]


CC BY-SA 3.0
2018-01-08T03:06:14.477
40807
2018-01-10T22:54:11.847
2018-01-10T22:54:11.847
7837
7837
40763
answer


別回答の形で恐縮です。  
質問者さんのクエリの要件を明確にしたかったのでクエリを整形してみたのですが、この認識で相違ないでしょうか？

1)

[code]

    -- 記事に紐づく、`madorisu` と `madorisyurui` の組合せ一覧
    SELECT DISTINCT
       madorisu.meta_value AS madorisu,
       madorisyurui.meta_value AS madorisyurui 
    FROM wp_posts AS post 
    INNER JOIN wp_postmeta AS bukkenshubetsu ON post.ID = bukkenshubetsu.post_id AND bukkenshubetsu.meta_key = 'bukkenshubetsu'
    INNER JOIN wp_postmeta AS madorisu       ON post.ID = madorisu.post_id AND madorisu.meta_key = 'madorisu' 
    INNER JOIN wp_postmeta AS madorisyurui   ON post.ID = madorisyurui.post_id AND madorisyurui.meta_key = 'madorisyurui'
    WHERE
        post.post_status = 'publish' 
    AND post.post_password = '' 
    AND post.post_type = 'fudo' 
    AND CAST(bukkenshubetsu.meta_value AS SIGNED) > 3000 
    
[/code]

2)

[code]

    -- 記事に紐づく、`路線`と`駅`の組合せ一覧
    SELECT 
       rosen.rosen_name,
       rosen.rosen_id,
       station.station_name,
       station.station_id,
       station.station_ranking  
    FROM (
        -- 先に rosen_id と station_id の組合せで一意に絞り込む。
        SELECT DISTINCT
            CAST(koutsurosen.meta_value AS SIGNED) AS rosen_id,
            CAST(koutsueki.meta_value AS SIGNED) AS station_id
        FROM wp_posts AS post
        INNER JOIN wp_postmeta AS bukkenshubetsu ON post.ID = bukkenshubetsu.post_id AND bukkenshubetsu.meta_key = 'bukkenshubetsu'
        INNER JOIN wp_postmeta AS koutsueki      ON post.ID = koutsueki.post_id
        INNER JOIN wp_postmeta AS koutsurosen    ON post.ID = koutsurosen.post_id
        WHERE
            post.post_status = 'publish'  
        AND post.post_password = ''  
        AND post.post_type = 'fudo'  
        AND bukkenshubetsu.meta_value > 3000
        AND (
            koutsueki.meta_key = 'koutsueki1' AND koutsurosen.meta_key = 'koutsurosen1'  
            OR
            koutsueki.meta_key = 'koutsueki2' AND koutsurosen.meta_key = 'koutsurosen2'
        )
    ) t
    INNER JOIN wp_train_rosen AS rosen     ON t.rosen_id = rosen.rosen_id
    INNER JOIN wp_train_station AS station ON t.station_id = station.station_id AND rosen.rosen_id = station.rosen_id
    
[/code]


CC BY-SA 3.0
2018-01-10T23:32:03.223
40873
2018-01-10T23:32:03.223
26808
40763
answer