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

Re: faster output from php and postgres

From: Frank Bax <fbax(at)sympatico(dot)ca>
To: pgsql-php(at)postgresql(dot)org
Subject: Re: faster output from php and postgres
Date: 2003-05-23 16:49:37
Message-ID: 5.2.0.9.0.20030523124321.043d1bf0@pop6.sympatico.ca (view raw or flat)
Thread:
Lists: pgsql-phppgsql-sql
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(at)postgresql(dot)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 


In response to

pgsql-php by date

Next:From: Ernest E VogelsingerDate: 2003-05-23 17:05:52
Subject: Re: Number of connections
Previous:From: scott.marloweDate: 2003-05-23 16:04:09
Subject: Re: faster output from php and postgres

pgsql-sql by date

Next:From: Lonnie VanZandtDate: 2003-05-23 17:26:08
Subject: Re: faster output from php and postgres
Previous:From: scott.marloweDate: 2003-05-23 16:04:09
Subject: Re: faster output from php and postgres

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