LUM_UserDiscussionWatch size issues

ddc · September 2006

As much as i hate my first post here to be a moan, since im loving vanilla from the features/functionality side. Basically, is it just me, or is having a record per person, per thread in the database causing ridiculously huge tables? We run an xbox forum with about 2500 members and 4500 topics (not exactly huge by forum standards) and this leads to a table with 12,000,000 records in the database, pushing the database as a whole to over half a gig in size. basically, i was just wondering whether, a) this is actually an issue, and im not being an idiot, and b) there's a fix in the works i'm more than happy to rip the forum to pieces and redo the read posts functionality myself, but if there's a plan to do it in the official release i'd obviously rather use that cheers,

WallPhone · September 2006

The rows in that table itself are very small. (60 bytes on my DB). I wouldn't be suprised if they take a very small fraction of the DB size. Also, those numbers assume that EVERY member would have read EVERY thread at least once...

One thing you can do easily is purge the table from records older than a some arbitrary date:

DELETE FROM LUM_UserDiscussionWatch
WHERE LastViewed < '2005-01-01'

I haven't tested this, so buyer beware.

ddc · September 2006

i did that, but it breaks things like show unread for the members whose records you delete. and yes, the rows are small, but 12,000,000 of them pushed our database up to 500MB. even after removing all records from before june this year, its 121MB. surely assuming that the worst case scenario will never happen is bad (even though this time i made the worst case scenario happen by marking all threads read), and maybe this method requires a rethink?

WallPhone · September 2006

Yah, that would do it. Mark reported that the DB for lussumo is about 10 MB, and he had 1673 topics at the time and I suspect just about as many users. My guess is it still is under 20 MB today, with almost as many topics as your forum.

I have a mark all read extension in the planning stages that stores a date in the user table. After that date, all threads show as read, before it, the UserDiscussionWatch table shows read/unread status. Never really been motivated to complete it.

ddc · September 2006

thats what i was thinking about doing, if you want to send it across, i'd be more than happy to get it finished. better than starting from scratch :P

WallPhone · September 2006

Sure thing. Be warned, this is my first attempt at an extension, and the code is ugly, and doesn't use some standard Vanilla constructs or classes that may make things easier, I.E. ajax switching, like is done for bookmarks.

<?php
/*
Extension Name: Thread Read
Extension Url: http://edacio.us/
Description: Adds a 'Mark all discussions read' link to the panel.
Version: 0.1 Beta
Author: WallPhone
Author Url: http://edacio.us/

*/


$Context->Dictionary['ThreadRead_Group']    = 'Mark as read:';
$Context->Dictionary['ThreadRead_All']      = 'Mark all discussions read';
$Context->Dictionary['ThreadRead_Category'] = 'Current category';
$Context->Dictionary['ThreadRead_Thread']   = 'Mark as read';
$Context->Dictionary['ThreadRead_UAll']     = 'Undo mark all read';
$Context->Dictionary['ThreadRead_UCat']     = 'Undo mark category read';
$Context->Dictionary['ThreadRead_UThread']  = 'Mark as unread';
//$Context->Dictionary['ThreadRead_'] = '';


// Attach to the required Vanilla objects

$SB = &$DiscussionManager->DelegateParameters['SqlBuilder'];





$Context->AddToDelegate('Discussion', 'GetStatus', 'ThreadRead_Discussion_SetAsRead');


// add the mark all read link to the panel
 if (($Context->SelfUrl == "account.php") | ($Context->SelfUrl == "index.php") && ($Context->Session->UserID > 0))
 {

//   if (no new threads) {
//      $PanelString = '<h1><a href="extensions/MarkAllRead/default.php?r=' . $Context->Session->UserID . '">' . $Context->Dictionary['MarkAllRead_Undo'] . '</a></h1>';
//   } else (
      $PanelString = '<h1><a href="extensions/ThreadRead/default.php?u=' . $Context->Session->UserID . '">' . $Context->Dictionary['ThreadRead_All'] . '</a></h1>';
//   )


      $Panel->AddListItem($Context->Dictionary['Options'],$Context->Dictionary['ThreadRead_All'],"?PostBackAction=MarkCategoryRead&CategoryID=".$CategoryID,"","id=\"MarkCategoryRead\"");
      $Panel->AddListItem($Context->Dictionary['Options'],$Context->GetDefinition('MarkAllRead'),"?PostBackAction=MarkAllRead","","id=\"MarkAllRead\"");



//   $Panel->AddString ($PanelString, 2);
 }



