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
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