How Can I Optimize My MySQL Select Query?

SELECT  audit_name,
        SUM(audit_choice = 'Passed') AS passed,
        SUM(audit_choice = 'Failed') AS failed
FROM    audit
GROUP BY
        audit_name

EXPLAIN SELECT i.* FROM inv i 
LEFT JOIN (inv_m im) ON (i.m_id = im.id)
LEFT JOIN (inv_f iff) ON (iff.num = i.num)
LEFT JOIN (temp_a ta) ON (ta.num = i.num)
WHERE i.vid = 1
AND iff.num IS NULL
AND ta.num IS NULL
LIMIT 100

(case when p.name like '% apple %' then 'Apple'
      when p.name like '% western digital %' then 'Western Digital'
      . . .
      else ''
 end) as marca

SEARCHSTRING    FULLNAME
'% apple %'     'Apple'

select p.name, group_concat(ss.fullname separator ', ') as fullnames
from products p left outer join
     searchstrings ss
     on p.name like ss.searchstring
group by p.name;

select p.name, ss.fullname
from products p left outer join
     searchstrings ss
     on p.name like ss.searchstring;