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 |
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.
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
;
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
© 2024 OneMinuteCode. All rights reserved.