Skip site navigation (1) Skip section navigation (2)

Re: faster output from php and postgres

From: Chadwick Rolfs <cmr(at)shell(dot)gis(dot)net>
To: pgsql-php(at)postgresql(dot)org
Subject: Re: faster output from php and postgres
Date: 2003-05-24 15:50:05
Message-ID: Pine.GSO.4.05.10305241147540.27954-100000@shell.gis.net (view raw or flat)
Thread:
Lists: pgsql-phppgsql-sql
I'm glad this came up, because I have the same type of problem.  Except,
I don't see how a join can work... of course, I'm not really schooled in
this stuff.

I also have three tables: author, publication, and pub_auth.

There are multiple authors for some publications, so it is necessary to
check each publication selected for ALL authors.  I'm doing this with a
foreach loop on the result of each publication key returned.

How would a join make this easier?

I'm browsing the pgsql-sql archives now, but that may take a week.  I'm
not sure what to search for...

On Fri, 23 May 2003, scott.marlowe wrote:

> Dear god, I saw the word join in his explanation and didn't even read much 
> after that.  I thought he meant join as in join.  
> 
> Even so, I bet he's still doing something else wrong, I can loop across 50 
> queries in a blink of an eye.  Maybe he's disconnecting / reconnecting on 
> every access, or worse, just connecting over and over.
> 
> I've seen some crazy code in PHP when folks are just beginning.  I think I 
> wrote a bit of it, but that was back when kids got suspended for saying 
> "I'm using PHP."  (<- true story, a kid had a run in with a teacher / 
> principle, and was nearly suspended for saying that.)
> 
> On Fri, 23 May 2003, Lonnie VanZandt wrote:
> 
> > The entire report should/could be a single SQL query with multi-table
> > joins and order by specifications - but I believe Mukta is doing
> > individual table queries and looping over result sets in the PHP code.
> > Seeing actual code will help...
> > 
> > -----Original Message-----
> > From: pgsql-php-owner(at)postgresql(dot)org
> > [mailto:pgsql-php-owner(at)postgresql(dot)org] On Behalf Of scott.marlowe
> > Sent: Friday, May 23, 2003 10:04 AM
> > To: Mukta Telang
> > Cc: pgsql-php(at)postgresql(dot)org
> > Subject: Re: [PHP] faster output from php and postgres
> > 
> > 
> > It's probably just a slow query.
> > 
> > Could you print out your query in PHP to the web browser, then cut and 
> > paste it into the psql monitor to get an explain anaylze output?
> > 
> > i.e.
> > psql dbname
> > #> explain analyze select .... rest of query goes here;
> > 
> > And give us the output of that?  Thanks.
> > 
> > On Fri, 23 May 2003, Mukta Telang wrote:
> > 
> > > Hi,
> > > I am dealing with a database with many-to-many relation and have a php
> > 
> > > script that joins these tables and displays the output. But the 
> > > problem is that after entering some 50 records the output is very 
> > > slow! I can count till 50 before the script produces the output!
> > > The complete description of the problem is written below..
> > > Thanks in advance,
> > > Mukta
> > > 
> > > 
> > > 
> > > 
> > > description in detail:
> > > 
> > > 
> > > database part:
> > > 
> > > Three tables: paper,author and author_paper
> > > 
> > > "A paper has many authors and an author has many papers."
> > > In this way there is many to many relation.
> > > 
> > > The attributes of paper are: paper_id (primary key),title,journal,year
> > 
> > > etc
> > > 
> > > The attributes of author are: author_id(primary key) and name
> > > 
> > > The attributes of author_paper are: author_id ,paper_id and level ( 
> > > author with level=1 is the main author of the paper )
> > > 
> > > 
> > > 
> > > php part:
> > > 
> > > For an author:
> > > 1. get author_id from author table
> > > 2. select all paper_ids from author_paper table
> > > 3. For each of the above selected paper_ids:
> > >         a. print all the attributes of paper
> > >         b. select all authors of the paper and print their names in 
> > > the order of their "level"
> > > 
> > > ---------------------------(end of 
> > > broadcast)---------------------------
> > > TIP 1: subscribe and unsubscribe commands go to
> > majordomo(at)postgresql(dot)org
> > > 
> > 
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
> > 
> > 
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> > 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 

-Chadwick


In response to

Responses

pgsql-php by date

Next:From: Chadwick RolfsDate: 2003-05-24 17:02:29
Subject: Re: faster output from php and postgres
Previous:From: Christopher Kings-LynneDate: 2003-05-24 09:03:17
Subject: Re: Number of connections

pgsql-sql by date

Next:From: Chadwick RolfsDate: 2003-05-24 17:02:29
Subject: Re: faster output from php and postgres
Previous:From: Bruce MomjianDate: 2003-05-24 04:45:37
Subject: Re: [SQL] noupcol code cleanup

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group