This topic was marked solved by its starter, on April 1st, 2014, 08:07 PM
« Let's discuss privacy openly!
[Poll] Board status icons: what's the point?
Weaving feedback »
Poll

Fix or remove the "unread" status?

Fix!
3 (17.6%)
Remove and replace with what you suggested!
13 (76.5%)
Remove everything!
1 (5.9%)
Total Members Voted: 14

Pandos

  • Living on the edge of Wedge
  • Posts: 635
[Poll] Re: Board status icons: what's the point?
« Reply #75, on April 7th, 2014, 07:08 PM »
I already have the (your) new query running without using filesorts :)
Subselects are really better than joins.

So in my upcoming commit there will be some changes to the database. Adding a view indexes to harmonize this query and to get rid of filesorts (yeah, if data is indexed, MySQL is smart enough to use them. Even subselect ones).
Code: [Select]
$where = $is_boards ? 'id_board' : 'id_topic';
$request = wesql::query('
  SELECT COUNT(DISTINCT m.id_msg) AS co, m.id_board
  FROM {db_prefix}messages AS m
  WHERE m.poster_time >= (UNIX_TIMESTAMP(NOW()) - 30 * 24 * 3600)
      AND (
        m.id_msg >
        IFNULL(
        (SELECT lt.id_msg FROM {db_prefix}log_topics AS lt WHERE (lt.id_topic = m.id_topic AND lt.id_member = 1)),
        IFNULL(
        (SELECT lmr.id_msg FROM {db_prefix}log_mark_read AS lmr WHERE (lmr.id_board = m.id_board AND lmr.id_member = 1)),
                0
            )
          )
        )
        ORDER BY ' . $where . ' DESC',
array(
'id_member' => MID,
'has_unread' => $has_unread
)
);

[Poll] Re: Board status icons: what's the point?
« Reply #76, on April 7th, 2014, 07:21 PM »
Another way is to write it all in a Cookie. But the disatvantage is that if someone is logged with another Computer, all topics are unread again  :yahoo:
# dpkg-reconfigure brain
error: brain is not installed or configured

Nao

  • Dadman with a boy
  • Posts: 16,079
[Poll] Re: Board status icons: what's the point?
« Reply #77, on April 7th, 2014, 09:34 PM »
Yeah it's a nono.

Tried both subselect and left join queries. Without any restrictions, the former seems to be twice slower. Are you sure it's really better..? More readable yeah but better I don't know.

Also why did you remove the group by? PhP parsing isn't the best...

Pandos

  • Living on the edge of Wedge
  • Posts: 635
[Poll] Re: Board status icons: what's the point?
« Reply #78, on April 7th, 2014, 10:02 PM »Last edited on April 7th, 2014, 10:13 PM
Not only more readable.
Sub-select was more efficient for this query in my tests.
More efficient means also it takes less CPU to perform. And (at this time) we use it for a limited dataset.
So it should be OK. If you misbehave, feel free to leave it by left join.
Forgot to mention: to get this query using the index, you must post something to start the magic :)

ORDER BY / GROUP BY:
I've changed it because we don't aggregating that much data ( sum(), max(), avg() ).

So here's the point:
If we're using this query with "ORDER BY", we get the full benefit from index.
If we're using GROUP BY, the query will start a partial filesort.

You can give it a shot and look how it's performing here on Wedge. The changes can easily be reverted:)

Nao

  • Dadman with a boy
  • Posts: 16,079
[Poll] Re: Board status icons: what's the point?
« Reply #79, on April 10th, 2014, 12:20 AM »
Quote from Pandos on April 7th, 2014, 10:02 PM
Not only more readable.
Sub-select was more efficient for this query in my tests.
Last test I did on wedge.org had an average query time of 0.15s for LEFT JOIN, and 0.4s for the sub-select. :-/
Quote from Pandos on April 7th, 2014, 10:02 PM
Forgot to mention: to get this query using the index, you must post something to start the magic :)
But Wedge (and SMF) ALWAYS have at least one post at install time..?
Quote from Pandos on April 7th, 2014, 10:02 PM
ORDER BY / GROUP BY:
I've changed it because we don't aggregating that much data ( sum(), max(), avg() ).
I don't get how your updated query (the one your pushed to GH) would work. It doesn't do the same thing as my 'naos' query...
Quote from Pandos on April 7th, 2014, 10:02 PM
So here's the point:
If we're using this query with "ORDER BY", we get the full benefit from index.
If we're using GROUP BY, the query will start a partial filesort.
Unless it does a preliminary sort on id_msg > a_certain_date... In which case, I'm not seeing 'using filesort' in the explain column. I told you, MySQL optimization is still quite an unknown for me. (I probably should read the book Pete sent me after he left Wedge, but... Ahem... It's in such a poor condition, I've never tried to use it. Its place should be in the trashcan, honestly.)
Quote from Pandos on April 7th, 2014, 10:02 PM
You can give it a shot and look how it's performing here on Wedge. The changes can easily be reverted:)
Yup...

