How to optimize such a SQL query?

Question

MySQL

There is such a request

SELECT
posts.id,
posts.post_title,
(SELECT meta_value FROM postmeta WHERE meta_key='key_1' AND post_id=posts.id LIMIT 1) as val_1,
(SELECT meta_value FROM postmeta WHERE meta_key='key_2' AND post_id=posts.id LIMIT 1) as val_2,
(SELECT meta_value FROM postmeta WHERE meta_key='key_3' AND post_id=(SELECT meta_value FROM postmeta WHERE meta_key='key_4' AND post_id=posts.id LIMIT 1) LIMIT 1) as val_3
FROM
posts

It's working but not very fast 0.559 s for 1000 lines output.
The difficulty arose in the fact that the values ​​val_1, val_2 and val_3 are in the same table in a field with the common name meta_value and differ only in the neighboring field meta_key. How can this sql query be made more correct or what?

Answer 1

something like this, but the third subquery shows a design problem, all this will work provided that the postmeta table has a unique key from two fields meta_key, post_id

SELECT
posts.id,
posts.post_title,
v1.meta_value  as val_1,
v2.meta_value  as val_2,
(SELECT meta_value FROM postmeta WHERE meta_key='key_3' AND post_id=(SELECT meta_value FROM postmeta WHERE meta_key='key_4' AND post_id=posts.id LIMIT 1) LIMIT 1) as val_3
FROM
posts 
 left join postmeta v1 on (v1.meta_key='key_1' AND v1.post_id=posts.id) 
  left join postmeta v2 on (v2.meta_key='key_2' AND v2.post_id=posts.id)

ideally it should look like this

SELECT
posts.id,
posts.post_title,
v1.meta_value  as val_1,
v2.meta_value  as val_2,
v3.meta_value  as val_3,

FROM
posts 
  left join postmeta v1 on (v1.meta_key='key_1' AND v1.post_id=posts.id) 
  left join postmeta v2 on (v2.meta_key='key_2' AND v2.post_id=posts.id)     
  left join postmeta v3 on (v2.meta_key='key_3' AND v2.post_id=posts.id)