phpBB2Refugees.com Logo
Not affiliated with or endorsed by the phpBB Group

Register •  Login 

Continue the legacy...

Welcome to all phpBB2 Refugees!Wave Smilie

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.

PROBLEM MYSQL Query


 
Search this topic... | Search phpBB2 Discussion... | Search Box
Register or Login to Post    Index » phpBB2 Discussion  Previous TopicPrint TopicNext Topic
Author Message
JLA
Board Member



Joined: 30 Apr 2009

Posts: 310
Location: U.S.A


flag
PostPosted: 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


flag
PostPosted: 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: 310
Location: U.S.A


flag
PostPosted: 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


flag
PostPosted: 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: 663
Location: Texas


flag
PostPosted: 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
Display posts from previous:   
Register or Login to Post    Index » phpBB2 Discussion  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT
 
Jump to:  

Index • About • FAQ • Rules • Privacy • Search •  Register •  Login 
Not affiliated with or endorsed by the phpBB Group
Powered by phpBB2 © phpBB Group
Generated in 0.0258 seconds using 15 queries. (SQL 0.0015 Parse 0.0008 Other 0.0236)
phpBB Customizations by the phpBBDoctor.com
Template Design by DeLFlo and MomentsOfLight.com Moments of Light Logo