Continue the legacy...

[JLA]

Having a problem with a query and I figure it should be able to be done better than this

$sql = "SELECT p.post_id
FROM " . TOPICS_TABLE . " t, " . POSTS_TABLE . " p
LEFT JOIN " . FUN_TABLE . " ON p.post_id = " . FUN_TABLE . ".post_id
WHERE p.forum_id = $funforum
AND t.topic_type = " . POST_NORMAL . "
AND p.post_approval = 1
AND p.topic_id = t.topic_id
AND " . FUN_TABLE . ".post_id IS NULL";
$db->sql_query($sql);

$thisquerysucks = $db->sql_fetchrowset($result);

So what we are dealing with here is a topics table with over 100 million rows, posts table with over 200 million rows and a "FUN TABLE" with 20 million rows

So this query is a problem and can take much too long to run. What might be a better method to get the $thisquerysucks result that we are looking for?

Thanks in advance

[Dog Cow]

Do I read this query right? You want a list of post IDs which are approved, from an ordinary topic in a given forum, but which do not appear in the fun table? Could you post a bit more about what this query is supposed to do?

[JLA]

[Dog Cow]

Well, as you probably know, joins aren't fun on big tables.

Are you going to run this query with a LIMIT?

I would suggest the following:

- If there are a lot of posts in $funforum, put a limit x,y on the query and run the query in batches

- forget about joining in the topics table and use PHP instead to skip over results that aren't POST_NORMAL

- forget about the post_approval condition and use PHP to skip over posts that aren't approved

Up to now, these suggestions will make your query look like this:

$sql = "SELECT p.post_id, p.post_approval
FROM " . POSTS_TABLE . " p
LEFT JOIN " . FUN_TABLE . " ON p.post_id = " . FUN_TABLE . ".post_id
WHERE p.forum_id = $funforum
AND " . FUN_TABLE . ".post_id IS NULL
LIMIT $offset, $limit";

On your posts table, you're going to want a composite index that consists of forum_id, post_id, in that order. If you have an index that contains more columns already, and starts with forum_id, post_id, then that will work. Fun table should, obviously, have an index on post_id.

Now, filtering out topics that aren't POST_NORMAL is a bit trickier, but if assumptions prove correct, it won't be too bad. First we assume that your $funforum doesn't have too many stickies or announcements, and then we assume that those topics don't have too many posts. We can then run a query to join the topics and posts table to get all of the post IDs from those topics. We can then put those post IDs in an array and use in_array() to check and skip over them when looping over the results from the first query.

[drathbun]