pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Frank Bax <fbax@sympatico.ca>
To: pgsql-php@postgresql.org
Subject: Re: faster output from php and postgres
Date: Fri, 23 May 2003 12:49:37 -0400
Message-ID: <5.2.0.9.0.20030523124321.043d1bf0@pop6.sympatico.ca> (raw)
In-Reply-To: <3ECE40AB.73544B27@darya.nio.org>

At 11:39 AM 5/23/03, 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"


Are you doing this with one select, or four selects, or something in 
between?  IAC, this doesn't sond like a php issue, but rather an issue with 
how you build your select statement.  If your SELECT statment is generated 
dynamically, then print it out when the script runs.  Then copy/paste the 
final SELECT statement, add "EXPLAIN" to the beginning of it and run it via 
psql.

Now you have enough info to post a question to pgsql-sql@postgresql.org - 
include the following:
- schema of various table involved (like you did above).
- copy of SELECT statement(s)
- output of EXPLAIN for the SELECT statement that is taking too long.

Frank 




view thread (15+ messages)  latest in thread

Message-ID: <5.2.0.9.0.20030523124321.043d1bf0@pop6.sympatico.ca>
Permalink:  ../5.2.0.9.0.20030523124321.043d1bf0@pop6.sympatico.ca/
Also on:    postgresql.org/message-id/5.2.0.9.0.20030523124321.043d1bf0@pop6.sympatico.ca

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: fbax@sympatico.ca, pgsql-php@postgresql.org
  Subject: Re: faster output from php and postgres
  In-Reply-To: <5.2.0.9.0.20030523124321.043d1bf0@pop6.sympatico.ca>
  References: <3ECE40AB.73544B27@darya.nio.org> <5.2.0.9.0.20030523124321.043d1bf0@pop6.sympatico.ca>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox