Fixed XFMG 1.0.2 CPU intensive queries, maybe add some indexes?

imthebest

Well-known member
#1
When viewing the album list for a member (around 40% CPU usage on a i7 2600):

1.png

When viewing a media item (around 60% CPU usage on a i7 2600):

2.png

Thanks,
Super120
 

imthebest

Well-known member
#3
There isn't a "xengallery" table but there are the following:

Code:
| xengallery_add_map  |
| xengallery_album  |
| xengallery_album_permission  |
| xengallery_album_view  |
| xengallery_album_watch  |
| xengallery_category  |
| xengallery_category_map  |
| xengallery_category_watch  |
| xengallery_comment  |
| xengallery_content_tag  |
| xengallery_content_tag_map  |
| xengallery_exif  |
| xengallery_exif_cache  |
| xengallery_field  |
| xengallery_field_category  |
| xengallery_field_value  |
| xengallery_media  |
| xengallery_media_user_view  |
| xengallery_media_view  |
| xengallery_media_watch  |
| xengallery_private_map  |
| xengallery_rating  |
| xengallery_shared_map  |
| xengallery_user_tag  |
| xengallery_watermark
show create table [table_name];

@Xon please let me know what to put in [table_name].
 

Xon

Well-known member
#4
@Xon please let me know what to put in [table_name].
Sorry, misread the screenshot.

You will want to check xengallery_comment

I don't have XFMG, so you'll need to check the zip for an install file which should have the xengallery_comment table schema in it. I'm suspecting the indexes where not applied after an upgrade.
 

imthebest

Well-known member
#5
Here you go:

Code:
| xengallery_comment | CREATE TABLE `xengallery_comment` (
  `comment_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `content_id` int(10) unsigned NOT NULL DEFAULT '0',
  `content_type` enum('media','album') NOT NULL DEFAULT 'media',
  `message` mediumtext NOT NULL,
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
  `username` varchar(50) NOT NULL DEFAULT '',
  `ip_id` int(10) unsigned NOT NULL DEFAULT '0',
  `comment_date` int(10) unsigned NOT NULL DEFAULT '0',
  `comment_state` enum('visible','moderated','deleted') NOT NULL DEFAULT 'visible',
  `rating_id` int(10) unsigned NOT NULL DEFAULT '0',
  `likes` int(10) unsigned NOT NULL DEFAULT '0',
  `like_users` blob,
  `warning_id` int(10) unsigned NOT NULL DEFAULT '0',
  `warning_message` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`comment_id`)
) ENGINE=InnoDB AUTO_INCREMENT=46205 DEFAULT CHARSET=utf8 |
Do it looks fine?
 

Chris D

XenForo developer
Staff member
#6
There is likely an index missing there, for some reason. I think we need an index on content_id and content_type, at least.

I'll do some profiling of the xengallery_album table, that doesn't quite seem right.
 

SneakyDave

Well-known member
#9
I think Chris is saying that the comment table needs some more indexes added as part of the add-on, not just particularly on your install. That would probably help it run queries better.

My table just has the one index also. Nothing to do with an import.

edit: ninja'd
 
Top