Found it, I now use SELECT adreslog.adres,adreslog.totalread ,sum(case when linkcode='1012' AND linklog.adrescode=adreslog.alogid then 1 else 0 end ) as Homepage ,sum(case when linkcode='1234' AND linklog.adrescode=adreslog.alogid then 1 else 0 end ) as Mickey FROM adreslog left join linklog on (adreslog.alogid = linklog.adrescode) WHERE adreslog.mailcode='24120256' group by adreslog.adres,adreslog.totalread; Your comment about joining the tables helped. Thanks !! Kind regards, Toon van Doorn -----Oorspronkelijk bericht----- Van: pgsql-php-owner@xxxxxxxxxxxxxx [mailto:pgsql-php-owner@xxxxxxxxxxxxxx] Namens Frank Bax Verzonden: dinsdag 4 oktober 2005 14:19 Aan: pgsql-php@xxxxxxxxxxxxxx Onderwerp: Re: [PHP] Query takes much to long, is there a work around? At 07:55 AM 10/4/05, Toon van Doorn wrote: >Is there a faster way of selecting the hits on a link per adres? This type of question (slow query) is usually asked on pgsql-sql list, it has nothing to do with php. You didn't mention indexes - surely you have indexes on your tables? Check the output of "EXPLAIN ANALYSE" for one of your "select count ..." subqueries. I think your query will benefit from a multi-column index on ( mailcode, linkcode, adrescode ), but 16 subselects and 51 rows in adreslog means over 800 subqueries being executed - that's a lot of work to do!! What happens if you change SELECT count(linkcode) FROM linklog WHERE linklog.mailcode='24120256' AND linkcode='1012' AND linklog.adrescode=adreslog.alogid) as Homepage to somthing like: SELECT adrescode, mailcode, count(linkcode) FROM linklog WHERE mailcode='24120256' AND linkcode='1012' as Homepage then join to adreslog table. ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