Pandos

  • Living on the edge of Wedge
  • Posts: 635
[Poll] Re: Board status icons: what's the point?
« Reply #80, on April 10th, 2014, 09:06 AM »Last edited on April 10th, 2014, 09:28 AM
OK, just a simple comparison.
First the new query with indexes:

Code: [Select]
SELECT COUNT(DISTINCT m.id_msg) AS co, m.id_board
FROM wedge_messages AS m
WHERE m.poster_time >= (UNIX_TIMESTAMP(NOW()) - 30 * 24 * 3600)
AND (
m.id_msg >
IFNULL(
(SELECT lt.id_msg FROM wedge_log_topics AS lt WHERE (lt.id_topic = m.id_topic AND lt.id_member = 1)),
IFNULL(
(SELECT lmr.id_msg FROM wedge_log_mark_read AS lmr WHERE (lmr.id_board = m.id_board AND lmr.id_member = 1)),
0
)
)
)
ORDER BY id_board DESC
Quote
in .../gz/app/Subs.php Zeile 1390, beträgt 0.00048804 Sekunden bei 0.05243492 der Abfrage.
Now see how it's used:

Code: [Select]
id   select_type                           table      type   possible_keys   key   key_len   ref   rows   Extra
1   PRIMARY                                 m        index      m                   17              2     Using where; Using index

OK, let's take a look at he actual query:

Code: [Select]
SELECT COUNT(DISTINCT m.id_msg) AS co, m.id_board
FROM wedge_messages AS m[color=black]
   LEFT JOIN wedge_log_topics AS lt ON (lt.id_topic = m.id_topic AND lt.id_member = 1)
   LEFT JOIN wedge_log_mark_read AS lmr ON (lmr.id_board = m.id_board AND lmr.id_member = 1)
WHERE m.id_board IN (1, 2)
   AND (m.id_msg > IFNULL(lt.id_msg, IFNULL(lmr.id_msg, 0)))
GROUP BY m.id_board
   ORDER BY null
Quote
in .../gz/app/Subs.php line 1383, which took 0.00051093 seconds at 0.05717301 into request.
Now how it's used:
Code: [Select]
id select_type table type    possible_keys key key_len ref rows Extra
1 SIMPLE         m        ALL PRIMARY,id_board                   3     Using where; Using filesort

I think we should give it a try.
Quote
But Wedge (and SMF) ALWAYS have at least one post at install time..?
Yes, but to get the index begin to work you'll have to post one NEW message :)

If you want to stick with left joins, we could try to adding new indexes to DB.
Because the difference isn't that much. Isn't it?

Auk

  • Can I get a Box?
  • Posts: 64
[Poll] Re: Board status icons: what's the point?
« Reply #81, on April 10th, 2014, 10:19 PM »Last edited on April 10th, 2014, 10:42 PM
Lol, I do not use this feature at all. Other than to see the pretty icons, if they are any unique.

Users (myself included) on really big forums might not even care to read the many forum categories on the board index page. On a busy forum, it might be a bit too much to read/catch up. So without actually reading through all the topics, this feature effectively becomes useless, especially with the time since last message is posted as Nao pointed out.

Other than forum "lock", "redirect" icons, It's just for decorations. I will not miss this feature. So let's just have: Normal/Nao's idea, locked and redirect (and anything else other than new/old.)

Other uses of that feature, for a "themed" forum, transparent icons can be replaced with something cool (For example, planets with a tiny bar beneath, the bar goes up as planet gets bigger) if you're into Black Nova Traders game. :)

Nothing is more despicable than respect based on fear.

Nao

  • Dadman with a boy
  • Posts: 16,079
[Poll] Re: Board status icons: what's the point?
« Reply #82, on April 16th, 2014, 04:51 PM »
I've tweaked this to search on unread posts since last visit, instead of a month ago. This allows me to avoid adding an extra index to the message table, which is already overcrowded as it is, ah ah...

Anyway, I realize that it's all looking like we're back to square one (only two icon states: read posts or unread posts), but at least now I'll be able to show a clear number of unread posts, unlike previously!

Nao

  • Dadman with a boy
  • Posts: 16,079
« Let's discuss privacy openly!
Weaving feedback »