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)
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
By the way, is the index used?
Therefore, I think the neck is
wp_postmeta, so if you create a covering index of
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.
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.
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?
© 2022 OneMinuteCode. All rights reserved.