Future fix Media Gallery Index causing slow query

[Kirby]

Affected version

2.1.2

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 (X, Y, Z)) OR (`xf_mg_media_item`.`category_id` = 0 AND `xf_mg_album_Album_1`.`view_privacy` = 'public'))
AND ((`xf_mg_media_item`.`media_state` IN ('visible')))

This query is slow as it can't use any existing index in xf_mg_media_item and thus causes a full table scan.

The following alternative yields the same results and seems a lot faster

SELECT 
(
    SELECT SUM(`media_count`)
    FROM `xf_mg_category`
    WHERE `category_id` IN (X, Y, Z)
)
+ 
(
    SELECT SUM(`media_count`)
    FROM `xf_mg_album`
    WHERE `album_state` = 'visible'
        AND `view_privacy` = 'public'
)

 

[Chris D]

This is definitely something we want to explore but not quite as easy as it seems on the surface.

The current total comes from the Finder which itself is built across multiple methods with different options and variables. The query as you have written it, doesn't take care of a bunch of scenarios including differences with guests/members, normal users/moderators, personal albums enabled/disabled, public/members only/private/shared privacy options and so on.

Not insurmountable but the ideal solution may be more flexible if it waits until 2.2.

For now, you can add a date limit to the media index which may help.

 

[Kirby]

The query as you have written it, doesn't take care of a bunch of scenarios including differences with guests/members, normal users/moderators, personal albums enabled/disabled, public/members only/private/shared privacy options and so on.

That's correct, it only targets one specific use case (as directed by the original query) - a user which can only access public albums and cannot view deleted/moderated media items.

But that's the majory of calls anyway so I went ahead and just use my version in this specific use case, in total this is still a lot faster than adding a cutoff and using the finder generated query in call cases.