I am developing a Wordpress store using the ACF plugin (custom fields). All the parameters of the goods are stored in his meta. accordingly, when filtering in the catalog, I have to do a JOIN table with meta data many times to select all the necessary parameters. The menu in the catalog is dynamic and is compiled depending on what parameters the product has.

Help optimize queries to the database generating the menu, now they look like this:

SELECT vozrat_koshek.post_id,vozrat_koshek.meta_value FROM wp_posts t1 JOIN wp_term_relationships ON wp_term_relationships.object_id=t1.ID JOIN wp_postmeta as vozrat_koshek ON vozrat_koshek.post_id=t1.ID LEFT JOIN wp_postmeta as brend ON brend.post_id = t1.ID AND brend.meta_key='ΠΏΡ€ΠΎΠΈΠ·Π²ΠΎΠ΄ΠΈΡ‚Π΅Π»ΡŒ' AND (brend.meta_value='137' ) LEFT JOIN wp_postmeta as klass_korma ON klass_korma.post_id = brend.post_id AND klass_korma.meta_key='класс_ΠΊΠΎΡ€ΠΌΠ°' AND (klass_korma.meta_value='premium' ) JOIN wp_postmeta as osobye_potrebnosti ON osobye_potrebnosti.post_id = klass_korma.post_id AND osobye_potrebnosti.meta_key='особыС_потрСбности_ΠΊΠ΄ΠΊ' AND (osobye_potrebnosti.meta_value like '%%s:8:\"kastraty\";%%' ) WHERE vozrat_koshek.meta_key='Π²ΠΎΠ·Ρ€Π°Ρ‚_кошСк' and wp_term_relationships.term_taxonomy_id=%d AND brend.meta_key='ΠΏΡ€ΠΎΠΈΠ·Π²ΠΎΠ΄ΠΈΡ‚Π΅Π»ΡŒ' AND (brend.meta_value='137' ) AND klass_korma.meta_key='класс_ΠΊΠΎΡ€ΠΌΠ°' AND (klass_korma.meta_value='premium' ) and t1.post_status='publish'
  • please attach to the question the output of the command explain тСкст-вашСго-запроса . You can edit the question by clicking edit below the question text. - aleksandr barakin

2 answers 2

first, you can get rid of duplicate conditions in where

 SELECT vozrat_koshek.post_id,vozrat_koshek.meta_value FROM wp_posts t1 JOIN wp_term_relationships ON wp_term_relationships.object_id=t1.ID and wp_term_relationships.term_taxonomy_id=%d and t1.post_status='publish' JOIN wp_postmeta ON wp_postmeta.post_id=t1.ID AND ( (wp_postmeta.meta_key='ΠΏΡ€ΠΎΠΈΠ·Π²ΠΎΠ΄ΠΈΡ‚Π΅Π»ΡŒ' AND wp_postmeta.meta_value='137') or (wp_postmeta.meta_key='класс_ΠΊΠΎΡ€ΠΌΠ°' AND wp_postmeta.meta_value='premium' )) ....

but it is not clear about the last joins on wp_postmeta some of the conditions of each other overlap specify the condition for this table

  • In the where clause with AND, you got OR. There are all elements that satisfy one of the conditions. - Marsel Arduanov
  • in where they are repeated AND brend.meta_key = 'producer' AND brend.meta_value = '137' they are also in the LEFT JOIN mapping and if you look closely at the last JOIN wp_postmeta as oebye_potrebnosti ON osobye_potrebnosti.post_id = klass_korma.h. as klass_korma here I don’t see any logs at all - Xramovnic
  • I will well describe the problem LEFT JOIN wp_postmeta as brend to brend.post_id = t1.ID and AND brend.meta_key = 'producer' AND (brend.meta_value = '137') - then wp_postmeta as brend jpd AND klass_korma.meta_value = 'premium', ie, according to the logic after the first join, all posts for which the producer = 137 condition were selected and then for 2 links we select the same post_id value where feed_class = premium do you think this is logical? - Khramovnic

You can promote the table once and in where through or prescribe all the conditions, then group by id and select only values ​​that contain all the conditions. In having 3 is the number of conditions in where.

 SELECT t1.id FROM t1 JOIN t2 WHERE t2.meta_key='ΠΏΡ€ΠΎΠΈΠ·Π²ΠΎΠ΄ΠΈΡ‚Π΅Π»ΡŒ' OR t2.meta_key='класс_ΠΊΠΎΡ€ΠΌΠ°' OR t2.meta_key='особыС_потрСбности_ΠΊΠ΄ΠΊ' GROUP BY t1.id HAVING count(t1.id) = 3