Performance in MYSQL , MAX( IF( condition ) ) or Subquery condition

Question

I need to search extra fields as columns on my table, it worked with 2 distinct soluctions:

Table: Boxes

+ -- + ----- + ---- +
| id | name  | vinc |
|----|-------|------|
| 1  | box1  | 22   |
| 2  | box2  | 45   |
| 3  | box3  | 60   |
+ -- + ----- + ---- +

Table: Extr

+ -- + ---- + ----- + ------- +
| id | vinc | type  | value   |
+----+------+-------+---------+
| 1  | 22   | color | #fafafa |
| 2  | 22   | size  | 100     |
| 3  | 45   | size  | 200     |
+ -- + ---- + ----- + ------- +

Needed

+ ---- + ---- + ------- +
| name | size | color   |
+------+------+---------+
| box1 | 100  | #fafafa |
| box2 | 200  |         |
| box3 |      |         |
+ ---- + ---- + ------- +

i did it with subquery and worked, but sometimes i need to search on query by the value by the extr.value, resulting in more process:

SELECT 
    box.name
    ,(select extr.value from ext where extr.name = 'size' and box.vinc = extr.vinc) as size
    ,(select extr.value from ext where extr.name = 'color' and box.vinc = extr.vinc) as color
    ... 1 to X extra fields
from box
(sometimes)
where 
    (select extr.value from ext where extr.name = 'size' and box.vinc = extr.vinc) = 'user_inputed value' 
    and... 1 to X

or

SELECT 
    box.name
    ,MAX( if( extr.name = 'size' , extr.value , '' ) ) as size
    ,MAX( if( extr.name = 'color' , extr.value , '' ) ) as color
FROM box    
LEFT JOIN extr on box.vinc = extr.vinc
WHERE
   if( extr.name = 'size' , extr.value , '' ) = 'user_inputed_value' 

both are grouped by box, so i dont have duplicated lines ( i think ) for select.

these queries has automated constructors, so i don't ease of use or have problems to debug after.

• the question is: which is the best practice and/or has the best performance ? :)

Answer 1

Another option is to just JOIN to extr twice.

SELECT b.name, eSize.value AS size, eColor.value AS color
    , ...
FROM box AS b 
LEFT JOIN extr AS eSize ON b.vinc = eSize.vinc AND sSize.name = 'size'
LEFT JOIN extr AS eColor ON b.vinc = eColor.vinc AND eColor.name = 'color'
WHERE ...