Fixed Slow MySQL queries

[Rasmus Vind]

I am getting slow queries. Please take a look:

# Query_time: 3.333284  Lock_time: 0.000156 Rows_sent: 2000  Rows_examined: 534714
EXPLAIN SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF(user.username IS NULL, media.username, user.username) AS username,
                    attachment.attachment_id, attachment.data_id, attachment.attach_date,data.filename, data.file_size, data.file_hash, data.file_path, data.width, data.height, data.thumbnail_width, data.thumbnail_height
            FROM xengallery_media AS media
               
                    LEFT JOIN xengallery_album AS album ON
                        (album.album_id = media.album_id)
                    LEFT JOIN xengallery_album_permission as albumviewperm ON
                        (album.album_id = albumviewperm.album_id AND albumviewperm.permission = 'view')
                   
                    LEFT JOIN xengallery_category AS category ON
                        (category.category_id = media.category_id)
                    LEFT JOIN xf_user AS user ON
                        (user.user_id = media.user_id)
                    LEFT JOIN xf_user_profile AS user_profile ON
                        (user_profile.user_id = media.user_id)
                    LEFT JOIN xf_attachment AS attachment ON
                        (attachment.content_type = 'xengallery_media' AND attachment.attachment_id = media.attachment_id)
                    LEFT JOIN xf_attachment_data AS data ON
                        (data.data_id = attachment.data_id)
                WHERE (
                       
                        media.media_privacy = 'public'
                        OR IF(media.category_id > 0, media.category_id IN (1), NULL)) AND (media.media_state IN ('visible')) AND (IF(media.album_id > 0, album.album_state = 'visible', 1=1))
                ORDER BY media.media_date DESC, media.media_id DESC           
            LIMIT 2000;

It looks very much like MySQL has to do a full scan of the table to find the results for this very common query. Is there something I can do aside from deleting people's contributions or can you somehow optimize this? I wouldn't have a problem with sending you (in private) all my XFMG tables to help you optimize your queries.

 

[Chris D]

What pages specifically in your gallery are executing this query? As I'm using your Gallery it seems to be fairly responsive in loading pages. Furthermore, we certainly don't execute any queries that would return 2000 rows; though this looks like an EXPLAIN query which presumably you have run yourself? In addition to an example where query execution seems to be slow within the add-on, do you have any examples from your slow query log which represent an actual query run by the add-on?

 

[Rasmus Vind]

Oh right, sorry about that. I added the EXPLAIN myself before pasting it in here to try to debug it myself. I am not sure where this query is being run, but I can tell you it happens quite often. Always at least 3 seconds to run the query. The slow query log does not tell where it happens and I can't find 2000 anywhere in the code. I am at a loss.

 

[Chris D]

Do you have any other add-ons which extend the gallery functionality? I couldn't find it in the code either, so I'm not totally sure where it is happening, I'll take another look...

 

[Rasmus Vind]

I got something. It's in find-new. It only happens when I am logged in. I disabled DEBUG mode again. Only keep it on for short time periods. Thanks for being so responsive.

I see its fast now but it might be cached right now in MySQL.

 

[Chris D]

Do you happen to have the "Maximum Number of Search Results" set to 2000? If so, then the explain text of that option very significantly applies here...

This number reflects the maximum number of search results that will be found, before permissions are taken into account. Setting this too high may cause performance problems.

If you don't have a particular reason to have it at such a high number then I recommend reducing it back down to its default (which is 200).

 

[Mike]

Can you check your indexes on the xengallery_media table? There's an open bug where the media_date index may be missing depending on versions used: https://xenforo.com/community/threa...edia_date-doesnt-exist-in-table-media.125808/

If you do have it, this may be a case where we need to give MySQL an index hint.

 

[Rasmus Vind]

Okay, I just ran this query:

SELECT SQL_NO_CACHE media.*
    ,
        album.*, albumviewperm.*,
        category.*,
        user.*, user_profile.*, IF(user.username IS NULL, media.username, user.username) AS username,
        attachment.attachment_id, attachment.data_id, attachment.attach_date,data.filename, data.file_size, data.file_hash, data.file_path, data.width, data.height, data.thumbnail_width, data.thumbnail_height
