SMFHacks.com

Modifications/Themes => General SMF Forum => Topic started by: shuban on October 17, 2022, 09:34:28 pm

Title: SQL Help
Post by: shuban on October 17, 2022, 09:34:28 pm

Hello, I am seeking to get a count of topics given the following code. However, it produces over 500,000 topics, which is exhausting the server. I want to limit it to the first 1000 results, but the LIMIT feature isn't working. Apparently there is a solution here (https://stackoverflow.com/questions/50248586/limit-not-working-on-count-distinct-mysql), but I can't get it to work.

Do you have any suggestions?

Code: [Select]

$request = db_query("
 SELECT COUNT(DISTINCT t.ID_TOPIC)
 FROM ({$db_prefix}topics AS t, {$db_prefix}boards AS b)
 
INNER JOIN {$db_prefix}messages AS m ON (m.ID_MSG = t.ID_FIRST_MSG)
 
 WHERE b.ID_BOARD = t.ID_BOARD
 AND $query_this_board
 AND m.topicSolved = 0 AND t.ID_FIRST_MSG = t.ID_LAST_MSG", __FILE__, __LINE__);

Title: Re: SQL Help
Post by: SMFHacks on October 17, 2022, 11:12:06 pm

The first topics in the board or the latest topics? LIMIT should work at the end of the query.