From: Jim C. Nasby <jnasby@pervasive.com>
To: Joe <svn@freedomcircle.net>
Cc: Andreas Pflug <pgadmin@pse-consulting.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: Comparative performance
Date: Tue, 4 Oct 2005 15:41:22 -0500
Message-ID: <20051004204122.GV40138@pervasive.com> (raw)
In-Reply-To: <433C5108.6070202@freedomcircle.net>
References: <433B4AA4.9080008@freedomcircle.net>
<op.sxu1g5bnth1vuj@localhost>
<433BDFF3.3090402@freedomcircle.net>
<433BEA7B.1050405@pse-consulting.de>
<433C5108.6070202@freedomcircle.net>
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
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-performance@postgresql.org
Cc: jnasby@pervasive.com, svn@freedomcircle.net, pgadmin@pse-consulting.de
Subject: Re: Comparative performance
In-Reply-To: <20051004204122.GV40138@pervasive.com>
* 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