Re: faster output from php and postgres

Finding previous examples of complex joins in archives is not likely an
easy thing to find.

pg_dump -s -t author -t publication -t pub_auth [database] | grep -v ^--

Change [database] to the name of your database - this command will dump out
schema relative to your request. Post the results to this list. Then ask
us the question "how do I write a SELECT that produces...[ you finish this
sentence]". Question probably more appropriate to the list you mentioned,
but I expect there are people here who are just as capable of answering the
question. I've even seen examples where the process goes through several
emails before SQL produces desired results exactly.

>How would a join make this easier?

I have always found that one properly constructed complex query is always
"cheaper" in runtime than numerous queries inside a foreach loop. Your
final query will likely include joining a table to itself (this can
sometimes be a difficult concept to grasp).

Frank

At 11:50 AM 5/24/03, Chadwick Rolfs wrote:

>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
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faqs/FAQ.html