// Update the database, and 302 back to the referring page.
//header("HTTP/1.0 302 Found")
//header("Location: http://www.example.com/")



// Add the mark all read datetime column to the users table
// TODO:  Lookup the table prefix

// $s = $this->Context->ObjectFactory->NewContextObject($this->Context, 'SqlBuilder');
// $PlanB = 'ALTER TABLE `LUM_User` ADD `MarkedAllRead` DATETIME DEFAULT NULL ;';




// Change the status string to show read posts after the last 'Mark All Read' click
function ThreadRead_Discussion_SetAsRead(&$Discussion)
{

	// Get the current status string
	$StatusString = &$Discussion->DelegateParameters['StatusString'];



	$SB = $this->Context->ObjectFactory->NewContextObject($this->Context, 'SqlBuilder');
	$SB->SetMainTable('User', 'u');

	// Get the date the mark was called
	$SB->AddSelect('MarkedAllRead', 'u');
   $SB->AddWhere('u', 'UserID', '', $Context->Session->UserID, '=');

	$Context->Database->Select($SB, 'ThreadRead', 'ThreadRead_Discussion_SetAsRead', 'An error occurred while retrieving discussions.');


//  	@$DataSet['MarkedAllRead']

//			$sql = 'SELECT MarkDate FROM '. $Context->Configuration['DATABASE_TABLE_PREFIX'] .'_USER
//	        WHERE uid = '. $Context->Session->UserID ;
	//  Oddly this doesn't get the configuration setting, nor the UID. (scope issue?)

//	mysql_query($sql) or die('Query "'. $sql .' Failed: '. mysql_error());

   if ( $this->LastViewed > $LastViewedDate )
	{
	   $StatusString = str_replace("NewComments", "NoNewComments", $StatusString);
   	$StatusString = str_replace("Unread", "Read", $StatusString);
	}

	$StatusString .= " Testing...";
}


?>

ddc · September 2006

no problem, its my first attempt too, i'll see what i can do. /me goes to read vanilla documentation

Minisweeper · September 2006

I have sometimes wondered why it doesnt use a date to work stuff out but Mark's usually a couple of steps ahead and nearly always has his reasons for doing stuff the way he does it. In any case I'd be seriously surprised if that was the reason for your huge database as Wallphone suggests. I host a forum for a guy on here who's got 1700 topics and is shifting about 3-4gig of data a month purely through forum use and his database was still only about 10meg last time i checked...

ddc · September 2006

no really, it IS the reason. that table was about 500meg big. admittedly, it was me that populated the table with every user and discussion topic, as otherwise users would have had 4500 threads that they'd read showing as unread. even after dropping it down to users who had visited in the last 3months, about 600, the database is 121meg. it is a nice way to do things, as there's the potential to store more data about how much of each thread a user has read (rather than just tying it to last login), but obviously more data = more disk space. however, an extension should fix it for those who want the potential to mark all read and are willing to drop the (potentially) higher functionality of the current system :)

Mark · September 2006

Have you tried this extension? http://lussumo.com/addons/?PostBackAction=AddOn&AddOnID=51 You don't need to mark all discussions read for every user for all time. You only need to do it up to a certain date in the past. That extension does it pretty well. My reasons for not trying to optimize the size of that table are (in no particular order): 1. I don't have a Mark All Read option on here, and I don't plan on having one any time soon. So, my watch table doesn't get that big. 2. Query speed. The discussion list has to be the most complex query in Vanilla, and I've done everything I can to make it fast. I played with using limiting dates, etc - it slowed things down. If someone comes up with a new way that doesn't slow things down, I'm all for it.

ddc · September 2006

yeah, i used that one, it wasn't particularly robust. if it saw anything it didnt expect, it just died horribly after some hacking, i got it to vaguely do what it was supposed to. but basically, i do want to be able to mark ALL threads read, and since that requires extensions, i guess i'll be busy tomorrow :P cheers for the response though guys, really appreciate it