Wedge

I don't know... there are serious benefits to ordering by physical id but note that I'm not talking about the same thing that you are.

If you filter by id_board + some kind of filter (which I'd make a dual index), you can still sort by id_topic which can be regularly increasing. But it does, as you say, cause trouble for importing just as it can for merging/external importing stuff.

In an ideal world, I'd just sort by time and be done with it but the performance aspects are awkward - InnoDB physically arranges rows by primary key as opposed to MyISAM ordering by creation and where it'll fit in the table - but I don't think we can do much about changing it, I think we're going to have to swallow the penalty of ordering by time, or we don't do this using the boards/topics tables.

- Ordering by poster_time means adding an extra key, although in the messages table there's already a surprising amount of keys...

- My main concern with converters is that TE isn't around much these days... Hasn't been online in weeks, hasn't posted in a month. I'd rather have media albums converted to floating boards at import time -- if anything, we can always remove the items from around here, or even do the conversion manually...
So, who writes the converter code, eh eh...

- I suppose it's okay doing everything by id if topics can be filtered by board type... In which case they'll always be sorted correctly.

- I don't even know if it's best to have an album = a board, or a gallery = a board... If an album = a board, then an item = a topic. Let's consider that if a topic is sticky or something, it means it's an album topic, not an item topic. If an album = a topic, then we could have it done this way: any replies = album comment, new items are posted as a special post in these topics, and from within the item page, if you comment on the item, it actually posts a reply to the album topic but it's set to have the item's special post as its PARENT internally... See? A threaded discussion. It's harder to manipulate though (moving items around, deleting them...), but I find the idea amusing.

Well, here's the thing. There's ordering topics by time/date and ordering posts by time/date, the latter certainly would likely be able to be indexed and the query for identifying posts based on that should be comparable with the current query (since the message ids can be found from the index), and requires no changes anywhere to queries other than to change the field we're pointing at.

Now, in the message index, the primary methods of sorting are by first post or last post in a thread (barring sticky), and though there are others, in virtually every case we'll be using those. The problem is that that those fields are not part of the topics table, and that means an extra join that we might need to work into the mix, or juggle the existing (complicated) joins. (And we need to apply this to the join made for the previous/next topics too)

The bottom line is that it will have a performance impact spread across more places - unless we keep the first post's and last post's time in the topics table too (and have an index on there too)


The reason I stepped back on the whole floating-topics idea is because of the debate we had before about having non-contiguous ids, e.g. board 1 is a default board, board 3 is the first user created board, and board 2 can't be used by default by the user - something similar will happen here.

I'd rather make an album as a type of board, because what we're talking about is a container that contains one or more content items - which means making the album's comments into a sticky topic. It does make some of the management complicated, but we can suitably manage that.

Re: sorting, I was definitely thinking about adding a poster_time field to the topic table as well, so it'd at least wouldn't require a join just to do the sorting... There aren't that many places where a rewrite is required...?
I didn't think about the last poster time though, sounds like a good idea...
Anyway, the topic table is definitely one of the least polluted in the entire table list. Most of its data is taken from its id_first_msg indeed, but I'm sure we could benefit from moving some of this data into the table.
Heck, I'm sure we could even benefit from moving the subject into the topic... :P Maybe even remove it from the post table but I'm sure some people would kill me for that :lol:

I don't mind using entire boards for an album. I just hope you realize that it'll make the topic table grow way more quickly than if we stored one album per topic :)

it would save a sql join for fringe cases where only the subject is needed from a specific topic.

I don't know, I've just never been a big fan of storing subjects when most of the time they're the same as the topic's...
Heck, I don't remember if Wedge stores the subject if it's the same. I think the Noisen codebase stores an empty subject or just 'Re:' in that case, but I'm not sure.

If you move the poster_time into the topics table, you have to do it for both the first and last posts in the topic, and you have to remember to update these when posting, but since you're already doing that to set up the first/last post id that's no biggie. (But we need to keep those too.)

You could only really benefit in moving if you can avoid the join to the messages table in the process, and that's already done in some places anyway. But I'm not a fan of moving the subject there.

While I couldn't care less about the subject being settable per post (and I have used it very sparingly), I do care about the performance impact that will have, as I observed with SimpleDesk; the topics table is fixed width, means it's *very* fast to traverse and look up, especially when you get into the realms of sorting. If you pull non-fixed-width content into that, it starts to hurt, and bear in mind there are several places that makes a difference (message index, display for the topic and prev/next for the topic come to mind)Yes - but also, that *all* becomes searchable, for free. I consider that a bonus.

Searchable, yes, it's a bonus. Heck it was my main point for going floating actually :P

How about we make the messages table fixed-width, if it's faster? I'm sure we could move non-fixed data to a secondary table :lol:
j/k, although we could use a few minutes of our time to evaluate that ;)

Having two tables with that content in is probably not worth the effort, to be honest.

The big hits on the table, really, are figuring out which messages to show (which is done by evaluating against the primary key in a range, at present) and then loading pretty much entire rows with the primary key.

What I would be in favour of doing is removing the participation index and actually moving that to a discrete table which allows for proper unfollowing in unread replies, for example.