<br />Hi!<br /><br />I've been trying to optimize a query in which I join several tables, since I've seen it takes
about2 seconds, which is way too much.<br /><br />Well, the query is the following, I'm using LEFT OUTER JOIN just when
thetables can have NULL results, plain JOIN otherwise: <br /><br />select ="select
to_char(a.fecha_publicacion,'dd/MM/yyyy'),"+<br /> "c.nombre,date_part('year',CURRENT_TIMESTAMP)-b.ano
asedad," +<br /> "b.alzada ,d.nombre,e.nombre,a.precio_a_mostrar,f.nombre," +<br />
"a.destacado,a.visto,h.nombre,m.nombre,g.idprovincia,"+<br />
"g.provincia,b.raza_id,b.raza,b.disciplina_id"+<br /> ",b.disciplina " +<br /> " from
anuncioa JOIN caballo b " +<br /> "ON (a.producto_id=b.id) " +<br /> "JOIN raza c ON
(b.raza_id=c.id) " +<br /> " LEFT OUTER JOIN disciplina d " +<br /> "ON
(b.disciplina_id=d.id)" +<br /> "LEFT OUTER JOIN nivel_disciplina e " +<br /> "ON (
b.disciplina_id=e.disciplina_id" +<br /> "and b.nivel_id=e.nivel) " +<br /> " JOIN
anunciante_datosg ON (a.id_anunciante_datos = <a href="http://g.id">g.id</a>)" +<br /> "JOIN provincia f
ON( g.idprovincia=f.id) " +<br /> "JOIN categoria h ON (a.categoria_id=h.id) " +<br /> "
LEFTOUTER JOIN sexo_caballo m ON " +<br /> "(b.sexo_id=m.id) "+ <br /> "WHERE a.id=?";<br
/><br/>I'd thought I could pull a trick on the fact that even though this "anuncio" table (a) is relatively big (a few
thousandentries), I really just need to retrieve one result from it and combine it with the other tables. This result
wouldbe that which matches with the ? in the <a href="http://a.id">a.id</a> condition, which is the Primary Key of this
"anuncio"table. The other data is derived from this specific result in "anuncio" (which btw means "advertisment" in
spanish).For example, "caballo" means "horse" in spanish, and its data is retrieved as a horse related to the
advertisementthat sells such a horse. <br /><br />My idea was to try to cut as soon as possible the few thousands of
registersfrom the "anuncio" table so that it might make it less costly to make the query. For instance I tried this,
butwith no visible results: <br /><br /> " from anuncio a JOIN caballo b " +<br /> "ON
(a.id=?AND a.producto_id=b.id) " +<br /><br />Any ideas on how to critically optimize the query? <br /><br />Thank you
lots=) <br /><br />