Queries' Relative Cost

[karmanis]

Which one do you think that woud add the LESS load to your server...


choice 1)
1 simple query selecting the latest 5 posts from 20 specified forums, among 100.000 posts.... repeated 10 times in a loop. gre:


choice 2)
1 query selecting the 5 latest posts from each of 200 forums (total =1000 selected posts), among 100.000 posts... applied just once
+
1 query selecting all the posts (should be 5 in total ) from 20 specified forums, among 1000 posts... repeated 10 times in a loop



It will also be of some interest, if you specify how many times heavier (approximately) would be the "other choice".

many thanks !
:bunny:

[otero]

?

[filburt1]

You should never need to query in a loop. Instead, I suggest that you instead think of a different way of writing the queries such that it isn't necessary to loop.

[Dean C]

Or put the data into an array and loop through that?

[Brad]

Yes, use fetch_array() anytime you are required to use query info in a loop.

[Erwin]

Do 1 query to select the things you want with DESC LIMIT 5, then use WHILE and ENDWHILE to process what you collected in a meaningful manner and parsing the results into the template.

[karmanis]

So, you all suggest me to go with one query gathering all the info I want, and some while statements to further process specific parts of the info.

But let me ask you something here. How, for example, would I gather the latest 5 thread titles from each one of 20 forums, using just one query ?

Supposing that the following query can be used to select the last 5 threads from a single forum....

$DB_site->query("SELECT threadid,title FROM thread 
WHERE forumid = $forumid 
ORDER BY lastpost DESC limit 5")

..how could we achieve to additionally:

1) have it selecting the last 5 threads from the first forum of $myforums[mylist], after that from the second one, the third... until all forums have been used. At the end I would have selected 5*20=1000 threads, the latest 5 from each of my 20 forums in the list.

2) have it selecting not only from the parsed forum in the list, but also from all its subforums (not stated in the list).


Are these possible with a single query, or I would end up using 20, one query for each forum ??


many thanks