FROM xengallery_media AS media
 
        LEFT JOIN xengallery_album AS album ON
            (album.album_id = media.album_id)
        LEFT JOIN xengallery_album_permission as albumviewperm ON
            (album.album_id = albumviewperm.album_id AND albumviewperm.permission = 'view')
    
        LEFT JOIN xengallery_category AS category ON
            (category.category_id = media.category_id)
        LEFT JOIN xf_user AS user ON
            (user.user_id = media.user_id)
        LEFT JOIN xf_user_profile AS user_profile ON
            (user_profile.user_id = media.user_id)
        LEFT JOIN xf_attachment AS attachment ON
            (attachment.content_type = 'xengallery_media' AND attachment.attachment_id = media.attachment_id)
        LEFT JOIN xf_attachment_data AS data ON
            (data.data_id = attachment.data_id)
    WHERE (
        
            media.media_privacy = 'public'
            OR IF(media.category_id > 0, media.category_id IN (1), NULL)) AND (media.media_state IN ('visible')) AND (IF(media.album_id > 0, album.album_state = 'visible', 1=1))
    ORDER BY media.media_date DESC, media.media_id DESC       
LIMIT 100;

The query took 4.0247 seconds.

Then tried reducing the nunber of returned rows like you said:

SELECT SQL_NO_CACHE media.*
    ,
        album.*, albumviewperm.*,
        category.*,
        user.*, user_profile.*, IF(user.username IS NULL, media.username, user.username) AS username,
        attachment.attachment_id, attachment.data_id, attachment.attach_date,data.filename, data.file_size, data.file_hash, data.file_path, data.width, data.height, data.thumbnail_width, data.thumbnail_height
FROM xengallery_media AS media
 
        LEFT JOIN xengallery_album AS album ON
            (album.album_id = media.album_id)
        LEFT JOIN xengallery_album_permission as albumviewperm ON
            (album.album_id = albumviewperm.album_id AND albumviewperm.permission = 'view')
    
        LEFT JOIN xengallery_category AS category ON
            (category.category_id = media.category_id)
        LEFT JOIN xf_user AS user ON
            (user.user_id = media.user_id)
        LEFT JOIN xf_user_profile AS user_profile ON
            (user_profile.user_id = media.user_id)
        LEFT JOIN xf_attachment AS attachment ON
            (attachment.content_type = 'xengallery_media' AND attachment.attachment_id = media.attachment_id)
        LEFT JOIN xf_attachment_data AS data ON
            (data.data_id = attachment.data_id)
    WHERE (
        
            media.media_privacy = 'public'
            OR IF(media.category_id > 0, media.category_id IN (1), NULL)) AND (media.media_state IN ('visible')) AND (IF(media.album_id > 0, album.album_state = 'visible', 1=1))
    ORDER BY media.media_date DESC, media.media_id DESC       
LIMIT 1;

The query took 3.2593 seconds.

This concludes that the configuration of the number of search results is irrelevant.

I am quite sure that MySQL does a table scan when you do an IF() because it has to evaluate the expression for each row. It is better to not use IF() in the WHERE clause.

I took at crack at rewriting the WHERE clause:

SELECT SQL_NO_CACHE media.*
    ,
        album.*, albumviewperm.*,
        category.*,
        user.*, user_profile.*, IF(user.username IS NULL, media.username, user.username) AS username,
        attachment.attachment_id, attachment.data_id, attachment.attach_date,data.filename, data.file_size, data.file_hash, data.file_path, data.width, data.height, data.thumbnail_width, data.thumbnail_height
FROM xengallery_media AS media
 
        LEFT JOIN xengallery_album AS album ON
            (album.album_id = media.album_id)
        LEFT JOIN xengallery_album_permission as albumviewperm ON
            (album.album_id = albumviewperm.album_id AND albumviewperm.permission = 'view')
    
        LEFT JOIN xengallery_category AS category ON
            (category.category_id = media.category_id)
        LEFT JOIN xf_user AS user ON
            (user.user_id = media.user_id)
        LEFT JOIN xf_user_profile AS user_profile ON
            (user_profile.user_id = media.user_id)
        LEFT JOIN xf_attachment AS attachment ON
            (attachment.content_type = 'xengallery_media' AND attachment.attachment_id = media.attachment_id)
        LEFT JOIN xf_attachment_data AS data ON
            (data.data_id = attachment.data_id)
    WHERE
    (
        media.media_privacy = 'public'
        OR
        media.category_id IN (1)
    )
    AND
        media.media_state IN ('visible')
    AND
    (
        media.album_id = 0
        OR
        album.album_state = 'visible'
    )
    ORDER BY media.media_date DESC, media.media_id DESC       
