Thanks for confirming - we have a custom helper addon to extend the gallery a bit so I extended the finder to unset that hint a bit more cleanly. It had been a bit of a pain point for a while as viewing albums had always been slow, and it seems this has fixed it right up.
If it helps, I've seen the same behaviour with hints making some queries better and some worse with another feature we built. We have an attachment gallery addon that pulls out attachments from within a forum or thread to display them in a grid, and that came with a whole host of challenges, but I encountered a similar issue with index hints there.
I discovered I needed an index hint for it to be usable, and when querying attachments from forums with 5 or 6 figure attachment counts (the board is very attachment-heavy), the query was running in < 0.1s whichever page I was on. In forums that had very few attachments however, the query ran so utterly abysmally that it took the entire database server down. The only difference between each query was which node ID was being queried. If I removed the index hint, the forum with not many attachments ran fine and the forum with lots of attachments ran just as badly as the other one did with the index hint, it just swapped the problem over. So I didn't know how to possibly address that.
I'd already scrapped even trying to do pagination as the count query was so slow that I just capped it to 100 pages, but what I ended up doing to fix it was taking some conditions out of the WHERE clause and applying them to a join. As soon as I did that the query ran fine regardless of which forum I was in. It still needs the index hint, but it's at least consistently performant regardless of the dataset it's looking at.
To achieve this I also had to mirror the node_id value to xf_attachment_data. Whereas I used to join in everything I needed and then apply conditions in the WHERE clause like WHERE content_type = 'post' and content_id = post_id and node_id = X, this is now all in the join. The start of the query I run is essentially something like select * from xf_attachment join xf_attachment_data on xf_attachment_data.data_id = xf_attachment.data_id join xf_post on (content_type = 'post' and content_id = post_id and node_id = X) - so a bit of extra logic is needed to mirror the value and create this join, but the upshot is that as soon as I moved the node_id condition to the join the issue was resolved immediately, and perhaps a similar approach can be used here and maybe elsewhere too.
Essentially it seems like it's slow filtering the results in the WHERE clause, and I could physically see that happening in my database GUI - the forum with lots of attachments would return results immediately, but with the forum with not many attachments it was returning a row once every few seconds, so it looked like it was running through a huge number of rows and returning the few that matched as it found them, and had to go through a lot of data to find enough to return to reach the limit for that page. Which makes sense, if it's going through 10,000 attachments and only 50 of them are in that forum, it has to go through a lot to find enough for a page. For some reason the where condition for the node_id was just insanely inefficient, which I don't have the knowledge to understand as it's an indexed primary key. From what I can tell, it's a similar issue with the gallery where it's slow filtering by the album_id, and if that condition was moved to the join it might be a lot faster.
The only problem is that adding additional conditions to joins isn't supported by core XF (when using the Finder vs a raw query at least), I had to use Xon's StandardLib addon which allows for it. I think there could be benefits to XF's finder allowing other conditions (similar to Laravel's with function that accepts a callback to apply other conditions), and I wouldn't be surprised if there's other areas that could benefit from conditions being applied in the join itself, as it can massively reduce the number of rows being returned and filtered.
