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 [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
