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 7F607DA040 for ; Tue, 4 Oct 2005 09:37:14 -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 14991-10 for ; Tue, 4 Oct 2005 12:35:09 +0000 (GMT) Received: from tomts22-srv.bellnexxia.net (tomts22.bellnexxia.net [209.226.175.184]) by svr1.postgresql.org (Postfix) with ESMTP id B4A34DA045 for ; Tue, 4 Oct 2005 09:30:19 -0300 (ADT) Received: from acer.sympatico.ca ([65.95.138.156]) by tomts22-srv.bellnexxia.net (InterMail vM.5.01.06.10 201-253-122-130-110-20040306) with ESMTP id <20051004123012.FVVN21470.tomts22-srv.bellnexxia.net@acer.sympatico.ca> for ; Tue, 4 Oct 2005 08:30:12 -0400 Message-Id: <5.2.1.1.0.20051004080930.0584cba0@pop6.sympatico.ca> X-Sender: b1wudu74@pop6.sympatico.ca X-Mailer: QUALCOMM Windows Eudora Version 5.2.1 Date: Tue, 04 Oct 2005 08:18:47 -0400 To: pgsql-php@postgresql.org From: Frank Bax Subject: Re: Query takes much to long, is there a work around? In-Reply-To: <20051004115514.A9ECED941F@svr1.postgresql.org> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.073 required=5 tests=[AWL=0.073] X-Spam-Level: X-Archive-Number: 200510/4 X-Sequence-Number: 3272 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.