Optimize mysql multiple left joins <p>I have a query like this: </p> <pre class="lang-sql prettyprint-override"><code>SELECT SUM(c.cantitate) AS num, p.id AS pid, p.titlu AS titlu, p.alias AS alias, p.gramaj AS gramaj, p.prettotal AS prettotal, p.pretunitar AS pretunitar, p.pretredus AS pretredus, p.stoc AS stoc, p.cant_variabila AS cant_variabila, p.nou AS nou, p.congelat AS congelat, p.cod AS cod, p.poza AS poza, cc.seo AS seo FROM produse p LEFT JOIN (SELECT produs, cantitate, COS FROM comenzi) c ON p.id = c.produs LEFT JOIN (SELECT STATUS, id FROM cosuri) cs ON c.cos = cs.id LEFT JOIN (SELECT id, seo FROM categorii) cc ON p.categorie = cc.id WHERE cs.status = 'closed' AND p.vizibil = '1' GROUP BY pid ORDER BY num DESC LIMIT 0, 14 </code></pre> <p>The query is working, but Duration for 1 query: 2.922 sec. How can I improve the query ?</p> <p>The keys are as following : </p> <p>comenzi: cos, produs as unique key</p> <p>cosuri: id as unique key</p> <p>produse: titlu, categorie, alias as key</p>