Un-answeredTopics for SMF 2.0?

[Arantor]

The unread code itself is in need of optimisation anyway. But that's not what I'm getting at here.

Since the original code was identified, I rewrote it to tie into the topics table first and then join (because that's much faster than going the other way and for the situation in question it's fine to do that)

I assumed that someone running a PHP help website would be able to apply the same logic to this query as to the previous query. But someone tweaking and changing code without any proper ability to benchmark the changes is bordering on the wrong side of competence.

I rarely write something optimised the first time. I test it, throw data at it, and then I sit and check what's actually going on. I turn on benchmarking, check the query plans, see what indexes it's using and in what order and change the query to suit. In the case above, most of the subsequent changes (like the duplicate joins) will mostly get optimised out by the query planner anyway so the overhead is not nearly as significant as first thought and in almost every case, the 'it's faster now' is almost certainly placebo effect.

[margarett]

...the 'it's faster now' is almost certainly placebo effect.

I get that a LOT

Ty again. I will try to implement that myself

[Phphelp]

@Arantor

Again, I'm at work from 9-5 everyday Eastern Standard time. I don't have the ability to remote Desktop out from this location to my test servers to test anything, unless I sit in our QA Lab, which is on a different wing in this building.

But I do have the ability to look over queries and FTP while I'm at work, I'm not ignoring your advice. I just don't have that Luxury at this time.  Improvements can be made through discussions and partially tested until I'm able to properly test it.

I'm not an SMF Guru by any means, I never studied the SMF Database Diagram or the code base other then for minor tweaks here and there.  I'm a .NET Developer by trade and I'm very good with SQL, Database design, and profiling.

I just don't have the ability to do so at this time and I also don't want to change the fundamental database design of SMF and underlying code to support a new design, which would hamper future upgrades.

I respect your opinion and help, but by no means am I ignoring any of your suggestions. I'm just merely using the only tool sets, I currently have available at this time.

[Arantor]

Improvements can be made through discussions and partially tested until I'm able to properly test it.

Bad idea as pointed out. You can't partially test it without actually understanding what you're testing or whether you're actually improving it. I still reckon most of your improvements are placebo.

I respect your opinion and help, but by no means am I ignoring any of your suggestions. I'm just merely using the only tool sets, I currently have available at this time.

Sorry, but that's BS. If you're able to modify the files where this stuff is, to be able to try changing queries, how do you not have the ability to turn on the debug features? I even told you how to turn them on, as it's an SMF feature and not anything else.

Anyway, best of luck with this.

[Phphelp]

If you really want to examine it, turn on full DB logging (add $db_show_debug = true; to Settings.php), grab the query actually being used and put an EXPLAIN in front of it to find out what indexes it's using.

I get it, I can turn on the debugging, I can get the queries.  Then I can to run that query in the database directly with the "EXPLAIN" on it.  I don't have the ability to run the query with the explain on it.  Unless I code a seperate PHP file and run it seperately, then keep manipulating everything I want to do through database commands to view and see everything I want. I guess it's do-able.  I rather just do it when I have access to MySQL Developer when I get home, it's much easier and less frustrating that way to me.

Drop the hate, there is no reason for it. No one is dis-respecting your knowledge or trivializing your input.

[Arantor]

Then I can to run that query in the database directly with the "EXPLAIN" on it.

You can even click on it to get the EXPLAIN right there too... just by clicking on it in the debug log.

Oh, and I'm not hating, this is just frustration of dealing with people who want to improve things but don't want to do it properly.

[Phphelp]

I wonder how high I can get @Arantor blood pressure!

[Phphelp]

I did my analysis and on my site.  I'm going with these two queries as being the best performance based on explain plan and overall performance sampling. As we all know depending on the amount of data in every table can skew and be fined tuned for individual message boards. I did rewrite the queries multiple ways.  Each of the queries below do execute in under .1 seconds and I'm completely happy about that.  I'm not saying there are not other ways to restructure the database to improve performance.  I was happy with the 1-2 second page speed and just as happy with the under .5 second page speed.

@Arantor  Thanks for your help in teaching me the default debugging capabilities in SMF and the entertainment.

@margarett Thank you for whipping up this very useful mod and teaching me how SMF Code is designed, I did learn a lot from your 2 iterations of code and hope to in the future be able to create my own SMF mods.

