SQL fails

[Sama34]

I'm trying to fetch the most popular threads to show them in the portal, this is the query I have:

SQL Code

1 2 3 4 5 6

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

1 2 3 4 5 6

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.

SQL Code

, COUNT(l.*) AS logs

---

But ", l.lid", ", l.*", or ", l.pid" does

Any clue?

[ZiNgA BuRgA]

You need to use grouping (GROUP BY clause) if you want multiple rows returned with an aggregate function (eg COUNT).

[Sama34]

I see, do you think this would work?

PHP Code:

1 2 3 4 5 6

$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.

[ZiNgA BuRgA]

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.

[Sama34]

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.

[ZiNgA BuRgA]

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).

[Sama34]

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 -.-

[ZiNgA BuRgA]

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...

[Sama34]

Thank you Zinga Burga.