There is a table (wp_postmeta), the structure is as follows:

id post_id key value 1 11 fromcity_1 Москва 2 11 fromdistrict_1 Садовое кольцо 3 11 fromcity_2 Воронеж 4 11 fromdistrict_1 Коминтерновский 5 13 fromcity_2 Воронеж 6 13 fromdistrict_1 Северный

And there is such a request:

  SELECT `post_id` FROM `wp_postmeta` AS p WHERE p.`key` LIKE 'fromcity_%' AND p.`value` = 'Москва' AND EXISTS ( SELECT 1 FROM wp_postmeta AS c WHERE c.post_id = p.post_id AND c.`key` = concat('fromdistrict_', substring(p.`key`, 9)) AND c.`value` = 'Садовое кольцо' )

In theory, it should return post_id , where fromcity_1 = Вороклини , and fromdistrict_1 = Центр , but not working.
At the same time, the number 1 in keys is ID , and in keys fromcity_ and fromdistrict_ ID must match, which I do here:

 AND c.`key` = concat('fromdistrict_', substring(p.`key`, 9))

But, apparently, I somehow incorrectly use the substring, since it does not work ...

  • Do not do this ... And the answer to your question: 10. For Yuri Ivanov
  • And how to do it?) And I have done everything the way you gave the link: concat ('fromdistrict_', substring (p. key , 9)) p. key = fromcity_1. So in the substring(p. Key , 9)) only the number 1 should remain. - RattleSneyk
  • Not. 10. select substring ('fromcity_1', 1) => 'fromcity_1'; select substring ('fromcity_1', 2) => 'romcity_1'; select substring ('fromcity_1', 3) => 'omcity_1'; select substring ('fromcity_1', 4) => 'mcity_1'; select substring ('fromcity_1', 5) => 'city_1'; select substring ('fromcity_1', 6) => 'ity_1'; select substring ('fromcity_1', 7) => 'ty_1'; select substring ('fromcity_1', 8) => 'y_1'; select substring ('fromcity_1', 9) => '_1'; Again. Your request will put the server. Do not do this. How many posts do you have? With 1000 posts, the query will be executed in seconds. - Yura Ivanov
  • Well, so how to do? - RattleSneyk

1 answer 1

You have an EAV pattern in which the filters are implemented like this:

 select p.id from posts p left join postmeta pm1 on p.id = pm1.post_id and pm1.key = 'fromcity_1' left join postmeta pm2 on p.id = pm2.post_id and pm2.key = 'fromdistrict_1' left join postmeta pm3 on p.id = pm3.post_id and pm3.key = 'fromcity_2' left join postmeta pm4 on p.id = pm4.post_id and pm4.key = 'fromdistrict_2' where (pm1.value = 'Москва' and pm2.value = 'Садовое кольцо') or (pm3.value = 'Москва' and pm4.value = 'Садовое кольцо');

Such a request is formed in the code, in a loop by properties.
This is not very nice and not very fast (there should be correct indexes, I hope they are in wordpress), but at least it’s set to a constant number of queries. As for the other options, they are probably also possible, but not the fact that they will be faster (or more correctly) as a request that you are currently using.

  • > there should be correct indexes only for post_id and non-participating meta_id - etki
  • @Etki, it is possible to speed up a query with just another index, by (key, value), although it is too expensive in terms of the size and speed of insertion. If post_id is indexed, then there will be a full postscall guarantee, here it will depend on the amount of data (after all, a blog / forum is not an online store with 100k products and 50 characteristics each). - Yura Ivanov
  • Of course, I had in mind that the default WordPress has only these indices, it has worked out ambiguously. - etki