• From: Ottawa, Canada
  • Registered: 2006-09-05
  • Posts: 308

Topic: SQL Statement optimization

I'm working on a really "new posts" thing, instead of the "since last visit" stuff.

Anyways... Here's the line, and it works... but I'm wondering if there's a better way of making it (read: optimize for speed and memory usage). It's from search.php below "if ($action == 'show_new')"

$result = $db->query('SELECT t.id FROM '.$db->prefix.'topics AS t INNER JOIN '.$db->prefix.'forums AS f ON f.id=t.forum_id LEFT JOIN '.$db->prefix.'forum_perms AS fp ON (fp.forum_id=f.id AND fp.group_id='.$pun_user['g_id'].') WHERE (fp.read_forum IS NULL OR fp.read_forum=1) AND NOT EXISTS (SELECT pr.tid FROM posts_read AS pr WHERE pr.tid=t.id AND pr.uid='.$pun_user['id'].')') or error('Unable to fetch topic list', __FILE__, __LINE__, $db->error());

The only thing I've done is add a table, "posts_read (uid INTEGER, tid INTEGER)" uid is the user id of someone who has read the tid, and the tid is you guessed it, the topic id. What happends (or will happen, soon as I finish up) is as viewtopic.php is run, a (uid, tid) pair is added to the table (if it doesn't already exist). As new topics are created nothing need be done. When search.php?action=show_new is run, only the topic id's that the user is allowed to see are returned, and only if the (uid tid) pair for that topic doesn't already exist.

I'm also thinking of inverting it, so that as new messages are created every user get's a (uid, tid) pair added to the posts_read table (or posts_unread, in that case). In the inverted sence, finding new posts would be relatively fast ("SELECT t.* FROM topics AS t INNER JOIN posts_unread AS p LEFT JOIN (permission stuffs) WHERE (more permission stuff) p.uid=CUR_USER_ID p.tid=t.id") and removing the "new post" status would be fast too "DELETE FROM posts_unread WHERE uid=CUR_UID and tid=CUR_TID". There are two downsides to this, the obvious one, new topic meens NUM_OF_REGISTERED_USERS entries created in the database, new posts, same thing, edits, same thing. Then the less obvious... as the number of inactive members grows, the size of the database starts to grow.

Another way would be to have a (uid, tid, timelastviewed); but you wouldn't be able to read the first 3 posts and leave the forth and fifth unread...

echo "deadram"; echo; fortune;
  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: SQL Statement optimization

1. You're using a subselect, that won't work anywhere but recent versions of MySQL (I believe, I don't know if/how other DBMS systems have implemented subselects)
2. Don't join the forums table, use topics.forum_id

  • From: Ottawa, Canada
  • Registered: 2006-09-05
  • Posts: 308

Re: SQL Statement optimization

1) You calling this a subselect EXISTS (SELECT blah)? sqlite3 supports it, dono about sqlite; but I figured if I could do it in sqlite3, I could do it anywhere... I'd have to do it as a few lines of php if that wasn't there... (trust me, I've tried) which meens slowing it down hmm

2) lol, yah, I was to busy trying to get it to work, to think of the simple stuff ~.~ lol, almost always the case, for everyone big_smile

echo "deadram"; echo; fortune;
  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: SQL Statement optimization

deadram wrote:

1) You calling this a subselect EXISTS (SELECT blah)? sqlite3 supports it, dono about sqlite; but I figured if I could do it in sqlite3, I could do it anywhere... I'd have to do it as a few lines of php if that wasn't there... (trust me, I've tried) which meens slowing it down hmm

Sorry, was thinking of something else, the exists query should work fine wink

  • From: Ottawa, Canada
  • Registered: 2006-09-05
  • Posts: 308

Re: SQL Statement optimization

Sweet big_smile cause I got it working... had to track down a bug with topics that got moved more then once... but it's working on my site atm big_smile
When I get a chance I'll try to post it on punres, dono if there's gonah be an install_mod.php or not though ;p I am rather lazy

echo "deadram"; echo; fortune;