Hi!
I've been trying to optimize a query in which I join several tables, since
I've seen it takes about 2 seconds, which is way too much.
Well, the query is the following, I'm using LEFT OUTER JOIN just when the
tables can have NULL results, plain JOIN otherwise:
select ="select to_char(a.fecha_publicacion,'dd/MM/yyyy')," +
"c.nombre,date_part('year',CURRENT_TIMESTAMP)-b.ano as
edad," +
"b.alzada,d.nombre,e.nombre,a.precio_a_mostrar,f.nombre," +
"a.destacado,a.visto,h.nombre,m.nombre,g.idprovincia," +
"g.provincia,b.raza_id,b.raza,b.disciplina_id" +
",b.disciplina " +
" from anuncio a JOIN caballo b " +
"ON (a.producto_id=b.id) " +
"JOIN raza c ON (b.raza_id=c.id) " +
" LEFT OUTER JOIN disciplina d " +
"ON (b.disciplina_id=d.id) " +
"LEFT OUTER JOIN nivel_disciplina e " +
"ON (b.disciplina_id=e.disciplina_id " +
"and b.nivel_id=e.nivel) " +
" JOIN anunciante_datos g ON (a.id_anunciante_datos = g.id)"
+
"JOIN provincia f ON (g.idprovincia=f.id) " +
"JOIN categoria h ON (a.categoria_id=h.id) " +
" LEFT OUTER JOIN sexo_caballo m ON " +
"(b.sexo_id=m.id) "+
"WHERE a.id=?";
I'd thought I could pull a trick on the fact that even though this "anuncio"
table (a) is relatively big (a few thousand entries), I really just need to
retrieve one result from it and combine it with the other tables. This
result would be that which matches with the ? in the a.id 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 advertisement that sells such a horse.
My idea was to try to cut as soon as possible the few thousands of registers
from the "anuncio" table so that it might make it less costly to make the
query. For instance I tried this, but with no visible results:
" from anuncio a JOIN caballo b " +
"ON (a.id=? AND a.producto_id=b.id) " +
Any ideas on how to critically optimize the query?
Thank you lots =)
