The slowest query on my site

[Rasmus Vind]

I just looked in my slow_query_log and found this repeated in the 1000s.

# Time: 2018-04-05T18:55:39.819781Z
# User@Host: root[root] @  [192.168.208.179]  Id: 1028754
# Query_time: 3.948271  Lock_time: 0.000123 Rows_sent: 2000  Rows_examined: 565049
SET timestamp=1522954539;
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, d
ata.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 = 'vi
sible', 1=1))
                                ORDER BY media.media_date DESC, media.media_id DESC
                         LIMIT 2000;

This is the explain:

+----+-------------+---------------+------------+--------+------------------------------+---------+---------+------------------------------+-------+----------+----------------------------------------------------+
| id | select_type | table         | partitions | type   | possible_keys                | key     | key_len | ref                          | rows  | filtered | Extra                                              |
+----+-------------+---------------+------------+--------+------------------------------+---------+---------+------------------------------+-------+----------+----------------------------------------------------+
|  1 | SIMPLE      | media         | NULL       | ALL    | NULL                         | NULL    | NULL    | NULL                         | 77432 |    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      | albumviewperm | NULL       | eq_ref | PRIMARY                      | PRIMARY | 5       | xenforo.album.album_id,const |     1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | category      | NULL       | ALL    | PRIMARY                      | NULL    | NULL    | NULL                         |     1 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | user          | NULL       | eq_ref | PRIMARY                      | PRIMARY | 4       | xenforo.media.user_id        |     1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | user_profile  | NULL       | eq_ref | PRIMARY                      | PRIMARY | 4       | xenforo.media.user_id        |     1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | attachment    | NULL       | eq_ref | PRIMARY,content_type_id_date | PRIMARY | 4       | xenforo.media.attachment_id  |     1 |   100.00 | Using where                                        |
|  1 | SIMPLE      | data          | NULL       | eq_ref | PRIMARY                      | PRIMARY | 4       | xenforo.attachment.data_id   |     1 |   100.00 | NULL                                               |
+----+-------------+---------------+------------+--------+------------------------------+---------+---------+------------------------------+-------+----------+----------------------------------------------------+
8 rows in set, 1 warning (0.01 sec)

I am guessing that we are missing some index.

I am willing to run a query or two on your recommendation.

 

[Chris D]

I'm not actually sure we'd classify this as a bug, exactly, as it seems to me that the root of the problem would actually be the number of records being loaded (2000).

Do you have any idea where you've set a limit of 2000? (It's not a value we would have set in the core).

Your site seems very slow, generally, so I'm also not sure there isn't any bottlenecks elsewhere. I note you also have "Read Marking Data Lifetime (Days)" set to a much higher value than the default (which is 30 days). That can have a big impact on performance too.

 

[Rasmus Vind]

That variable is from 'Maximum Number of Search Results' in 'Options' -> 'Search Options'.

The site is only slow on the gallery page in my experience. I feel that it is a little beside the point. I feel that this 'Using where; Using temporary; Using filesort' is a sin. A database should never have to resort to these measures. It scans ALL entries in the database. That is not good.

 

[Xon]

Using an if() is probably preventing indexes being used.

Can you try this query/explain ?

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 media.category_id IN (1))
       AND (media.media_state IN ('visible')) AND (media.album_id != 0 AND album.album_state = 'visible' OR media.album_id = 0)
ORDER BY media.media_date DESC, media.media_id DESC
LIMIT 2000;