LIMIT 1;

This query took 2.5076 seconds. Quite an improvement but still unacceptably slow.

Now I removed a lot of the joins. I know this will be useless in production, but I want to find where the slowness comes from.

SELECT SQL_NO_CACHE *
FROM xengallery_media AS media
    LEFT JOIN xengallery_album AS album ON (album.album_id = media.album_id)
    WHERE
    (
        media.media_privacy = 'public'
        OR
        media.category_id IN (1)
    )
    AND
        media.media_state IN ('visible')
    AND
    (
        media.album_id = 0
        OR
        album.album_state = 'visible'
    )
    ORDER BY media.media_date DESC, media.media_id DESC       
LIMIT 1;

Which took 0.0033 seconds to run. That is much better.

Okay, I will re-add one join:

SELECT SQL_NO_CACHE *
FROM xengallery_media AS media
    LEFT JOIN xengallery_album AS album ON (album.album_id = media.album_id)
    LEFT JOIN xengallery_category AS category ON (category.category_id = media.category_id)
    WHERE
    (
        media.media_privacy = 'public'
        OR
        media.category_id IN (1)
    )
    AND
        media.media_state IN ('visible')
    AND
    (
        media.album_id = 0
        OR
        album.album_state = 'visible'
    )
    ORDER BY media.media_date DESC, media.media_id DESC       
LIMIT 1;

That took 0.7860 seconds to run. That query took 238 times longer than the last. Interesting considering the xengallery_category table contains ONE row. Why does this happen?

Let's try using EXPLAIN:

EXPLAIN (QUERY WITH ONE JOIN);

| id | select_type | table | partitions | type   | possible_keys                              | key        | key_len | ref                    | rows | filtered | Extra       |
|  1 | SIMPLE      | media | NULL       | index  | album_id_media_date,category_id_media_date | media_date | 4       | NULL                   |    1 |     8.33 | Using where |
|  1 | SIMPLE      | album | NULL       | eq_ref | PRIMARY                                    | PRIMARY    | 4       | xenforo.media.album_id |    1 |   100.00 | Using where |

EXPLAIN (QUERY WITH TWO JOINS);
| id | select_type | table    | partitions | type   | possible_keys                              | key     | key_len | ref                    | rows  | filtered | Extra                                              |
|  1 | SIMPLE      | media    | NULL       | ALL    | album_id_media_date,category_id_media_date | NULL    | NULL    | NULL                   | 65085 |    33.33 | Using where; Using temporary; Using filesort       |
|  1 | SIMPLE      | album    | NULL       | eq_ref | PRIMARY                                    | PRIMARY | 4       | xenforo.media.album_id |     1 |   100.00 | Using where                                        |
|  1 | SIMPLE      | category | NULL       | ALL    | PRIMARY                                    | NULL    | NULL    | NULL                   |     1 |   100.00 | Using where; Using join buffer (Block Nested Loop) |

I am no MySQL expert but does the 'key' row mean that it is not using any key at all for the media table? As soon as one extra LEFT JOIN is added, even without adding conditions for it, it decides to do a table scan of the media table and then it gets slow.

EDIT:
I just came back from talking to a friend about this. Turns out that for all columns where you are joining, you have to put the column in an index.

 

[Chris D]

Can you run:

SHOW CREATE TABLE xengallery_media

 

[Rasmus Vind]

