Large(ish) database problem

Databases
1

Hi,

I have a database with over 12,000 products. The ecommerce system works fine on other sites, which have a lot less products.

Problem is, it simply won’t work with this database, probably slow performance before timeout. My query is:


$q = "
		SELECT p.*, b.brandName, pr.price, pr.offerTypeID
		FROM ".DB_PREFIX."categories node
			JOIN ".DB_PREFIX."categories parent
				ON node.lft BETWEEN parent.lft AND parent.rgt 
			JOIN ".DB_PREFIX."catmembers cm 
				ON cm.categoryID = node.categoryID
			JOIN ".DB_PREFIX."products p
				ON p.productID = cm.productID
			LEFT JOIN ".DB_PREFIX."brandmembers bm
				ON bm.productID = p.productID
			LEFT JOIN ".DB_PREFIX."brands b 
				ON b.brandID = bm.brandID

			LEFT JOIN ".DB_PREFIX."prices pr 
				ON pr.productID = p.productID
				AND (pr.dateFrom <= '".strtotime("Today")."' AND pr.dateTo >= '".strtotime("Today")."') 
			
		WHERE productDisplay = 1
			
		";

Even running it direct from the server takes a long time. (Infact it’s still running. It takes around 4 seconds to get every row in the 12000 table).

Is the problem with the statement? Or is there a way I could optimize the table.

Cheers,
Rhys

2

Further to the previous, an EXPLAIN with the query gave me the following.

3

Do you really need all fields from the products table? If not then list just the fields from that table which are needed.

Try moving this bit to the WHERE clause

AND (pr.dateFrom <= '".strtotimeCOLOR=#007700.“’ AND pr.dateTo >= '”.strtotimeCOLOR=#007700.[/COLOR]"') [/COLOR]

4

why?

it’s a left outer join, and putting these conditions into the WHERE clause will turn it into an inner join

5

Thanks for the reply. Unfortunately even setting p.* to p.productID had no effect.

6

After playing around, I found removing the brandmembers and brands joins solved the problem. Is there any more efficient JOIN than the one being used?

7

Try adding an index on brandmembers.productID

8

Do you mean in the code? The database has the index unique key set to brandmembersID.

EDIT:
Sorry, misunderstood the question.

Problem solved. Thanks very much guido2004.