Re: faster output from php and postgres

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