Re: faster output from php and postgres --please help!

On Mon, 19 May 2003 16:13:31 +0000, you wrote: >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! Note: this is a pure SQL problem, not PHP. You should submit it in a more appropriate place next time. >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 ) That's fine - your database is normalised. I'd add author_paper primary key(author_id, paper_id) but that's just me. >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" What does your SQL statement look like? It sounds like you're doing multiple SELECTs for each record... SELECT [attributes of paper], author.author_id FROM author, author_paper, paper WHERE author.author_id = author_paper.author_id AND author_paper.paper_id = paper.paper_id AND author.name = "John Smith" will cover 1, 2 and 3a in a single SQL statement. Ask someone on a database-specific mailing list, and they'll show you how to do everything in a single statement (either with a sub-select or a left outer join[?], depending which DB you use).