breaking up queries

[raymie7]

Hi,
I have a pretty large query which I've split into two to try and cut down the time it takes to load the page. Only I don't know the best way to link them together to display the results- not sure how to pull the second query out the while loop. Any ideas would be greatly appreciated. This is what I have:

$works_id = mysql_query ("SELECT c.works_id
						FROM CONTRIBUTORS c, ROLE r, ENTITY e
						WHERE c.role_id = r.role_id
						AND r.entity_id = e.entity_id
						AND c.works_id IS NOT NULL
						ORDER BY e.othernames 
						LIMIT $page_top, $page_bottom");
		$i = 0;
		while($row [] = mysql_fetch_array($works_id))
		{
			$wk_id = $row[$i][works_id];
			$i ++;

		$id = mysql_query("SELECT p.product_id,p.type_id,p.product_name
					FROM PRODUCTS p,PRODUCT_LOOKUP pl,CARDBOX cb, CONTRIBUTORS c
					WHERE p.product_id = pl.product_id
					AND pl.cardbox_id = cb.cardbox_id 
					AND cb.works_id = $wk_id ");

		$product_matches = mysql_fetch_array($id);

	}

[Roger_Ramjet]

At first glance I would say that splitting the query has slowed the page down even more: it now takes multiple queries to retrieve the dataset you want and that will slow your page down.

Instead, I would recomend that you reconstruct the complete query and use EXPLAIN to see how it could be optimised.

I have a feeling that you could improve things immediately by using alternativs Joins, but not havind your DB schema I can't be sure. I can ask questions about your Indexing: are all join-columns indexed, are all where-columns indexed?