Wedge

Public area => The Pub => Bug reports => Topic started by: Arantor on March 22nd, 2012, 12:27 PM

Title: Board order issues
Post by: Arantor on March 22nd, 2012, 12:27 PM

I'm really not sure why this is the case but it needs fixing. Some of these boards I'm about to mention are not public visible.

Board index lists Public Area category as:
 - The Pub
     FAQs
     Features
     Bug reports
     Plugins
     Off-topic
     SMF
 - Dev Blog
 - Salvaged Topics
 - Salvaged Board

Going to The Pub itself also shows its sub-boards correctly, as does the view in Boards & Categories. But if I view the Public Area category as a whole[1] I get a very different order.

 - Salvaged Topics
 - Salvaged Board
 - The Pub
     FAQs
     Off-topic
     Features
     SMF
     Plugins
     Bug reports
 - Dev Blog


Now, I'm not 100% sure but for each level of hierarchy, that would be consistent with MyISAM ordering rather than correct ordering (i.e. order of creation).

I'm not sure how best to fix this. Part of me says we could just fix it by using ORDER BY board_order but I have a feeling we might be better served using Mark Rose's suggestion and applying the board order to the primary key (so that we end up with id_board as a UNIQUE, but board_order, id_board as physical primary key, so that when it's reordered, regardless of whether that's MyISAM[2] or InnoDB[3] it works properly)

1.	Also, why doesn't the header on the board index link to the category itself? I think it really should.
2.	MyISAM orders by creation, but there's a physical ALTER TABLE to reorder it in the manage boards code.
3.	InnoDB orders by primary key, so making the board_order the first half of the primary key, it will automatically be sorted into order.

Title: Re: Board order issues
Post by: Pandos on March 22nd, 2012, 12:36 PM

Yes. Had the same problem by switching to INOODB a long time ago.
Just set the Primary Key to board_order AND id_board.
Works great.

Title: Re: Board order issues
Post by: Arantor on March 22nd, 2012, 12:39 PM

There are other issues with merely doing that, namely that it screws up other optimisations in the pile because MySQL considers a composite key from left to right.

Title: Re: Board order issues
Post by: Pandos on March 22nd, 2012, 12:57 PM

I can give you a dump of my structure from this table. Perhaps it is usefull for you.

Code: [Select]

CREATE TABLE `smf_boards` (  `id_board` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `id_cat` tinyint(4) unsigned NOT NULL DEFAULT '0', `child_level` tinyint(4) unsigned NOT NULL DEFAULT '0', `id_parent` smallint(5) unsigned NOT NULL DEFAULT '0', `board_order` smallint(5) NOT NULL DEFAULT '0', `id_last_msg` int(10) unsigned NOT NULL DEFAULT '0', `id_msg_updated` int(10) unsigned NOT NULL DEFAULT '0', `member_groups` varchar(255) NOT NULL DEFAULT '-1,0', `id_profile` smallint(5) unsigned NOT NULL DEFAULT '1', `name` varchar(255) NOT NULL, `description` text NOT NULL, `num_topics` mediumint(8) unsigned NOT NULL DEFAULT '0', `num_posts` mediumint(8) unsigned NOT NULL DEFAULT '0', `count_posts` tinyint(4) NOT NULL DEFAULT '0', `id_theme` tinyint(4) unsigned NOT NULL DEFAULT '0', `override_theme` tinyint(4) unsigned NOT NULL DEFAULT '0', `countMoney` tinyint(1) unsigned NOT NULL DEFAULT '1', `lastUpdated` varchar(255) NOT NULL DEFAULT '', `redirect` varchar(255) NOT NULL DEFAULT '', `unapproved_posts` smallint(5) NOT NULL DEFAULT '0', `unapproved_topics` smallint(5) NOT NULL DEFAULT '0', `rateTopic_board_enable` tinyint(4) NOT NULL DEFAULT '0', PRIMARY KEY (`board_order`,`id_board`), UNIQUE KEY `id_board_2` (`id_board`), KEY `ID_PARENT` (`id_parent`), KEY `ID_MSG_UPDATED` (`id_msg_updated`), KEY `memberGroups` (`member_groups`(48)), KEY `categories` (`id_cat`), KEY `ID_BOARD` (`id_board`), KEY `ID_CAT` (`id_cat`) ) ENGINE=InnoDB AUTO_INCREMENT=680 DEFAULT CHARSET=latin1 AUTO_INCREMENT=680 ;


Title: Re: Board order issues
Post by: Arantor on March 22nd, 2012, 01:00 PM

Quote

PRIMARY KEY (`board_order`,`id_board`), UNIQUE KEY `id_board_2` (`id_board`)

