What I found is that the this query:
Code:
SELECT * FROM (SELECT * FROM chv_images
LEFT JOIN chv_albums ON chv_images.image_album_id = chv_albums.album_id
WHERE chv_images.image_nsfw = 0 AND (chv_albums.album_privacy NOT IN ('private','private_but_link','password','custom') OR chv_albums.album_privacy IS NULL OR chv_albums.album_user_id IS NULL)
ORDER BY chv_images.image_id DESC
LIMIT 0,25) chv_images
LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id
LEFT JOIN chv_users ON chv_images.image_user_id = chv_users.user_id
LEFT JOIN chv_categories ON chv_images.image_category_id = chv_categories.category_id
In your server is not working as expected because in your server the engine is re-sorting the resulset, which is something that it shouldn't. Basically the sub-query select the matching images with order, then it adds (join) the other tables (storages, users, categories, etc.) but in your server it does the sub-query then re-sort the resultset (not always).
So at script side the patch is quite simple is just add this line to the bottom of the query:
Code:
ORDER BY chv_images.image_id DESC
I've added a patch that will work for now in your server (PHP) but is not an official patch because I'm still investigating this.
I think that the trouble could be in your optimizer_switch which has this value:
Code:
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
As a reference, this is my optimizer_switch for the demo:
Code:
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on
And this one is for my dev server:
Code:
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
You should try changing your optimizer_switch and debug the thing to see if you get a re-ordered resulset via PHPMyAdmin or any DB script/software you use. There could be other variables causing this, not sure about it to be honest. I searched around, I asked around, no one knows this bug or whatever it is.
I'm pretty sure that the problem is your MySQL configuration, I dumped your database in my local server and the query worked fine so is not DB structure, indexes, etc.
The MySQL user that I used (taken from settings.php) didn't have SUPER privileges so I wasn't able to test those variables, I assume that everybody uses *standard* setups and I can't spend all day debugging the 500 variables of a custom MySQL server but all those with "sort" must be related to this.
I noticed that you are on a VPS so it won't be that hard to change those values. Let me know if you do it or if you grant me SUPER. I need to fix this and the patch only applies to your installation, I'm not adding that patch to the official releases yet.
