SQLでDistinctをExistに置き換えたい
検索結果のクエリ速度を上げるため、DistinctをExistに置き換えたいのですが、自分には複雑でうまく置き換えができませんでした。
2つあるのですが、下記となります。
尚、計測したところ合計で約2.5秒ほどかかっています。(wp_postmetaは30万件)
1つ目
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'
2つ目
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
)
どうぞよろしくお願いいたします。
post_metaの構造ですが、ワードプレス標準のものをそのまま利用しており下記となっています。
【post_metaテーブル】(1つ目の参考に抜粋しました。meta_idがプライマリとなります。)
| meta_id | post_id | meta_key | meta_value |
| 100000 | 3120 | mardorisu | 4 |
| 100001 | 3120 | mardorisyurui | 10 |
【期待する結果】(1つ目の抜粋)
| mardorisu | mardorisyurui |
| 1 | 50 |
| 2 | 50 |
| 1 | 10 |
| 1 | 55 |
| 3 | 30 |