You've done exactly what I was speculating about: created a composite key on the board_order + id_board with a unique key on just the id_board to preserve other optimisations :)

Thanks for posting, that gives me a lot more reassurance about doing it in Wedge now :)

Title: Re: Board order issues
Post by: Pandos on March 22nd, 2012, 01:04 PM

Always glad to help. :)

Title: Re: Board order issues
Post by: Arantor on March 29th, 2012, 02:00 AM

I've committed this in r1519, so that should fix it - but it needs Nao to apply the changes here before I can say this one is fixed.

Title: Re: Board order issues
Post by: Pandos on March 29th, 2012, 07:35 AM

By the way. For use with InnoDB the categories table must be updated too:

Code: [Select]

ALTER TABLE `$prefix_categories` ADD UNIQUE KEY (`id_cat`), DROP PRIMARY KEY, ADD PRIMARY KEY ( `cat_order`, `id_cat` ), ENGINE=InnoDB;

Title: Re: Board order issues
Post by: Arantor on March 29th, 2012, 12:01 PM

Oh, I didn't know about that part, thanks for letting me know.

(Of course, I need to fix it in the installer, which means changing the original key definitions rather than writing change statements.)

Title: Re: Board order issues
Post by: Nao on March 29th, 2012, 10:01 PM

Just FYI I applied the database structure change manually this morning. Did it fix the problem as expected?

Title: Re: Board order issues
Post by: Arantor on March 29th, 2012, 10:19 PM

No, it hasn't. How did you apply the changes, exactly?

(You may have to move a board, then move it back to force MySQL to rebuild the indexes, assuming it's in a MyISAM table)

Title: Re: Board order issues
Post by: Pandos on March 29th, 2012, 10:27 PM

Quote from Arantor on March 29th, 2012, 10:19 PM

(You may have to move a board, then move it back to force MySQL to rebuild the indexes, assuming it's in a MyISAM table)

exactly :)

Title: Re: Board order issues
Post by: Nao on March 29th, 2012, 10:58 PM

Through admin? I'll let you do it. Cant access my pc tonight ;)

Title: Re: Board order issues
Post by: Arantor on March 29th, 2012, 11:05 PM

I've moved a board from admin, moved it back.

There were other places that weren't quite right - namely the jumpto could be misordered at times and so could move-topics. But the category page is still wrong, even if everything else is correct, which says to me there is something wrong with the category listing page itself. I'm doing some other oddments tonight, so I'll look at that code too.

Title: Re: Board order issues
Post by: Nao on March 29th, 2012, 11:13 PM

Okay ;)

