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