Re: Comparative performance

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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461