2011-01-10, 07:23 PM
Pages: 1 2
2011-01-10, 07:28 PM
It's not a rewrite of the feature actually. It's an optimization effecting an important function. And waiting till 2.0 is not an option imho. This should be carefully reviewed asap. This can be improved in some form without the end results being different imho. There has been complaints for years about how this feature worked. I've personally never looked into it but I see it clearly now.
2011-01-10, 09:24 PM
issues like this bring up several DB/query design issues in MyBB.
You'd see some tiny gains if the t.closed field was a simple enumerated integer (-1 for moved, 0 for open, 1 for closed) and a separate field for the new thread ID instead of the varchar it is now that is being searched lexically via LIKE 'moved|%' (which happens A LOT). When you need moved info, that new field would be in the threads query result anyway as the get threads type queries return * anyway, so no loss there.
searches on indexed integer fields are the fastest, so why not make use of them?
Also, since this function labrocca is commenting on uses WHERE t.fid IN ($fid) so MySQL needs to optimize the query for an array search, but in reality, the function only accepts a SINGLE FID, so why not help things along with a simple WHERE t.fid={$fid} ?
I see these type of things once and a while when I am building plugins and digging into the core code, but my site is not busy enough to be impacted so I ignore them
You'd see some tiny gains if the t.closed field was a simple enumerated integer (-1 for moved, 0 for open, 1 for closed) and a separate field for the new thread ID instead of the varchar it is now that is being searched lexically via LIKE 'moved|%' (which happens A LOT). When you need moved info, that new field would be in the threads query result anyway as the get threads type queries return * anyway, so no loss there.
searches on indexed integer fields are the fastest, so why not make use of them?
Also, since this function labrocca is commenting on uses WHERE t.fid IN ($fid) so MySQL needs to optimize the query for an array search, but in reality, the function only accepts a SINGLE FID, so why not help things along with a simple WHERE t.fid={$fid} ?
I see these type of things once and a while when I am building plugins and digging into the core code, but my site is not busy enough to be impacted so I ignore them
2011-01-10, 09:36 PM
Yeah I've been bothered by all the NOT LIKE 'moved|%' I see too. It's a lot of them but to mess with that is pretty extensive at this point to alter. But little functions like this one we're discussing can be changed or at least reviewed and optimized.
2011-01-11, 12:01 AM
have you looked at your debug info to see how this query is performing compared to other ones on the forumdisplay.php page? curious to see how its time is compared to the other queries.
2011-01-11, 01:02 AM
Well now it's fine. But you have to realize that with HF I'm doing 1000-3000 queries per second. If a page takes 20 queries and I can take away just 2 of them I'm saving 10% of my resources and load on the DB server. I'm at that level where saving one query a page matters. I'm doing a million page views every day.
Even one bad query and my server goes nuts. I was perfect on 1.4. Immediately with 1.6x all went to hell. I've been struggling ever since with performance problems. This is a brand new dual quad core (8 cores) with 18gb ram running the database.
I moved mybb_threadsread into InnoDB and it's performing better. I removed the new breadcrumb popup navigation. I'm at the point where I might just go one step more and rebuild my MyBB and split my site from the tree. And no I don't mean make a public fork. I'm not interested in doing that at all. I just want my site to run efficiently under MyBB.
Even one bad query and my server goes nuts. I was perfect on 1.4. Immediately with 1.6x all went to hell. I've been struggling ever since with performance problems. This is a brand new dual quad core (8 cores) with 18gb ram running the database.
I moved mybb_threadsread into InnoDB and it's performing better. I removed the new breadcrumb popup navigation. I'm at the point where I might just go one step more and rebuild my MyBB and split my site from the tree. And no I don't mean make a public fork. I'm not interested in doing that at all. I just want my site to run efficiently under MyBB.
2011-01-11, 02:32 AM
I hear ya, and am glad I don't have the issues you do (though I would love that traffic!)
I am not sure what the issue is, as this particular query is the same as in 1.4 (at least when I look at my debug info on my site) so why you are now feeling this is an issue is odd. Has one of your other changes impacted this query?
Has the way your users navigate changed any such that forumdisplay.php is being viewed more often?
Moving threadsread and forumsread to HEAP may be even better. If the server goes down or hiccups those tables, what is the loss? If you site moves that fast, a visitor being gone 30 minutes will result in most being new stuff unread anyway.
I am not sure what the issue is, as this particular query is the same as in 1.4 (at least when I look at my debug info on my site) so why you are now feeling this is an issue is odd. Has one of your other changes impacted this query?
Has the way your users navigate changed any such that forumdisplay.php is being viewed more often?
Moving threadsread and forumsread to HEAP may be even better. If the server goes down or hiccups those tables, what is the loss? If you site moves that fast, a visitor being gone 30 minutes will result in most being new stuff unread anyway.
2011-01-12, 02:26 AM
I can't understand why it would have been put down to 100 in the first place? Labrocca's explanation seems exactly right, why trawl 100 threads when you could just look at one. Nice find.
2011-01-12, 03:22 AM
looking at the queries again, the first one for guests needs a LIMIT since there are no constraints and the number of possible results is large.
however, there is no need to increment the counter as once you get the first hit, you know there are new posts, so once you enter the TRUE portion of the IF statement, you can return from the function with a 1 value instead of counting all the unread of that 100 results.
regarding the second, larger query, there is no limit needed as it is an aggregate function and will always return a single value
however, there is no need to increment the counter as once you get the first hit, you know there are new posts, so once you enter the TRUE portion of the IF statement, you can return from the function with a 1 value instead of counting all the unread of that 100 results.
regarding the second, larger query, there is no limit needed as it is an aggregate function and will always return a single value
Pages: 1 2