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.

searching a member posts : post_id in (huge list) problem


 
Search this topic... | Search General Support... | Search Box
Register or Login to Post    Index » General Support  Previous TopicPrint TopicNext Topic
Author Message
Oelita
Board Member



Joined: 09 Feb 2012

Posts: 11
Location: Paris


flag
PostPosted: Tue Feb 14, 2012 9:53 am 
Post subject: searching a member posts : post_id in (huge list) problem

Hello,
While trying to resolve some performance problems on my forum, a member told me she had errors on searching for another member posts. On the member profile, she's clicking on the "all messages from this member"... for a person who has 34 000 posts.
I tried, and got a debug message with a huge search query :

SELECT pt.post_text, pt.bbcode_uid, pt.post_subject, p.*, f.forum_id, f.forum_name, t.*, u.username, u.user_id, u.user_sig, u.user_sig_bbcode_uid FROM phpbb_forums f, phpbb_topics t, phpbb_users u, phpbb_posts p, phpbb_posts_text pt WHERE p.post_id IN (....) ) AND pt.post_id = p.post_id AND f.forum_id = p.forum_id AND p.topic_id = t.topic_id AND p.poster_id = u.user_id ORDER BY p.post_time DESC LIMIT 0, 20

and in (...) comes a huge list of post numbers. 34 000 post id ? coming from the search result table.

This query sometimes gets ok, sometimes not, and I think it is a possible problem for my server. Even if it succeeds, it is then impossible to go to page 2 ("no results"), so it is useless anyway.

Did someone mod this search feature to avoid so many posts to be listed and searched that way ? Looking at the search.php code, I guess this kind of IN(...huge list...) can also be met with text search ?

_________________
Maximomes forum
Back to top
drathbun
Board Member



Joined: 24 Jul 2008

Posts: 663
Location: Texas


flag
PostPosted: Tue Feb 14, 2012 2:24 pm 
Post subject: Re: searching a member posts : post_id in (huge list) proble

You could provide a limit clause on the query so that only the most recent "X" posts are returned. To be honest, when someone does the "search user posts" they're not likely to read all 34,000 posts, right? icon_smile.gif That's one option. I have also increased the text field used to store search results to the next text level up. There is text, medium text, large text, or something like that. Given that you're already under some performance issues, I would suggest the limit solution instead.

Are you comfortable editing the php code for your board?

_________________
phpBBDoctor Blog
Back to top
Oelita
Board Member



Joined: 09 Feb 2012

Posts: 11
Location: Paris


flag
PostPosted: Tue Feb 14, 2012 3:41 pm 
Post subject: Re: searching a member posts : post_id in (huge list) proble

Yes, I would rather choose the limit option too. (but I didn't even think that there could be a limit to the text field, this is an explanation for the second page which doesn't function, thanks !)

I can edit the php code, no problem with that.

Searching the posts of a member seems to be :
$sql = "SELECT post_id
FROM " . POSTS_TABLE . "
WHERE poster_id IN ($matching_userids)";
(line 249 in my version)

but there is also another search later on, which could cause the same problem. and an "egosearch".

Should I add a " LIMIT 0,1000" at the end of $sql just before the "if ( !($result = $db->sql_query($sql)) )"
- on line 259, in the : if ( $search_id == 'newposts' || $search_id == 'egosearch' || ( $search_author != '' && $search_keywords == '' ) ) loop
- and on line 339, in the if ( $search_keywords != '' ) loop

_________________
Maximomes forum
Back to top
drathbun
Board Member



Joined: 24 Jul 2008

Posts: 663
Location: Texas


flag
PostPosted: Tue Feb 14, 2012 6:10 pm 
Post subject: Re: searching a member posts : post_id in (huge list) proble

"egosearch" is what happens when you click the link that says "view my posts."

I'll look at the code tonight and give you a suggestion to try if nobody else is able to provide a solution earlier.

_________________
phpBBDoctor Blog
Back to top
Oelita
Board Member



Joined: 09 Feb 2012

Posts: 11
Location: Paris


flag
PostPosted: Tue Feb 14, 2012 6:57 pm 
Post subject: Re: searching a member posts : post_id in (huge list) proble

Thank you !
_________________
Maximomes forum
Back to top
Oelita
Board Member



Joined: 09 Feb 2012

Posts: 11
Location: Paris


flag
PostPosted: Thu Feb 16, 2012 2:34 pm 
Post subject: Re: searching a member posts : post_id in (huge list) proble

I did the change, adding also an ORDER BY post_id DESC : I added a LIMIT 0,1000 to the SQL queries of the first part of the search, the part which looks for posts and store them in the search results table.
It seems Ok, We only see the 1000 more recents posts, now.
We'll see if we have less problems now accessing the DB.

_________________
Maximomes forum
Back to top
drathbun
Board Member



Joined: 24 Jul 2008

Posts: 663
Location: Texas


flag
PostPosted: Mon Feb 20, 2012 5:27 pm 
Post subject: Re: searching a member posts : post_id in (huge list) proble

There is already an option for an order by on the search screen. Does your "order by POST_ID desc" override that?
_________________
phpBBDoctor Blog
Back to top
Citrix
Board Member



Joined: 19 Feb 2012

Posts: 42



PostPosted: Tue Feb 21, 2012 8:44 pm 
Post subject: Re: searching a member posts : post_id in (huge list) proble

hmmmm.. this limit on Search should be a default behavior.
I have to take a look at my code and add that. Because soon or later, this search going to get out of hand.
Back to top
Oelita
Board Member



Joined: 09 Feb 2012

Posts: 11
Location: Paris


flag
PostPosted: Wed Feb 22, 2012 4:20 pm 
Post subject: Re: searching a member posts : post_id in (huge list) proble

drathbun wrote:
There is already an option for an order by on the search screen. Does your "order by POST_ID desc" override that?


Not quite.
These searches (for a person, or for some keywords) work in 2 steps : the first one searches for all the posts matching the search criteria, and then the second search finds whole data about these posts and orders them using the option you're talking about.

My update is acting on the first step (which had no limit and no order by at all) : I now look for the 1000 most recent posts matching the critera. And then, the second step orders them in a classical way.

So, there is an impact, of course, but not totally.
I guess I could upgrade my modification to better integrate this option in the first step too...

_________________
Maximomes forum
Back to top
Display posts from previous:   
Register or Login to Post    Index » General Support  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.0270 seconds using 15 queries. (SQL 0.0021 Parse 0.0009 Other 0.0240)
phpBB Customizations by the phpBBDoctor.com
Template Design by DeLFlo and MomentsOfLight.com Moments of Light Logo