X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 43C11D9FF1 for ; Tue, 4 Oct 2005 11:46:23 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 41001-08 for ; Tue, 4 Oct 2005 14:46:18 +0000 (GMT) Received: from retolocal.net (ipd50a4489.speed.planet.nl [213.10.68.137]) by svr1.postgresql.org (Postfix) with SMTP id 40DBAD9FE3 for ; Tue, 4 Oct 2005 11:46:18 -0300 (ADT) Received: (qmail 30616 invoked from network); 26 Feb 1999 07:15:54 -0000 Received: from pc-00106.retolocal.net (HELO retopctoon) (192.168.55.106) by vuurmuur.retolocal.net (192.168.55.10) with ESMTP; 26 Feb 1999 07:15:54 -0000 From: "Toon van Doorn" To: Subject: Re: Query takes much to long, is there a work around? Date: Tue, 4 Oct 2005 16:46:23 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook, Build 11.0.6353 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 In-Reply-To: <5.2.1.1.0.20051004080930.0584cba0@pop6.sympatico.ca> Thread-Index: AcXI8mPN20g98IvDSIykiDexTOvVBQ== Message-Id: <20051004144618.40DBAD9FE3@svr1.postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.328 required=5 tests=[AWL=-0.426, FORGED_RCVD_HELO=0.05, MSGID_FROM_MTA_ID=1.704] X-Spam-Level: * X-Archive-Number: 200510/5 X-Sequence-Number: 3273 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@postgresql.org [mailto:pgsql-php-owner@postgresql.org] Namens Frank Bax Verzonden: dinsdag 4 oktober 2005 14:19 Aan: pgsql-php@postgresql.org 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