It's hard to say for certain without knowing much more about your DB (the schema, number of rows, indexes and such). But here are some general tips I can think of:
-try the multi table query in your admin tool (to avoid any overhead PHP and the web server may cause). Is it 10 seconds here or is it sub second? -While still using your admin tool try various indexes to make the multi table query sub second (yes you should be able to get it sub second). You didn't mention which DBMS you're using and they offer different types of indexes which may help. Also creating a View may help (I don't think this will make a difference in MySQL but other DBMS's offer "materialized Views" and indexes which span both tables) -Does your DBMS offer a "show plan"? Use this, it will be very valuable in optimizing this. -Now that you've got the query running quick as lightning try in in the web page. If it's significantly slower try inserting timestamps between the blocks of code so you can tell what's taking so long (it may not be the DB at all). Good Luck, Frank On 5/30/02 10:31 PM, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > From: Leif K-Brooks <[EMAIL PROTECTED]> > Date: Thu, 30 May 2002 13:51:54 -0400 > To: [EMAIL PROTECTED] > Subject: Multi-table query slower than using array? > > I have a website where users can adopt virtual "pets". On this page, > there is a page with some statistics. One of the statistics listed on > that page is most popular pet species. It was taking over 3 seconds to > load, so I'm trying to optimize it. I store the pet species in a > seperate table from pets, an id from the species table is stored in the > species table. I used to be putting all of the species in an array by > looping through the species table, then I would use that array to > process the id numbers. In my attempts to optimize, I changed it to a > multi-table query that looked something like "select count(*) as > cnt,species.name,pets.species from pets,species where > species.id=pets.species group by pets.species order by cnt desc limit > 1". Now, the speed of the page loading is up to 10 seconds! Was the > array method the better way? -- Frank Flynn Poet, Artist & Mystic -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
