Which is more efficient

[timgolding]

Hi can anyone let me know which is more efficient


$query="SELECT (SELECT COUNT(PIL.IMAGE_ID) FROM PRODUCT, PIL WHERE PRODUCT.NAME='TTL002S' AND PIL.PRODUCT_ID = PRODUCT.PRODUCT_ID) AS COUNT, PRODUCT.NAME, PRODUCT.PRODUCT_ID, IMAGE.* FROM IMAGE, PIL, PRODUCT WHERE PRODUCT.PRODUCT_ID = PIL.PRODUCT_ID AND PIL.IMAGE_ID = IMAGE.IMAGE_ID AND PRODUCT.NAME='TTL002S' ORDER BY PIL.ORDERING DESC LIMIT 0, 1";

//query etc


Or


$query1="SELECT PIL.IMAGE_ID FROM PRODUCT, PIL WHERE PRODUCT.NAME='TTL002S' AND PIL.PRODUCT_ID = PRODUCT.PRODUCT_ID"

$res=mysql_query($query1);

$count=mysql_num_rows($res);

$query2="SELECT PRODUCT.NAME, PRODUCT.PRODUCT_ID, IMAGE.* FROM IMAGE, PIL, PRODUCT WHERE PRODUCT.PRODUCT_ID = PIL.PRODUCT_ID AND PIL.IMAGE_ID = IMAGE.IMAGE_ID AND PRODUCT.NAME='TTL002S' ORDER BY PIL.ORDERING DESC LIMIT 0, 1";

//run query 2 etc.


It's basically for my pagination. I want one result per page. So need the limit to limit results to one result but need the total number of results if limit was not present.
Or is there a better way? Maybe i should be using php to limit the results?

[Fumigator]

COUNT() queries are extremely efficient, especially if only indexes have to be used. The first one avoids overhead of getting to and from MySQL so I'd say that one's better. And cleaner too, code-wise.

[timgolding]

Ok thanks I'll stick with that one then