From: Lonnie VanZandt <lonniev@predictableresponse.com>
To: 'scott.marlowe' <scott.marlowe@ihs.com>
Cc: pgsql-php@postgresql.org
Subject: Re: faster output from php and postgres
Date: Fri, 23 May 2003 11:26:08 -0600
Message-ID: <009601c32150$66a78220$113ea8c0@predictable> (raw)
In-Reply-To: <Pine.LNX.4.33.0305231003070.27073-100000@css120.ihs.com>
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@postgresql.org
[mailto:pgsql-php-owner@postgresql.org] On Behalf Of scott.marlowe
Sent: Friday, May 23, 2003 10:04 AM
To: Mukta Telang
Cc: pgsql-php@postgresql.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@postgresql.org
>
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lonniev@predictableresponse.com, scott.marlowe@ihs.com, pgsql-php@postgresql.org
Subject: Re: faster output from php and postgres
In-Reply-To: <009601c32150$66a78220$113ea8c0@predictable>
* 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