I want to replace Distinct with Exist in SQL

Asked 2 months ago, Updated 2 months ago, 6 views

To speed up the query of the search results, I wanted to replace Distinct with Exist, but it was too complicated for me to replace it well.
There are two, but they are as follows.
Also, when I measured it, it took about 2.5 seconds in total.(300,000 wp_postmeta)

First

SELECT DISTINCT
   PM.meta_value AS madorisu,
   PM_2.meta_value AS madorisyurui 
from
   (
       (
           (
                wp_posts ASP 
                INNER JOIN wp_postmeta AS PM 
                OPP.ID = PM.post_id
           ) 
           INNER JOIN wp_postmeta ASPM_1
           ON P.ID = PM_1.post_id
       ) 
       INNER JOIN wp_postmeta ASPM_2
       ON P.ID = PM_2.post_id
   )
WHERE
    P.post_status='publish' 
AND P.post_password='" 
AND P.post_type='fudo' 
ANDPM_1.meta_key='bukkenshubetsu' 
AND CAST (PM_1.meta_value AS SIGNED) > 3000 
ANDPM.meta_key='madorisu' 
ANDPM_2.meta_key='madorisyurui'

Second

SELECT DISTINCT
   DTR.rosen_name,
   DTR.rosen_id,
   DTS.station_name,
   DTS.station_id,
   DTS.station_ranking  
from
   (
       (
           (
               (
                   (
                       (
                           wp_posts ASP
                        )
                        INNER JOIN wp_postmeta AS PM 
                        OPP.ID = PM.post_id
                    )  
                    INNER JOIN wp_postmeta ASPM_1
                    ON P.ID = PM_1.post_id
                )
                INNER JOIN wp_postmeta ASPM_2 
                ON P.ID = PM_2.post_id
            )  
            INNER JOIN wp_train_rosen AS DTR
            ON CAST (PM_1.meta_valueAS SIGNED) = DTR.rosen_id
        )  
        INNER JOIN wp_train_station AS DTS
        ON DTS.rosen_id = DTR.rosen_id 
        AND CAST (PM.meta_valueAS SIGNED) = DTS.station_id
   )
WHERE
    (
        P.post_status='publish'  
        AND P.post_password='"  
        AND P.post_type='fudo'  
        ANDPM.meta_key = 'koutsueki1'  
        ANDPM_1.meta_key='koutsurosen1'  
        ANDPM_2.meta_key='bukkenshubetsu'  
        ANDPM_2.meta_value>3000 
    )
OR(
        P.post_status='publish'  
        AND P.post_password='"  
        AND P.post_type='fudo'  
        ANDPM.meta_key = 'koutsueki2'  
        ANDPM_1.meta_key='koutsurosen2'  
        ANDPM_2.meta_key='bukkenshubetsu'  
        ANDPM_2.meta_value>3000 
    )

I look forward to your kind cooperation.

Regarding the structure of post_meta, I use the word press standard as it is, and it is as follows.

[post_meta table] (excerpt for the first reference.meta_id is the primary.)

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

[Expected Results] (1st Excerpt)

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

mysql sql phpmyadmin

2022-09-30 11:15

3 Answers

The use of the Exists clause is often a set of correlation subqueries, which often results in poor performance.I think it would be a better plan to focus on avoiding OR.

By the way, is the index used?

(300,000 wp_postmeta)

Therefore, I think the neck is wp_postmeta, so if you create a covering index of post_id and meta_key in wp_postmeta, the query performance will improve.


2022-09-30 11:15

Your reply has been updated.

Assumed conditions: meta_key madorisyurui and madorisu appear only once per post.Most of the published posts are published.

The point is
1. Since the number of lines explodes as wp_posts xwp_postmeta xwp_postmeta xwp_postmeta in the original query of the questioner, wp_postmeta was first read once in the sub-SELECT and only madirisyurui, madorisu were extracted according to the conditions of bukenshubetsu.
2. Most posts are published, so I assume that there is little to throw away in the line extracted above.If the assumption is the other way around, it is also good to consider using post_id to select metadata first.
3. Group_concat is used to convert vertical tables to horizontal tables.

select
        pm3.madorisyurui,
        pm3.madorisu
from
        wp_posts asp 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 aspm2join
                (
                select
                        post_id
                from
                        wp_postmeta
                where
                        meta_key = 'bukkenshubetsu' AND
                        CAST (meta_value AS SIGNED) > 3000
                aspm1
                ON pm1.post_id=pm2.post_id
        where
                meta_key in ('madorisyurui', 'madorisu')
        group by 1
        aspm3
        ON(
           p.id=pm3.post_id and
           p.post_status='publish' and
           p.post_password='AND
           p.post_type='fudo'
           )
group by 1,2
;

The best query depends on the number of rows and nature of the data, and there is no way to verify it, so I don't know if it's a good answer, but please let me know if it doesn't suit your purpose or if it'If I get a reply, I will think about that 2.

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 asp join
        (
        select
                pm2.post_id as post_id,
                meta_key,
                meta_value
        from
                wp_postmeta aspm2join
                (
                select
                        post_id
                from
                        wp_postmeta
                where
                        meta_key = 'bukkenshubetsu' AND
                        CAST (meta_value AS SIGNED) > 3000
                aspm1
                ON pm1.post_id=pm2.post_id
        where
                meta_key in ('madorisyurui', 'madorisu')
        aspm3
        ON(
           p.id=pm3.post_id and
           p.post_status='publish' and
           p.post_password='AND
           p.post_type='fudo'
           )
group by p.id
;


2022-09-30 11:15

I'm sorry to give you a different answer.
I wanted to clarify the requirements of the query for the questioner, so I tried shaping the query, but is this correct?

1)

 -- List of combinations of `madorisu` and `madorisyurui` linked to the article
SELECT DISTINCT
   madorisu.meta_value AS madorisu,
   madorisyui.meta_value AS madorisyui 
FROM wp_posts AS post 
INNER JOIN wp_postmeta AS bukenshubetsu ON post.ID=bukkenshubetsu.post_id AND bukenshubetsu.meta_key='bukenshubetsu'
INNER JOIN wp_postmeta AS madorisu ON post.ID=madorisu.post_id AND madorisu.meta_key='madorisu' 
INNER JOIN wp_postmeta AS madorisyui ON post.ID=madorisyurui.post_id AND madorisyui.meta_key='madorisyui'
WHERE
    post.post_status='publish' 
AND post.post_password='" 
AND post.post_type='fudo' 
AND CAST (bukkenshubetsu.meta_value AS SIGNED) > 3000 

2)

 -- List of combinations of `route` and `station` linked to the article
SELECT 
   rosen.rosen_name,
   rosen.rosen_id,
   station.station_name,
   station.station_id,
   station.station_ranking  
FROM(
    -- First, the combination of rosen_id and station_id is uniquely narrowed down.
    SELECT DISTINCT
        CAST (koutsurosen.meta_value AS SIGNED) ASrosen_id,
        CAST (koutsueki.meta_value AS SIGNED) ASstation_id
    FROM wp_posts AS post
    INNER JOIN wp_postmeta AS bukenshubetsu ON post.ID=bukkenshubetsu.post_id AND bukenshubetsu.meta_key='bukenshubetsu'
    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 ASrosen ON t.rosen_id =rosen.rosen_id
INNER JOIN wp_train_station AS station ON t.station_id = station.station_id ANDrosen.rosen_id = station.rosen_id


2022-09-30 11:15

If you have any answers or tips


© 2022 OneMinuteCode. All rights reserved.