Date: Tue, 4 Oct 2005 15:41:22 -0500
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Joe <svn@freedomcircle.net>
Cc: Andreas Pflug <pgadmin@pse-consulting.de>,
 pgsql-performance@postgresql.org
Subject: Re: Comparative performance
Message-ID: <20051004204122.GV40138@pervasive.com>
References: <433B4AA4.9080008@freedomcircle.net> <op.sxu1g5bnth1vuj@localhost>
	<433BDFF3.3090402@freedomcircle.net>
	<433BEA7B.1050405@pse-consulting.de>
	<433C5108.6070202@freedomcircle.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <433C5108.6070202@freedomcircle.net>
User-Agent: Mutt/1.5.9i

On Thu, Sep 29, 2005 at 04:39:36PM -0400, Joe wrote:
> Andreas Pflug wrote:
> >Hm, if you only have 4 tables, why do you need 12 queries?
> >To reduce queries, join them in the query; no need to merge them 
> >physically. If you have only two main tables, I'd bet you only need 1-2 
> >queries for the whole page.
> 
> There are more than four tables and the queries are not functionally 
> overlapping.  As an example, allow me to refer to the page 
> www.freedomcircle.com/topic.php/Economists.
> 
> The top row of navigation buttons (Life, Liberty, etc.) is created from a 
> query of the 'topic' table.  It could've been hard-coded as a PHP array, 
> but with less flexibility.  The alphabetical links are from a SELECT 
> DISTINCT substring from topic.  It could've been generated by a PHP for 
> loop (originally implemented that way) but again with less flexibility.  
> The listing of economists is another SELECT from topic.  The subheadings 
> (Articles, Books) come from a SELECT of an entry_type table --which 
> currently has 70 rows-- and is read into a PHP array since we don't know 
> what headings will be used in a given page.  The detail of the entries 

I suspect this might be something better done in a join.

> comes from that query that I posted earlier, but there are three additional 
> queries that are used for specialized entry types (relationships between 
> topics --e.g., Prof. Williams teaches at George Mason, events, and 
> multi-author or multi-subject articles and books).  And there's yet another 

Likewise...

> table for the specific book information.  Once the data is retrieved it's 
> sorted internally with PHP, at the heading level, before display.

It's often better to let the database sort and/or aggregate data.

> Maybe there is some way to merge all the queries (some already fairly 
> complex) that fetch the data for the entries box but I believe it would be 
> a monstrosity with over 100 lines of SQL.

Also, just because no one else has mentioned it, remember that it's very
easy to get MySQL into a mode where you have no data integrity. If
that's the case it's going to be faster than PostgreSQL (though I'm not
sure how much that affects the performance of SELECTs).
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461