(not 100% sure I expanded out those if() statements right, but it logically makes sense.

I'm not actually sure we'd classify this as a bug, exactly, as it seems to me that the root of the problem would actually be the number of records being loaded (2000).

The 1st row of the explain has it pulling 77k rows from xengallery_media with only ~33% filtering is definitely hurting.

 

[Rasmus Vind]

That still gives me this

+----+-------------+---------------+------------+--------+--------------------------------------------+---------+---------+------------------------------+-------+----------+----------------------------------------------------+

| 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                         | 77433 |    30.00 | 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      | albumviewperm | NULL       | eq_ref | PRIMARY                                    | PRIMARY | 5       | xenforo.album.album_id,const |     1 |   100.00 | NULL                                               |

|  1 | SIMPLE      | category      | NULL       | ALL    | PRIMARY                                    | NULL    | NULL    | NULL                         |     1 |   100.00 | Using where; Using join buffer (Block Nested Loop) |

|  1 | SIMPLE      | user          | NULL       | eq_ref | PRIMARY                                    | PRIMARY | 4       | xenforo.media.user_id        |     1 |   100.00 | NULL                                               |

|  1 | SIMPLE      | user_profile  | NULL       | eq_ref | PRIMARY                                    | PRIMARY | 4       | xenforo.media.user_id        |     1 |   100.00 | NULL                                               |

|  1 | SIMPLE      | attachment    | NULL       | eq_ref | PRIMARY,content_type_id_date               | PRIMARY | 4       | xenforo.media.attachment_id  |     1 |   100.00 | Using where                                        |

|  1 | SIMPLE      | data          | NULL       | eq_ref | PRIMARY                                    | PRIMARY | 4       | xenforo.attachment.data_id   |     1 |   100.00 | NULL                                               |

+----+-------------+---------------+------------+--------+--------------------------------------------+---------+---------+------------------------------+-------+----------+----------------------------------------------------+

8 rows in set, 1 warning (0.00 sec)

 

[Xon]

Yuck, it is doing a full table scan of the media table, that is why the query is so damn slow. Not sure how that will be fixed

 

[Rasmus Vind]

Yeah that's kind of why I felt it was a bug. In many situations a query doing a table scan is considered a bug or suboptimal at the very least. I have reported a similar thing in XF once and they fixed that by forcing a specific index on the query.

 

[Rasmus Vind]

Being woken up early today because site was appearing down. What was MySQL doing?

root@localhost:~/Hive# docker-compose exec mysql mysql -uroot -p xenforo -e 'show processlist'
+---------+------+-----------------------+---------+---------+------+---------------------+------------------------------------------------------------------------------------------------------+
| Id      | User | Host                  | db      | Command | Time | State               | Info                                                                                                 |
+---------+------+-----------------------+---------+---------+------+---------------------+------------------------------------------------------------------------------------------------------+
| 2747341 | root | 192.168.208.179:51040 | xenforo | Execute |  853 | Creating sort index | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747364 | root | 192.168.208.179:51108 | xenforo | Execute |  850 | Creating sort index | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747378 | root | 192.168.208.179:51176 | xenforo | Execute |  845 | Creating sort index | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747391 | root | 192.168.208.179:51208 | xenforo | Execute |  843 | Creating sort index | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747393 | root | 192.168.208.179:51212 | xenforo | Execute |  843 | Creating sort index | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747396 | root | 192.168.208.179:51218 | xenforo | Execute |  842 | Creating sort index | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747402 | root | 192.168.208.179:51230 | xenforo | Execute |  840 | Creating sort index | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747412 | root | 192.168.208.179:51284 | xenforo | Execute |  838 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747420 | root | 192.168.208.179:51336 | xenforo | Execute |  835 | Creating sort index | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747426 | root | 192.168.208.179:51384 | xenforo | Execute |  831 | Creating sort index | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747435 | root | 192.168.208.179:51474 | xenforo | Execute |  825 | Creating sort index | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747441 | root | 192.168.208.179:51510 | xenforo | Execute |  823 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747448 | root | 192.168.208.179:51530 | xenforo | Execute |  821 | Creating sort index | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747451 | root | 192.168.208.179:51560 | xenforo | Execute |  820 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747477 | root | 192.168.208.179:51678 | xenforo | Execute |  817 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747480 | root | 192.168.208.179:51682 | xenforo | Execute |  816 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747494 | root | 192.168.208.179:51730 | xenforo | Execute |  813 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747499 | root | 192.168.208.179:51740 | xenforo | Execute |  812 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747514 | root | 192.168.208.179:51784 | xenforo | Execute |  811 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747598 | root | 192.168.208.179:51996 | xenforo | Execute |  803 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747604 | root | 192.168.208.179:52014 | xenforo | Execute |  802 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747620 | root | 192.168.208.179:52070 | xenforo | Execute |  801 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747636 | root | 192.168.208.179:52138 | xenforo | Execute |  798 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747646 | root | 192.168.208.179:52164 | xenforo | Execute |  797 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747651 | root | 192.168.208.179:52174 | xenforo | Execute |  795 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747660 | root | 192.168.208.179:52192 | xenforo | Execute |  792 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747662 | root | 192.168.208.179:52196 | xenforo | Execute |  792 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747673 | root | 192.168.208.179:52226 | xenforo | Execute |  788 | Creating sort index | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747675 | root | 192.168.208.179:52230 | xenforo | Execute |  787 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747677 | root | 192.168.208.179:52234 | xenforo | Execute |  787 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747680 | root | 192.168.208.179:52240 | xenforo | Execute |  785 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747683 | root | 192.168.208.179:52246 | xenforo | Execute |  785 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747692 | root | 192.168.208.179:52264 | xenforo | Execute |  781 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747713 | root | 192.168.208.179:52308 | xenforo | Execute |  777 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747753 | root | 192.168.208.179:52388 | xenforo | Execute |  772 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747764 | root | 192.168.208.179:52410 | xenforo | Execute |  768 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747780 | root | 192.168.208.179:52442 | xenforo | Execute |  766 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747841 | root | 192.168.208.179:52566 | xenforo | Execute |  759 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747844 | root | 192.168.208.179:52572 | xenforo | Execute |  758 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747849 | root | 192.168.208.179:52582 | xenforo | Execute |  756 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747946 | root | 192.168.208.179:52796 | xenforo | Execute |  736 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747954 | root | 192.168.208.179:52812 | xenforo | Execute |  734 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747967 | root | 192.168.208.179:52850 | xenforo | Execute |  722 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2747973 | root | 192.168.208.179:52868 | xenforo | Execute |  719 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2748000 | root | 192.168.208.179:53068 | xenforo | Execute |  681 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2748051 | root | 192.168.208.179:53266 | xenforo | Execute |  658 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2748054 | root | 192.168.208.179:53272 | xenforo | Execute |  658 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2748056 | root | 192.168.208.179:53282 | xenforo | Execute |  658 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2748068 | root | 192.168.208.179:53306 | xenforo | Execute |  654 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2748075 | root | 192.168.208.179:53380 | xenforo | Execute |  652 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2748080 | root | 192.168.208.179:53426 | xenforo | Execute |  650 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2748081 | root | 192.168.208.179:53440 | xenforo | Execute |  627 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2748090 | root | 192.168.208.179:53482 | xenforo | Execute |  598 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2748109 | root | 192.168.208.179:53616 | xenforo | Execute |  586 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2748114 | root | 192.168.208.179:53722 | xenforo | Execute |  572 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2748135 | root | 192.168.208.179:53814 | xenforo | Execute |  551 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2748146 | root | 192.168.208.179:53880 | xenforo | Execute |  546 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2748151 | root | 192.168.208.179:53932 | xenforo | Execute |  517 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2748182 | root | 192.168.208.179:54146 | xenforo | Execute |  493 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2748188 | root | 192.168.208.179:54224 | xenforo | Execute |  482 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 2748193 | root | localhost             | xenforo | Query   |    0 | starting            | show processlist                                                                                     |
+---------+------+-----------------------+---------+---------+------+---------------------+------------------------------------------------------------------------------------------------------+
root@localhost:~/Hive#

 

[Rasmus Vind]

Site was down the past 5 hours. Why? High activity in the media gallery causing the disk to thrash because of bad indexing.

+----------+------+-----------------------+---------+---------+------+---------------------+------------------------------------------------------------------------------------------------------+
| Id       | User | Host                  | db      | Command | Time | State               | Info                                                                                                 |
+----------+------+-----------------------+---------+---------+------+---------------------+------------------------------------------------------------------------------------------------------+
| 10083905 | root | 192.168.208.179:38688 | xenforo | Execute |  766 | Creating sort index | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10083918 | root | 192.168.208.179:39038 | xenforo | Execute |  743 | Creating sort index | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10083927 | root | 192.168.208.179:39076 | xenforo | Execute |  740 | Creating sort index | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10083938 | root | 192.168.208.179:39662 | xenforo | Execute |  695 | Creating sort index | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10083949 | root | 192.168.208.179:39790 | xenforo | Execute |  687 | Creating sort index | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10083961 | root | 192.168.208.179:41246 | xenforo | Execute |  615 | Creating sort index | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10083975 | root | 192.168.208.179:41466 | xenforo | Execute |  602 | Creating sort index | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10083983 | root | 192.168.208.179:41730 | xenforo | Execute |  586 | Creating sort index | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10083997 | root | 192.168.208.179:41832 | xenforo | Execute |  581 | Creating sort index | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084000 | root | 192.168.208.179:41862 | xenforo | Execute |  579 | Creating sort index | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084011 | root | 192.168.208.179:42148 | xenforo | Execute |  547 | Creating sort index | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084036 | root | 192.168.208.179:42314 | xenforo | Execute |  533 | Creating sort index | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084040 | root | 192.168.208.179:42408 | xenforo | Execute |  526 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084043 | root | 192.168.208.179:42498 | xenforo | Execute |  520 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084052 | root | 192.168.208.179:42734 | xenforo | Execute |  500 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084062 | root | 192.168.208.179:42904 | xenforo | Execute |  481 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084069 | root | 192.168.208.179:43032 | xenforo | Execute |  473 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084071 | root | 192.168.208.179:43126 | xenforo | Execute |  469 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084073 | root | 192.168.208.179:43536 | xenforo | Execute |  453 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084077 | root | 192.168.208.179:43614 | xenforo | Execute |  445 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084083 | root | 192.168.208.179:43716 | xenforo | Execute |  439 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084097 | root | 192.168.208.179:44002 | xenforo | Execute |  430 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084107 | root | 192.168.208.179:44488 | xenforo | Execute |  396 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084114 | root | 192.168.208.179:44854 | xenforo | Execute |  376 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084118 | root | 192.168.208.179:44928 | xenforo | Execute |  374 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084122 | root | 192.168.208.179:45026 | xenforo | Execute |  369 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084124 | root | 192.168.208.179:45074 | xenforo | Execute |  367 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084132 | root | 192.168.208.179:45428 | xenforo | Execute |  354 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084139 | root | 192.168.208.179:45642 | xenforo | Execute |  340 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084154 | root | 192.168.208.179:45778 | xenforo | Execute |  332 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084165 | root | 192.168.208.179:45880 | xenforo | Execute |  328 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084166 | root | 192.168.208.179:46070 | xenforo | Execute |  321 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084185 | root | 192.168.208.179:46788 | xenforo | Execute |  295 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084187 | root | 192.168.208.179:46806 | xenforo | Execute |  294 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084190 | root | 192.168.208.179:46812 | xenforo | Execute |  293 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084197 | root | 192.168.208.179:46924 | xenforo | Execute |  290 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084198 | root | 192.168.208.179:47170 | xenforo | Execute |  273 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084227 | root | 192.168.208.179:48336 | xenforo | Execute |  218 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084253 | root | 192.168.208.179:48702 | xenforo | Execute |  191 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084270 | root | 192.168.208.179:48766 | xenforo | Execute |  187 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084289 | root | 192.168.208.179:48900 | xenforo | Execute |  175 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084306 | root | 192.168.208.179:49046 | xenforo | Execute |  163 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084327 | root | 192.168.208.179:49244 | xenforo | Execute |  152 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084377 | root | 192.168.208.179:49616 | xenforo | Execute |  122 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084379 | root | 192.168.208.179:49674 | xenforo | Execute |  117 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084380 | root | 192.168.208.179:49720 | xenforo | Execute |  114 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084381 | root | 192.168.208.179:49828 | xenforo | Execute |  110 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084390 | root | 192.168.208.179:50048 | xenforo | Execute |   99 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084393 | root | 192.168.208.179:50126 | xenforo | Execute |   96 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084412 | root | 192.168.208.179:50644 | xenforo | Execute |   75 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084413 | root | 192.168.208.179:50652 | xenforo | Execute |   74 | Sending data        | SELECT thread.*, bundle.*,
                    user.*, IF(user.username IS NULL, thread.username, user.username) AS |
| 10084415 | root | 192.168.208.179:50940 | xenforo | Execute |   59 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084418 | root | 192.168.208.179:50946 | xenforo | Execute |   58 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084422 | root | 192.168.208.179:50978 | xenforo | Execute |   57 | Sending data        | SELECT thread.*, bundle.*,
                    user.*, IF(user.username IS NULL, thread.username, user.username) AS |
| 10084438 | root | 192.168.208.179:51214 | xenforo | Execute |   51 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084460 | root | 192.168.208.179:51428 | xenforo | Execute |   43 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084465 | root | 192.168.208.179:51450 | xenforo | Execute |   42 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084467 | root | 192.168.208.179:51466 | xenforo | Execute |   42 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084468 | root | 192.168.208.179:51474 | xenforo | Execute |   42 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084471 | root | 192.168.208.179:52264 | xenforo | Execute |    4 | Sending data        | SELECT media.*
                ,
                    album.*, albumviewperm.*,
                    category.*,
                    user.*, user_profile.*, IF |
| 10084472 | root | localhost             | NULL    | Query   |    0 | starting            | show processlist                                                                                     |
+----------+------+-----------------------+---------+---------+------+---------------------+------------------------------------------------------------------------------------------------------+

I am sorry for being so negative. I love what you guys are doing, I just find myself in a pinch because of time constraints to debug and fix this myself and it puts a huge amount of stress on my shoulders when the site is down.

EDIT: (an hour later)
That's it. I am disabling this addon until it stops being inefficient.

 

[Chris D]

And, yet, no one else is experiencing similar issues. I already advised the probable reason for that in my earlier post.

The query performance is absolutely fine when dealing with smaller limits, as it was designed to do. When you change a value from 200 to 2000 and give it potentially 10 times more work to do (or at least significantly more to select that number of results) performance is going to suffer somewhere. It may be less so in other areas of the software, but they aren’t having to handle fairly complex joins to multiple tables with many different conditions to ensure permissions and privacy are respected.

I’ll see if I can help, but the extent of it is likely reducing that limit specifically for the gallery through a code edit.

 

[Rasmus Vind]

You might be right. Or I just happen to have the largest gallery so far combined with my 'Maximum Number of Search Results' at 2000.

The query performance is absolutely fine when dealing with smaller limits, as it was designed to do. When you change a value from 200 to 2000 and give it potentially 10 times more work to do (or at least significantly more to select that number of results) performance is going to suffer somewhere. It may be less so in other areas of the software, but they aren’t having to handle fairly complex joins to multiple tables with many different conditions to ensure permissions and privacy are respected.

About this. I disagree. It was designed to work with whatever the user entered for 'Maximum Number of Search Results'. If it was designed to run with fewer, you would have inserted a ceiling or just not depend on this variable at all.
Anyway, discussing this this does not help either of us. So I will let it rest.

I am however analysing the query. I have read a little bit about using IF() in WHERE clauses and I think that MySQL is more or less forced to do a table scan because it cannot predict the IF properly. I am trying to rewrite that to an OR instead. I am also trying out an index on:

ALTER TABLE `xengallery_media` ADD INDEX `ralle_test` (`media_privacy`, `category_id`, `media_state`, `album_id`, `media_date`, `media_id`)

But with no luck so far.

Also, if you are comfortable with it, I can provide you my xengallery tables so you can experiment with this yourself. However, I think no matter the size, you can just look at the 'EXPLAIN' to see if it still does the same thing.

 

[RobParker]

About this. I disagree. It was designed to work with whatever the user entered for 'Maximum Number of Search Results'. If it was designed to run with fewer, you would have inserted a ceiling or just not depend on this variable at all.

Surely it's resource dependent. The default settings are set to work on the majority of hardware. If you had a server with a ridiculous amount of RAM, a hard ceiling would be stupid. All types of server optimisation are the same.

If I understand correctly, you're returning 2000 search results. Why? No one searching for that is going to look through 2000 results. If they can't see what they want in the first few pages of results, they'll change their search.

 

[Digital Doctor]

change a value from 200 to 2000 and give it potentially 10 times more work

Did you do this ?
What location ?

 

[Rasmus Vind]

it’s set to 2000 because of the way the ElasticSearch integration works. I have a few MySQL constraints that will further reduce the results returned by ES. I set this to change the XF search results. It seems weird that the gallery must use the same variable. I’m not searching for album pictures when browsing.

 

[Rasmus Vind]

I have been experimenting some more.

I tried removing all LEFT JOINs that didn't affect the end media entries. That was this query:

explain SELECT media.*, album.*
                        FROM xengallery_media AS media

                                        LEFT JOIN xengallery_album AS album ON
                                                (album.album_id = media.album_id)

                                WHERE
                                (
                                        media.media_privacy = 'public'
                                    OR
                                        # IF(media.category_id > 0, media.category_id IN (1), NULL)
                                        media.category_id = 0 OR media.category_id IN (1)
                                )
                                AND
                                (
                                    media.media_state IN ('visible')
                                )
                                AND
                                (
                                    # IF(media.album_id > 0, album.album_state = 'visible', 1=1)
                                    media.album_id = 0 OR album.album_state = 'visible'
                                )
                                ORDER BY media.media_date DESC, media.media_id DESC
                         LIMIT 2000;

It worked. The query is super snappy.

+----+-------------+-------+------------+--------+-------------------------------------------------------+------------+---------+------------------------+------+----------+-------------+
| 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,ralle_test | media_date | 4       | NULL                   | 2000 |    10.83 | Using where |
|  1 | SIMPLE      | album | NULL       | eq_ref | PRIMARY                                               | PRIMARY    | 4       | xenforo.media.album_id |    1 |   100.00 | Using where |
+----+-------------+-------+------------+--------+-------------------------------------------------------+------------+---------+------------------------+------+----------+-------------+

But not useful. I still kind of wanted to know what was causing it so I tried adding LEFT JOINS one by one and added ALL the LEFT JOINs. Turns out the selected columns causes this, not the joins. It might be that it just ignores the LEFT JOINs if we don't select their columns though. As soon as we SELECT their columns, it starts getting slow again.

I tried a two-query solution. First we do this:

SELECT media.media_id
                                        FROM xengallery_media AS media

                                                        LEFT JOIN xengallery_album AS album ON
                                                                (album.album_id = media.album_id)

                                                WHERE
                                                (
                                                        media.media_privacy = 'public'
                                                    OR
                                                        # IF(media.category_id > 0, media.category_id IN (1), NULL)
                                                        media.category_id = 0 OR media.category_id IN (1)
                                                )
                                                AND
                                                (
                                                    media.media_state IN ('visible')
                                                )
                                                AND
                                                (
                                                    # IF(media.album_id > 0, album.album_state = 'visible', 1=1)
                                                    media.album_id = 0 OR album.album_state = 'visible'
                                                )
                                                ORDER BY media.media_date DESC, media.media_id DESC
                                                LIMIT 2000

Then we do this:

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_id IN ($INSERT_IDS_HERE)
ORDER BY media.media_date DESC, media.media_id DESC

And I get this again:

+----+-------------+---------------+------------+--------+------------------------------+---------+---------+------------------------------+------+----------+----------------------------------------------------+
| id | select_type | table         | partitions | type   | possible_keys                | key     | key_len | ref                          | rows | filtered | Extra                                              |
+----+-------------+---------------+------------+--------+------------------------------+---------+---------+------------------------------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | media         | NULL       | range  | PRIMARY                      | PRIMARY | 4       | NULL                         | 2000 |   100.00 | Using where; Using temporary; Using filesort       |
|  1 | SIMPLE      | album         | NULL       | eq_ref | PRIMARY                      | PRIMARY | 4       | xenforo.media.album_id       |    1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | albumviewperm | NULL       | eq_ref | PRIMARY                      | PRIMARY | 5       | xenforo.album.album_id,const |    1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | category      | NULL       | ALL    | PRIMARY                      | NULL    | NULL    | NULL                         |    1 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | user          | NULL       | eq_ref | PRIMARY                      | PRIMARY | 4       | xenforo.media.user_id        |    1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | user_profile  | NULL       | eq_ref | PRIMARY                      | PRIMARY | 4       | xenforo.media.user_id        |    1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | attachment    | NULL       | eq_ref | PRIMARY,content_type_id_date | PRIMARY | 4       | xenforo.media.attachment_id  |    1 |   100.00 | Using where                                        |
|  1 | SIMPLE      | data          | NULL       | eq_ref | PRIMARY                      | PRIMARY | 4       | xenforo.attachment.data_id   |    1 |   100.00 | NULL                                               |
+----+-------------+---------------+------------+--------+------------------------------+---------+---------+------------------------------+------+----------+----------------------------------------------------+

The LEFT JOIN is the problem. No matter the query conditions. The LEFT JOIN causes all the issues we are having. It can be fixed using INNER JOIN but those don't have the same programmatic result.

This is the work I have done so far. I guess what can be done is to split the query into multiple ones. It will be faster if it can be done without using FileSort and temporary. First you fetch the media and its album, maybe INNER JOIN the attachment and data because if those don't exist, you can't show the image anyway. Then we fetch the album INNER JOINED with the albumviewperm where albumid IN (the list of albumids from the first query), etc, etc. This is not a simple query change.

Another option is to add a new variable for the media gallery alone that does not depend on the search results variable. It seems weird that this variable should have so much power and that users wanting one thing will be forced to have another.