My, we still have a lot of small bugs to fix... :(

Title: Re: Board order issues
Post by: Arantor on March 29th, 2012, 11:14 PM

We do, but with any luck I can start getting back on them now I'm a lot more settled in and have no plans to go out for the next couple of days :)

Title: Re: Board order issues
Post by: Nao on March 29th, 2012, 11:19 PM

I'll be busy tomorrow -- will be at the premiere for Saint Seiya Omega. :)
So I probably won't be available after 3pm until later in the night.
I'll try to work on some of the bugs tomorrow morning though.

Title: Re: Board order issues
Post by: Nao on April 4th, 2012, 11:05 AM

Is this one fixed or not.?

Title: Re: Board order issues
Post by: Arantor on April 4th, 2012, 11:34 AM

I need to recheck, but after I did the move/move back, it was still wrong in certain places like categories. :/

Did you also apply the extra change regarding the category table?

Title: Re: Board order issues
Post by: Nao on April 5th, 2012, 12:32 PM

cat_order? Yep... (Long ago.)

Posted: April 4th, 2012, 12:06 PM

Bump?
(Oh there are so many posts I'm waiting for someone to answer really... :P)

Title: Re: Board order issues
Post by: Arantor on April 6th, 2012, 04:32 PM

It's still broken but I have no idea why - the category codepath uses the same codepath as the boards one but it's still out of order.

Also, I think there might be a problem if the category is collapsed but you still try going to the category page itself, you'll just get that category header on its own without any way to uncollapse it.

Title: Re: Board order issues
Post by: Nao on April 6th, 2012, 10:49 PM

So that's a bug right...?

As for board order -- what exactly should be expected?
BTW, I updated the site to rev 1545.

Title: Re: Board order issues
Post by: Arantor on April 6th, 2012, 10:53 PM

Well, the problem is as my first post - if you look at the Public Area category's boards on the main board index, they're in a different order to when you view them in the category itself.

It's right everywhere *except* the category specific page.

Posted: April 6th, 2012, 10:51 PM

Btw, I've locally fixed the issue regarding categories being hidden and collapsed at the same time.

Title: Re: Board order issues
Post by: Nao on April 6th, 2012, 11:41 PM

And so it's also a SMF bug, right?

Title: Re: Board order issues
Post by: Arantor on April 6th, 2012, 11:50 PM

Well, yes and no. The symptoms of misordering are indeed an SMF bug - and applying the changes fixes where they occur in SMF. But the category specific listing is not an SMF feature and that's the part that's broken.

Title: Re: Board order issues
Post by: Nao on April 8th, 2012, 05:36 PM

But isn't it using the same functions as the board index..?
I'm sure it's an easy fix?

Title: Re: Board order issues
Post by: Arantor on April 8th, 2012, 06:13 PM

It's running the exact same query with one difference - only the category gets an extra clause in the WHERE.

The 'obvious' answer would be to ORDER BY board_order but that will add an overhead that isn't needed (since it works naturally elsewhere)

Title: Re: Board order issues
Post by: Nao on April 12th, 2012, 02:32 PM

Considering that clicking on a category in the linktree (which is AFAIK the only way to access a category page) is not something people will do 70 times a day, I added the ordering to the category listing...

I'm not sure why it does the sorting naturally if b.id_board isn't tested against, but it doesn't really matter. It's not going to make the forum any slower anyway...

Title: Re: Board order issues
Post by: Arantor on April 12th, 2012, 03:16 PM

Other than using a filesort on that page >_< But it isn't likely to be MANY calls (other than search engines)

Title: Re: Board order issues
Post by: Nao on April 12th, 2012, 03:29 PM

Not only that, but any given category is unlikely to have tons of boards in it.
So, a filesort on.... a dozen entries at best? It's not going to kill performance, is it...?

Title: Re: Board order issues
Post by: Arantor on April 12th, 2012, 03:35 PM

But that's still a filesort per time it's viewed - and it is an I/O hit that's not very nice. I don't think it's a performance killer but we should be mindful of it. What it might be worth doing is figuring out whether it's worth doing in PHP rather than MySQL.

Title: Re: Board order issues
Post by: Nao on April 12th, 2012, 04:02 PM

The issue with performance is that when you start, you never know how much you're going to gain...

For instance, saving 95% performance on timeformat() is great. Realizing that this means saving less than a millisecond sucks...
Saving 5% in parse_bbc() for that matter, would be much nicer ;)

In our case though... If we're going to worry about filesorts every time we change a query, I don't know about you but I'll just stop doing queries altogether...

Title: Re: Board order issues
Post by: Arantor on April 12th, 2012, 04:16 PM

But I do worry about it :P But really worrying about it at this stage is not a huge deal, we can always fix it up later if we have better ideas :) (Beta is when we really need to worry about performance)

Title: Re: Board order issues
Post by: Nao on April 12th, 2012, 04:40 PM

I guess so.

I just don't want to be pushed back by things like that. I'm already worried enough about game changers like the privacy query. (which I'm always postponing as you can see...)

Title: Re: Board order issues
Post by: Arantor on April 12th, 2012, 06:11 PM

Don't let yourself be pushed back by it, I'll worry about it :)

Title: Re: Board order issues
Post by: Nao on April 12th, 2012, 07:07 PM

Well, after all this time I still don't know whether to use an extra table, or several, or none, and whether to use membergroups for everything, etc, etc... It feels like it's going to be my second 'impossible task' after my failure to make a decision on the AeMe-powered attachment system. (Even though Noisen.com is quite happy to run with my original privacy code...)

Title: Re: Board order issues
Post by: Arantor on April 12th, 2012, 07:48 PM

There's no best solution at all, only a variety of least worst solutions that all have their up-sides and down-sides to them.

Title: Re: Board order issues
Post by: Nao on April 12th, 2012, 09:51 PM

BTW the query seems to execute in a millisecond or two... Really not worth worrying ;)

What does this mean in explain?

Using where; Using filesort

Does it mean it uses an index and then a filesort on the entries it found?

Title: Re: Board order issues
Post by: Arantor on April 12th, 2012, 10:25 PM

No, that means it's not using any index to filter records (because it's using the WHERE to isolate, rather than an index), and then the results are sorted on a filesort.

Just to clarify, when you normally do sorting, you create a temporary table in memory, perform the sort and return results. When you invoke something that has a text column into a sort, it cannot use a temporary table in memory and does so on disk - by comparison this is much slower. (Note that varchar columns only create on-disk tables if there isn't enough memory to do the sorting in RAM, whereas a text column will always generate a filesort)