CREATE TABLE `xengallery_media` (
 `media_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `media_title` text NOT NULL,
 `media_description` text NOT NULL,
 `media_date` int(10) unsigned NOT NULL DEFAULT '0',
 `last_edit_date` int(10) unsigned NOT NULL DEFAULT '0',
 `last_comment_date` int(10) unsigned NOT NULL DEFAULT '0',
 `media_type` enum('image_upload','video_upload','video_embed') NOT NULL DEFAULT 'image_upload',
 `media_tag` text,
 `media_embed_url` text,
 `media_embed_cache` blob,
 `media_state` enum('visible','moderated','deleted') NOT NULL DEFAULT 'visible',
 `album_id` int(10) unsigned NOT NULL DEFAULT '0',
 `media_privacy` enum('private','public','shared','members','followed','category') NOT NULL DEFAULT 'public',
 `category_id` int(10) unsigned NOT NULL DEFAULT '0',
 `attachment_id` int(10) unsigned NOT NULL DEFAULT '0',
 `user_id` int(10) unsigned NOT NULL DEFAULT '0',
 `username` varchar(50) NOT NULL,
 `ip_id` int(10) unsigned NOT NULL DEFAULT '0',
 `likes` int(10) unsigned NOT NULL DEFAULT '0',
 `like_users` blob,
 `comment_count` int(10) unsigned NOT NULL DEFAULT '0',
 `media_view_count` int(10) unsigned NOT NULL DEFAULT '0',
 `rating_count` int(10) unsigned NOT NULL DEFAULT '0',
 `rating_sum` int(10) unsigned NOT NULL DEFAULT '0',
 `rating_avg` float unsigned NOT NULL DEFAULT '0',
 `rating_weighted` float unsigned NOT NULL DEFAULT '0',
 `watermark_id` int(10) unsigned NOT NULL DEFAULT '0',
 `custom_media_fields` mediumblob NOT NULL,
 `media_exif_data_cache` mediumblob NOT NULL,
 `media_exif_data_cache_full` mediumblob NOT NULL,
 `warning_id` int(10) unsigned NOT NULL DEFAULT '0',
 `warning_message` varchar(255) NOT NULL DEFAULT '',
 `position` int(10) unsigned NOT NULL DEFAULT '0',
 `imported` int(10) unsigned NOT NULL DEFAULT '0',
 `thumbnail_date` int(10) NOT NULL DEFAULT '0',
 `tags` mediumblob NOT NULL,
 PRIMARY KEY (`media_id`),
 KEY `position` (`position`),
 KEY `media_date` (`media_date`),
 KEY `user_id_media_date` (`user_id`,`media_date`),
 KEY `album_id_media_date` (`album_id`,`media_date`),
 KEY `category_id_media_date` (`category_id`,`media_date`)
) ENGINE=InnoDB AUTO_INCREMENT=113990 DEFAULT CHARSET=utf8

 

[Mike]

It looks like you have the necessary index. MySQL just isn't using it in this case (it's using a bad query plan).

We may be able to force the index -- or even just help it along by adding an additional cut off point like we do with threads -- so I'm going to move this to bugs.

 

[Chris D]

If you now run your 4 second query again but change:

FROM xengallery_media AS media

To:

FROM xengallery_media AS media FORCE INDEX media_date

That should test the current theory.

 

[Rasmus Vind]

I will try this.

Okay... I had to do FORCE INDEX (media_date). It wouldn't work without the parentheses. Anyway, that did it!

Query took 0.0034 seconds.)

 

[Chris D]

Ah yeah, sorry. Was typing it on my phone

Glad that works, we'll get that sorted for the next release.

If you want to implement the fix now, you can do the following:

In library/XenGallery/ControllerPublic/FindNew.php

Find:

'join' =>

(This appears twice, the one to change is on line 149)

And directly above add:

'forceMediaIndex' => 'media_date',

Which will now look like:

'forceMediaIndex' => 'media_date',
'join' =>

In library/XenGallery/Model/Media.php

Find:

$sqlClauses = $this->prepareMediaFetchOptions($fetchOptions, $conditions);

And directly below add:

$forceIndex = (!empty($fetchOptions['forceMediaIndex']) ? 'FORCE INDEX (' . $fetchOptions['forceMediaIndex'] . ')' : '');

A few lines down from that, you will see:

FROM xengallery_media AS media

(This appears around 10 times, only replace this one!)

Change that to:

FROM xengallery_media AS media ' . $forceIndex . '

Those changes are a bit fiddly, so make a backup of the original file first. If you get any problems, just restore the original file.

 

[Rasmus Vind]

Thanks so much for such thorough support and delving into debugging with me . I am happy we found a solution. I don't think I am that much in a hurry. I'd rather have the sanctioned PHP file instead of making my own changes as I am a XenForo purist .

 

[Chris D]

I'd rather have the sanctioned PHP file instead of making my own changes as I am a XenForo purist .

Those changes are the exact changes we've made in the next version, so they're there if you need them or want them. To a certain extent, applying those changes will help us verify the issue is fully resolved, but we're fairly confident of that anyway.

 

[Rasmus Vind]

Ah well. I have added the changes. Runs fine. Yay