How can i update this query, to give me more speed?
Why is the query slower, when I use JOIN?
SELECT v.vare_id,
LEFT(v.varenavn, 25) as varenavn,
v.salgspris,
ve.enhed,
ve.vare_enhed_id,
vt.tilstand,
vt.vare_tilstand_id,
v.oko_vare,
v.varefoto
FROM vare v
JOIN vare_enhed_valg vev
ON vev.vare_id = v.vare_id
JOIN vare_enhed ve
ON ve.vare_enhed_id = vev.enhed_id
JOIN vare_tilstand_valg vtv
ON vtv.vare_id = v.vare_id
JOIN vare_tilstand vt
ON vt.vare_tilstand_id = vtv.tilstand_id
WHERE v.vare_type_id = 1
AND v.synlig = "ja"
ORDER BY v.varenavn ASC
For your query, you want an index on: vare(vare_type_id, v.synlig, v.varenavn). Other indexes might be appropriate. Let me assume that the join keys include the primary key of at least one of the tables.