I'm trying to fetch the most popular threads to show them in the portal, this is the query I have:
SQL Code
SELECT t.subject, t.tid, t.views, t.icon, t.prefix
FROM ".TABLE_PREFIX."threads t
LEFT JOIN ".TABLE_PREFIX."ratemf_log l ON (l.pid=t.firstpost)
WHERE t.visible='1' {$overview_unviewwhere} AND t.closed NOT LIKE 'moved|%'
ORDER BY t.views DESC
LIMIT 0, {$settings['overview_max']};
|
As you can see, I'm joining a different tables to order the threads by the number of "rates" the thread has had. In the above I just joined, the query still works as usual. The problem comes here:
SQL Code
SELECT t.subject, t.tid, t.views, t.icon, t.prefix, COUNT(l.uid) AS logs
FROM ".TABLE_PREFIX."threads t
LEFT JOIN ".TABLE_PREFIX."ratemf_log l ON (l.pid=t.firstpost)
WHERE t.visible='1' {$overview_unviewwhere} AND t.closed NOT LIKE 'moved|%'
ORDER BY t.views DESC
LIMIT 0, {$settings['overview_max']};
|
I added:
SQL Code
, COUNT(l.uid) AS logs
, COUNT(l.pid) AS logs
, COUNT(l.lid) AS logs
|
That is suppose to count the number of rows for that thread, but that alone shouldn't affect the query at all, just that it does, the thread being pulled are no the same as the one before adding that piece of code.
This doesn't seem to work either.
But ", l.lid", ", l.*", or ", l.pid" does 
Any clue?
You need to use grouping (GROUP BY clause) if you want multiple rows returned with an aggregate function (eg COUNT).
I see, do you think this would work?
PHP Code:
$query = $db->query("SELECT t.subject, t.tid, t.views, t.icon, t.prefix, l.installs
FROM ".TABLE_PREFIX."threads t
LEFT JOIN (SELECT pid, COUNT(lid) AS installs FROM ".TABLE_PREFIX."ratemf_log WHERE rid='1' GROUP BY pid) l ON (l.pid=t.firstpost)
WHERE t.visible = '1' {$where} AND t.closed NOT LIKE 'moved|%'
ORDER BY l.installs DESC, t.views DESC
LIMIT 0,{$limit};");
|
$where holds the disabled forums list.
There are no visible errors now, just need to wait for posts being rated as installed.
Probably should. Might not be the best performance wise, as the inner select is probably doing a full table scan, but might be acceptable to you.
So I would need to filter the scan in the inner select WHERE statement? But the only filters I can use are "rid (reputation type, in this case 'installs')" and pid (which should be the same as 'firstpost'), but that last is already being used in the ON (...) statement. Will "firstpost" work in the inner select? Will try as soon as I can connect to the FTP.
The MyBB solution is to cache the count in the threads table.
But maybe I'm wrong, check MySQL's explain function to see how it's running the query (note that small result sets may be inaccurate).
I'm using the
Overview plugin which cache the HTML output and updates it every X amount of time, do you think that would be enough?
I tried using i$mybb->debug_mode = true and $db->explain_query() but well, I'm not that good, I don't understand what the information it outputs means nor what to do with it -.-
Well if it's not causing issues for you, then I wouldn't worry about it. No point in over-engineering something that isn't needed...