Fixed Media listing pages load extremely slow

[Jake B.]

I'm not sure if some cache didn't rebuild correctly, or what exactly is going on, but this query is running for about 5-7 seconds every time one of the pages are loaded:

SELECT COUNT(*)
FROM `xf_mg_media_item`
LEFT JOIN `xf_mg_album` AS `xf_mg_album_Album_1` ON (`xf_mg_album_Album_1`.`album_id` = `xf_mg_media_item`.`album_id`)
WHERE (IF(`xf_mg_media_item`.`album_id` > 0, `xf_mg_album_Album_1`.`album_state` = 'visible', 1=1)) AND ((`xf_mg_media_item`.`category_id` IN (500, 504, 502, 1420, 935, 513, 1418, 937, 1173, 511, 978, 939, 653, 674, 831, 705, 654, 656, 657, 670, 681, 688, 1393, 1422, 569, 839, 829, 1405, 1407, 1406, 1408, 1411, 840, 522, 534, 528, 1409, 1410, 577, 759, 1412, 1413, 1398, 938, 639, 1204, 1423, 1424)) OR (`xf_mg_media_item`.`category_id` = 0)) AND ((`xf_mg_media_item`.`media_state` IN ('visible', 'deleted')))

 

[Jake B.]

Just wanted to bump this as it's a fairly large issue, from what I can tell it's coming from running XF\Mvc\Entity\Finder->total() on well over a million rows

 

[Chris D]

Wrong forum again

We’ll move this to bugs though. We can take a look at improvements but I don’t have any specific recommendations at the moment.

Were they previously running MG1?

 

[Jake B.]

Ah, didn't assume it was a bug figured I just missed a rebuild somewhere

Well, they were briefly on XFMG 1 before being immediately upgraded to XenForo 2 after the import completed, but for your purposes the answer is probably yes

 

[Chris D]

I was mostly commenting in it being in Troubleshooting rather than MG support hence why I missed it.

Also I was mostly trying to ascertain if the issue existed in MG1 so I guess it’s not possible to ascertain that if they upgraded quickly.

We do have sites with millions of records though.

Actually it’s just dawned on me what the issue probably is. This issue does exist in MG1. It’s related to the amount of data being returned. The issue actually exists in forums too though it’s worse with the media home because generally that’s all records from all forums but also the queries are a bit more complex because of all the privacy stuff.

Anyway there’s a solution for this (similar solution exists for forums too) and that’s the “Only show items on media home from the last X days”.

Setting that to 365 is usually reasonable. It doesn’t affect the media list of any album or category, just the media home.

Try that and see how you get on.

 

[Jake B.]

Also I was mostly trying to ascertain if the issue existed in MG1 so I guess it’s not possible to ascertain that if they upgraded quickly.

Yeah, wasn't sure if you were thinking maybe it had something to do with the upgrade process

I've set that setting and it helped immensely on the homepage, still very slow on a couple of category views though

 

[Chris D]

That's working as expected then for the home page. Are some of the categories fairly large? I guess we could add a similar option there but obviously we don't really want to start making the oldest stuff totally undiscoverable.

Are you still looking at about 5-7 seconds for the category view?

 

[Jake B.]

Yeah, most of the images on the site are in the imported "Member Galleries" from PhotoPost. I'm going to be making a script to convert these into new albums for each user that has posted there soon though. I'd guess about 80-90% of all images on the site are in that one category so it's still a 5-7 second load time

 

[Chris D]

Ok, that might be the primary reason then.

Obviously we'll have a PhotoPost import at some point and they can be imported to albums within a member galleries category, or similar.

 

[Chris D]

For the next release, we've extended the media list limit to apply to the category view as well. There's also a clearer indication as to when the list is limited, and a link is displayed which will unfilter the list too and show older items (if you get to the last page).

 

[nocte]

Question regarding this: If a gallery index on XF1 had to be limited to X days, will this be on XF2 the same (regarding performance)? Or did you improve performance?

 

[Chris D]

Likely the same.