
Welcome to all phpBB2 Refugees! This site is intended to continue support for the legacy 2.x line of the phpBB2 bulletin board package. If you are a fan of phpBB2, please, by all means register, post, and help us out by offering your suggestions. We are primarily a community and support network. Our secondary goal is to provide a phpBB2 MOD Author and Styles area. |
|
Author |
Message |
JLA Board Member

Joined: 30 Apr 2009
        Posts: 298 Location: U.S.A

|
Posted: Tue Aug 09, 2011 9:38 pm Post subject: PROBLEM MYSQL Query |
|
|
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 _________________ http://www.jlaforums.com |
|
Back to top |
|
 |
Dog Cow Board Member

Joined: 18 Nov 2008
         Posts: 378

|
Posted: Mon Aug 22, 2011 9:21 pm Post subject: Re: PROBLEM MYSQL Query |
|
|
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? _________________ Moof!
Lincoln's Tomb, Oak Ridge Cemetery, Springfield IL • Mac 512K Blog • Mac GUI |
|
Back to top |
|
 |
JLA Board Member

Joined: 30 Apr 2009
        Posts: 298 Location: U.S.A

|
Posted: Tue Aug 23, 2011 2:53 am Post subject: Re: PROBLEM MYSQL Query |
|
|
Dog Cow wrote: | 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? |
Yes, that is correct. It will take the result set and pull some info later on for inclusion about the posts into the fun table. _________________ http://www.jlaforums.com |
|
Back to top |
|
 |
Dog Cow Board Member

Joined: 18 Nov 2008
         Posts: 378

|
Posted: Tue Aug 23, 2011 3:18 pm Post subject: Re: PROBLEM MYSQL Query |
|
|
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. _________________ Moof!
Lincoln's Tomb, Oak Ridge Cemetery, Springfield IL • Mac 512K Blog • Mac GUI |
|
Back to top |
|
 |
drathbun Board Member

Joined: 24 Jul 2008
         Posts: 653 Location: Texas

|
Posted: Wed Nov 09, 2011 6:00 pm Post subject: Re: PROBLEM MYSQL Query |
|
|
JLA wrote: | 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 |
JLA, not sure if you're still working this issue, but I find that removing an outer (left) join and replacing it with a second query is often more effective. However in this case you're looking for missing rows, not trying to preserve rows that would otherwise drop out because of missing data. What we really need is a MINUS operator for MySQL; maybe Oracle will slide it in at some point. Without a MINUS operator, you could try a NOT IN but that also can be problematic. The challenge is you're not using an index, since you can't index NULL or missing rows.
What sort of volume to you expect to get back? Meaning how many rows would your normal result set contain? Hundreds? Thousands? Millions? In other words, how many rows are typically missing from the fun table at any given point in time?
Dog Cow wrote: | Fun table should, obviously, have an index on post_id. |
Which I'm sure it does, but it won't help in this case because we are looking for rows that do not exist, and therefore by definition will not appear in the index. _________________ phpBBDoctor Blog |
|
Back to top |
|
 |
|
Not affiliated with or endorsed by the phpBB Group
Powered by phpBB2 © phpBB Group
Generated in 0.0100 seconds using 15 queries. (SQL 0.0022 Parse 0.0002 Other 0.0076) |
phpBB Customizations by the phpBBDoctor.com
Template Design by DeLFlo and MomentsOfLight.com
|
|