My experience with SMF is under a month - I'm rapidly learning it. 

Code Select Expand

$request = $smcFunc['db_query']('', '
SELECT COUNT(m.id_msg) as total
FROM {db_prefix}messages as m
INNER JOIN {db_prefix}topics AS t ON (t.id_topic = m.id_topic)
WHERE t.id_board IN ({array_int:list_boards})
AND t.approved = 1
AND t.num_replies = 0
AND m.poster_time > {int:time_limit}',
array(
'list_boards' => $boards_to_access,
'time_limit' => $timeLimit,
)

and

Code Select Expand

$request = $smcFunc['db_query']('', '
SELECT
m.id_msg, m.subject, m.smileys_enabled, m.poster_time, m.body, m.id_topic, t.id_board, b.id_cat,
b.name AS bname, c.name AS cname, t.num_replies, m.id_member, m.id_member AS id_first_member,
IFNULL(mem.real_name, m.poster_name) AS first_poster_name, t.id_first_msg,
IFNULL(mem.real_name, m.poster_name) AS poster_name, t.id_last_msg
FROM {db_prefix}messages AS m
INNER JOIN {db_prefix}topics AS t ON (t.id_topic = m.id_topic)
INNER JOIN {db_prefix}boards AS b ON (b.id_board = t.id_board)
INNER JOIN {db_prefix}categories AS c ON (c.id_cat = b.id_cat)
LEFT JOIN {db_prefix}members AS mem ON (mem.id_member = m.id_member)
WHERE t.id_board IN ({array_int:list_boards})
    AND  t.id_first_msg = m.id_msg
AND t.approved = 1
AND t.num_replies = 0
AND m.poster_time > {int:time_limit}
ORDER BY m.id_msg DESC
LIMIT {int:offset}, {int:limit}',

array(
'list_boards' => $boards_to_access,
'time_limit' => $timeLimit,
'offset' => $_REQUEST['start'],
'limit' => 10,
)

[margarett]

You're welcome. I'm far from a developer but getting your hands dirty is the best way to understand how it works.
I'm pretty sure that most of my logics can be processed in many other ways. But, for me, that's logical and step-by-step enough.

[Phphelp]

I made one more additional adjustments to prevent locked topics from showing, didn't want to show topics that couldn't be answered.

Here are the queries with the additional condition.

Code Select Expand

$request = $smcFunc['db_query']('', '
SELECT COUNT(m.id_msg) as total
FROM {db_prefix}messages as m
INNER JOIN {db_prefix}topics AS t ON (t.id_topic = m.id_topic)
WHERE t.id_board IN ({array_int:list_boards})
AND t.approved = 1
AND t.locked = 0
AND t.num_replies = 0
AND m.poster_time > {int:time_limit}',
array(
'list_boards' => $boards_to_access,
'time_limit' => $timeLimit,
)
);

Code Select Expand

$request = $smcFunc['db_query']('', '
SELECT
m.id_msg, m.subject, m.smileys_enabled, m.poster_time, m.body, m.id_topic, t.id_board, b.id_cat,
b.name AS bname, c.name AS cname, t.num_replies, m.id_member, m.id_member AS id_first_member,
IFNULL(mem.real_name, m.poster_name) AS first_poster_name, t.id_first_msg,
IFNULL(mem.real_name, m.poster_name) AS poster_name, t.id_last_msg
FROM {db_prefix}messages AS m
INNER JOIN {db_prefix}topics AS t ON (t.id_topic = m.id_topic)
INNER JOIN {db_prefix}boards AS b ON (b.id_board = t.id_board)
INNER JOIN {db_prefix}categories AS c ON (c.id_cat = b.id_cat)
LEFT JOIN {db_prefix}members AS mem ON (mem.id_member = m.id_member)
WHERE t.id_board IN ({array_int:list_boards})
    AND  t.id_first_msg = m.id_msg
AND t.approved = 1
AND t.locked = 0
AND t.num_replies = 0
AND m.poster_time > {int:time_limit}
ORDER BY m.id_msg DESC
LIMIT {int:offset}, {int:limit}',

array(
'list_boards' => $boards_to_access,
'time_limit' => $timeLimit,
'offset' => $_REQUEST['start'],
'limit' => 10,
)
);


[margarett]

Easy, right?

[PCNetSpec]

Is this ever likely to get bundled into an installable mod ?

[margarett]

Maybe.
I will do it when I have enough free time (judging by my current workload, should happen in the year 2037 )

Everyone is free for picking it up and make a mod out of it

[Arantor]

I'd considered doing it but I'd actually be doing profiling and stuff. Then again after the attitude in this thread (because actually caring about performance is apparently entertainment), I don't know if I can be bothered.

[PCNetSpec]

How about if I lighten the mood a little, maybe with a dance or something ? .. on second thoughts you really wouldn't want to see that 

(Teaser)

Come to think of it, it would probably make a better threat than offer.

Thanks guys, I'll cross my fingers and hope for the best

[kimyaci]

If you open admin topics, these topics do not appear to be better.How we do it? Thanks.

[margarett]

Hum?

[roza]

Thank you dear @margarett and @Phphelp and sorry about posting on old topic. I am using this useful trick too. Would you please update those attached files of first margarett's post so that they include the adjustments have been provided in subsequent posts? To be honest, I did not understand how to do with those adjustments! I just want it to not show the locked topics and empty pages.
Also It would be much better if instead of displaying the entire contents of Topics the index of them be displayed on the page same as the default pages of "unread posts since last visit" and "new replies to your posts".I appreciate if you add this option for me please.
With this poor English I hope you get what I mean! Thanks in advance.

[margarett]

Hi.

Apart from the locked thing, the files attached here should work fine (the pagination thing is fixed), even if with some performance hit
http://www.simplemachines.org/community/index.php?topic=510124.msg3605476#msg3605476

I could pack the changes but I'm honestly out of time to properly test things

[roza]

Thanks for your quick reply @margarett. I used that file and pagination issue has been solved but my bigger problem is that I have some very large locked topics about help and rules of my forum which have been set as read-only and now they fill the entire page of unanswered topics. I see you're busy and maybe I expect too much, but I'm not in a hurry and can wait until you have enough time. You've already helped me too and I really appreciate the time you spend to help me generously. I can also make the necessary changes myself if you put me in the right direction.For example if I know in what section of Unanswered.php and how I should edit/add the codes provided in following quote, I'll do that myself! I wish "Phphelp" had been specified which code should be found and replaced with what code or he would determine the where(before or after something!) those codes should be added!

I made one more additional adjustments to prevent locked topics from showing, didn't want to show topics that couldn't be answered.

Here are the queries with the additional condition.

Code Select Expand

$request = $smcFunc['db_query']('', '
SELECT COUNT(m.id_msg) as total
FROM {db_prefix}messages as m
INNER JOIN {db_prefix}topics AS t ON (t.id_topic = m.id_topic)
WHERE t.id_board IN ({array_int:list_boards})
AND t.approved = 1
AND t.locked = 0
AND t.num_replies = 0
AND m.poster_time > {int:time_limit}',
array(
'list_boards' => $boards_to_access,
'time_limit' => $timeLimit,
)
);

Code Select Expand

$request = $smcFunc['db_query']('', '
SELECT
m.id_msg, m.subject, m.smileys_enabled, m.poster_time, m.body, m.id_topic, t.id_board, b.id_cat,
b.name AS bname, c.name AS cname, t.num_replies, m.id_member, m.id_member AS id_first_member,
IFNULL(mem.real_name, m.poster_name) AS first_poster_name, t.id_first_msg,
IFNULL(mem.real_name, m.poster_name) AS poster_name, t.id_last_msg
FROM {db_prefix}messages AS m
INNER JOIN {db_prefix}topics AS t ON (t.id_topic = m.id_topic)
INNER JOIN {db_prefix}boards AS b ON (b.id_board = t.id_board)
INNER JOIN {db_prefix}categories AS c ON (c.id_cat = b.id_cat)
LEFT JOIN {db_prefix}members AS mem ON (mem.id_member = m.id_member)
WHERE t.id_board IN ({array_int:list_boards})
    AND  t.id_first_msg = m.id_msg
AND t.approved = 1
AND t.locked = 0
AND t.num_replies = 0
AND m.poster_time > {int:time_limit}
ORDER BY m.id_msg DESC
LIMIT {int:offset}, {int:limit}',

array(
'list_boards' => $boards_to_access,
'time_limit' => $timeLimit,
'offset' => $_REQUEST['start'],
'limit' => 10,
)
);