Advertisement:

Author Topic: Question regarding BoardIndex.php's SQL query  (Read 474 times)

Offline Virginiaz

  • Semi-Newbie
  • *
  • Posts: 40
Question regarding BoardIndex.php's SQL query
« on: March 08, 2018, 01:10:29 AM »
While monkeying around with some code, I noticed that even when a category is collapsed by the user and none of its boards are visible, it still pulls data for the board as if the category was expanded:

Code: [Select]
// Find all boards and categories, as well as related information.  This will be sorted by the natural order of boards and categories, which we control.
$result_boards = db_query("
SELECT
c.name AS catName, c.ID_CAT, b.ID_BOARD, b.name AS boardName, b.description,
b.numPosts, b.numTopics, b.ID_PARENT, IFNULL(m.posterTime, 0) AS posterTime,
IFNULL(mem.memberName, m.posterName) AS posterName, m.subject, m.ID_TOPIC,
IFNULL(mem.realName, m.posterName) AS realName," . ($user_info['is_guest'] ? "
1 AS isRead, 0 AS new_from" : "
(IFNULL(lb.ID_MSG, 0) >= b.ID_MSG_UPDATED) AS isRead, IFNULL(lb.ID_MSG, -1) + 1 AS new_from,
c.canCollapse, IFNULL(cc.ID_MEMBER, 0) AS isCollapsed") . ",
IFNULL(mem.ID_MEMBER, 0) AS ID_MEMBER, m.ID_MSG,
IFNULL(mods_mem.ID_MEMBER, 0) AS ID_MODERATOR, mods_mem.realName AS modRealName
FROM {$db_prefix}boards AS b
LEFT JOIN {$db_prefix}categories AS c ON (c.ID_CAT = b.ID_CAT)
LEFT JOIN {$db_prefix}messages AS m ON (m.ID_MSG = b.ID_LAST_MSG)
LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER = m.ID_MEMBER)" . (!$user_info['is_guest'] ? "
LEFT JOIN {$db_prefix}log_boards AS lb ON (lb.ID_BOARD = b.ID_BOARD AND lb.ID_MEMBER = $ID_MEMBER)
LEFT JOIN {$db_prefix}collapsed_categories AS cc ON (cc.ID_CAT = c.ID_CAT AND cc.ID_MEMBER = $ID_MEMBER)" : '') . "
LEFT JOIN {$db_prefix}moderators AS mods ON (mods.ID_BOARD = b.ID_BOARD)
LEFT JOIN {$db_prefix}members AS mods_mem ON (mods_mem.ID_MEMBER = mods.ID_MEMBER)
WHERE $user_info[query_see_board]" . (empty($modSettings['countChildPosts']) ? "
AND b.childLevel <= 1" : ''), __FILE__, __LINE__);


What I am trying to do is make it so a collapsed category does not pull any data about the boards in that category, since it's not necessary, but I also don't want to do this in a way that takes longer to process, as it would defeat the purpose of changing this (to optimize as best as possible).

If I do this, it stops querying the boards in the collapsed category, but then the category itself is no longer shown because at least 1 board in that category would need to be queried, so SMF gets the relevant category info. This I understand (changes below are bolded):

Quote from: Code
   // Find all boards and categories, as well as related information.  This will be sorted by the natural order of boards and categories, which we control.
   $result_boards = db_query("
      SELECT
         c.name AS catName, c.ID_CAT, b.ID_BOARD, b.name AS boardName, b.description,
         b.numPosts, b.numTopics, b.ID_PARENT, IFNULL(m.posterTime, 0) AS posterTime,
         IFNULL(mem.memberName, m.posterName) AS posterName, m.subject, m.ID_TOPIC,
         IFNULL(mem.realName, m.posterName) AS realName," . ($user_info['is_guest'] ? "
         1 AS isRead, 0 AS new_from" : "
         (IFNULL(lb.ID_MSG, 0) >= b.ID_MSG_UPDATED) AS isRead, IFNULL(lb.ID_MSG, -1) + 1 AS new_from,
         c.canCollapse, IFNULL(cc.ID_MEMBER, 0) AS isCollapsed") . ",
         IFNULL(mem.ID_MEMBER, 0) AS ID_MEMBER, m.ID_MSG,
         IFNULL(mods_mem.ID_MEMBER, 0) AS ID_MODERATOR, mods_mem.realName AS modRealName
      FROM {$db_prefix}boards AS b
         LEFT JOIN {$db_prefix}categories AS c ON (c.ID_CAT = b.ID_CAT)
         LEFT JOIN {$db_prefix}messages AS m ON (m.ID_MSG = b.ID_LAST_MSG)
         LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER = m.ID_MEMBER)" . (!$user_info['is_guest'] ? "
         LEFT JOIN {$db_prefix}log_boards AS lb ON (lb.ID_BOARD = b.ID_BOARD AND lb.ID_MEMBER = $ID_MEMBER)
         LEFT JOIN {$db_prefix}collapsed_categories AS cc ON (cc.ID_CAT = c.ID_CAT AND cc.ID_MEMBER = $ID_MEMBER)" : '') . "
         LEFT JOIN {$db_prefix}moderators AS mods ON (mods.ID_BOARD = b.ID_BOARD)
         LEFT JOIN {$db_prefix}members AS mods_mem ON (mods_mem.ID_MEMBER = mods.ID_MEMBER)
      WHERE NOT IFNULL(cc.ID_MEMBER, 0) AND $user_info[query_see_board]" . (empty($modSettings['countChildPosts']) ? "
         AND b.childLevel <= 1" : ''), __FILE__, __LINE__);


My question: Is there any way to only query 1 board for a collapsed category? So that SMF has the category info it needs, but doesn't waste time on the extra boards the user can't see?

I'm assuming not - at least in a way that doesn't add more overhead, but I figured I'd see if anyone had any ideas.

Offline drewactual

  • Jr. Member
  • **
  • Posts: 305
    • College Football Fan Site CFB51
Re: Question regarding BoardIndex.php's SQL query
« Reply #1 on: March 08, 2018, 08:47:57 AM »
you could add a simple if/else statement in it's stead, and have the 'if' point (php include) to another file where you parked the collapsed board, and it should fold right into the operation without a hitch... the initial load of the page wouldn't include the weight of the hidden boards until they are requested.

the category title and information can be hardcoded (html) in the page to denote it's information.

all that said- the data you're avoiding loading isn't that expensive- and likely only adds a single degree to the DOM, as the query is already underway as it pulls boardindex in... the more expensive items that will offer you worth while time reductions are the info center with it's stats, or the welcome center.  those are prime candidates to use the if/else tactics above in my humble opinion.
https://www.cfb51.com is a College Football Fan Site, Store, and Publisher, launched in July of 2017

Offline Virginiaz

  • Semi-Newbie
  • *
  • Posts: 40
Re: Question regarding BoardIndex.php's SQL query
« Reply #2 on: March 08, 2018, 05:45:13 PM »
thanks!

Offline albertlast

  • Development Contributor
  • Jr. Member
  • *
  • Posts: 330
Re: Question regarding BoardIndex.php's SQL query
« Reply #3 on: March 08, 2018, 11:44:44 PM »
When you optimize it's important to measure your changes,
my guess by your changes that ther is no runtime improvments.
Try to run you sql query directly at the database and look at the runtime.