Awaiting More Details Missing index xf_xa_ubs_comment

[Kirby]

UBS Version

2.3.8

XenForo Version

2.3.6

PHP Version

8.3.20

Database & Version

MariaDB 10.11.11

Are there any errors being throw?

No

Table xf_xa_ubs_comment seems to be missing an index on blog_entry_id, comment_state which causes massive scans

 

[Bob]

Table xf_xa_ubs_comment seems to be missing an index on blog_entry_id, comment_state which causes massive scans

View attachment 10545

Can you screenshot me what indexes you have on the comment table please? TY

 

[Bob]

seems to be missing an index on blog_entry_id, comment_state which causes massive scans

I can certainly add that index for you

 

[Kirby]

The schema how it was before posting this bug report:

CREATE TABLE `xf_xa_ubs_comment` (
  `comment_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `blog_entry_id` int(10) unsigned NOT NULL DEFAULT 0,
  `user_id` int(10) unsigned NOT NULL DEFAULT 0,
  `username` varchar(50) NOT NULL DEFAULT '',
  `comment_date` int(10) unsigned NOT NULL DEFAULT 0,
  `comment_state` enum('visible','moderated','deleted') NOT NULL DEFAULT 'visible',
  `message` mediumtext NOT NULL,
  `reaction_score` int(11) NOT NULL DEFAULT 0,
  `reactions` blob DEFAULT NULL,
  `reaction_users` blob NOT NULL,
  `warning_id` int(10) unsigned NOT NULL DEFAULT 0,
  `warning_message` varchar(255) NOT NULL DEFAULT '',
  `attach_count` int(10) unsigned NOT NULL DEFAULT 0,
  `ip_id` int(10) unsigned NOT NULL DEFAULT 0,
  `last_edit_date` int(10) unsigned NOT NULL DEFAULT 0,
  `last_edit_user_id` int(10) unsigned NOT NULL DEFAULT 0,
  `edit_count` int(10) unsigned NOT NULL DEFAULT 0,
  `embed_metadata` blob DEFAULT NULL,
  PRIMARY KEY (`comment_id`),
  KEY `comment_date` (`comment_date`),
  KEY `comment_id_comment_date` (`comment_id`,`comment_date`),
  KEY `user_id` (`user_id`)
)

As can be seen from the screenshot, the query generated by

/** @var \XenAddons\UBS\Entity\Comment[] $comments */
$commentList = $commentRepo->findCommentsForContent($blogEntry)
    ->limitByPage($page, $perPage);

$comments = $commentList->fetch();

which is used to load the comments queries the table by blog_entry_id (and comment_state) but there is no usable index so the whole table is scanned.

Index comment_id_blog_entry_id_comment_date (which doesn't exist on my DB?) isn't used for this query as comment_id is not part of where clause, it might work with an index hint though (I didn't test this).

It might also make sense to drop comment_id from the indexes as the primary key is implicitly part of all indexes anyway (at least with InnoDB).

Either way, IMHO there needs to be some kind of index which includes blog_entry_id to avoid scanning the whole table.

 

[Bob]

Either way, IMHO there needs to be some kind of index which includes blog_entry_id to avoid scanning the whole table.

If you have time, could you create a couple new indexes on your comments table and do a quick test, then let me know the index (or indexes) you'd like me to add Really appreciate all the Help Kirby!

 

[Kirby]

Sure, just let me know what you want to test.