• Welcome to the Chevereto User Community!

    Here, users from all over the world come together to learn, share, and collaborate on everything related to Chevereto. It's a place to exchange ideas, ask questions, and help improve the software.

    Please keep in mind:

    • This community is user-driven. Always be polite and respectful to others.
    • Support development by purchasing a Chevereto license, which also gives you priority support.
    • Go further by joining the Community Subscription for even faster response times and to help sustain this space
  • Chevereto Support CLST

    Support response

    Support checklist

    • Got a Something went wrong message? Read this guide and provide the actual error. Do not skip this.
    • Confirm that the server meets the System Requirements
    • Check for any available Hotfix - your issue could be already reported/fixed
    • Read documentation - It will be required to Debug and understand Errors for a faster support response

If this user has too many pictures, clicking on the album will cause the database to be very slow.

T

twm

Chevereto Member
If this user has too many pictures (1M+),
clicking on the album will cause the database to be very slow.

3.15.2 is normal. This bug only appeared after upgrading to 3.16.2 two days ago





(SELECT * FROM chv_images LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=46541 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=46399 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=46386 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=46374 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=46220 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=46156 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=45818 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=45796 ORDER BY chv_images.image_id ASC LIMIT 0,5);
Click to expand...
 
Last edited:
1. What is 1.5.2v?
2. Where it happens? Like the URL, so I can tell where this happens.

Be clear as possible.
 
This is an ongoing issue, and I'm afraid that is not version related. If you think that is version related then downgrade to see how it goes. You could also compare the source code from both editions trying to spot a difference in the way the listing queries work. Most likely you need to create custom indexes to speed up queries, perhaps you require to fine tune the MySQL server. In any case, it is normal that having so much data build with relationships that at some point dealing with that data will be expensive.

I got your personal message, going to check the outcome of the EXPLAIN command to try to figure out what could be the issue. You could also provide the slowlog, it will help me a lot to narrow the issue.
 
Sorry, I don’t know what MySQL data is useful for you, I sent you MySQL slow query log.
Thank you.
 
For your use case the problem can be solved by creating a combined index for speeding the slow query:

SQL: 
ALTER TABLE `imgsha_data`.`chv_images` ADD INDEX `image_album_id_image_storage_id_image_is_approved` (`image_album_id`, `image_storage_id`, `image_is_approved`);

Then, doing INDEX type-hint the query now uses the index instead of traverse the entire dataset:

SQL: 
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49469 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49468 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49467 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49450 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49449 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49448 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49447 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49446 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49409 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49387 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49219 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49160 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49100 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49084 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49052 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48938 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48909 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48802 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48717 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48672 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48626 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48604 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48549 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48504 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48454 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48422 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48241 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48151 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48105 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48083 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48038 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48031 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47880 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47857 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47784 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47776 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47772 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47767 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47711 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47664 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47628 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47567 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47539 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47537 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47508 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47425 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47415 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47402 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47312 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47038 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47021 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=46789 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=46760 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=46576 ORDER BY chv_images.image_id ASC LIMIT 0,5)

Now, all this hassle is required only to support the album previews (thumbns below each album cover) shown in listings. This is because for each album the preview is generated aware of the user requesting it, so to list 64 albums the system spawns dozens of sub-queries that end up killing performance because for each album it needs to build the list of previews. Ideally, the preview list should be only generated once, and cached to don't struggle the system with this constant query.

If I remove the album previews from from listings we could boost performance without any extra headache, and I believe that most users won't bother at all with that subtle removal. I think that I will remove those, because this performance issue will sooner or later affect all installations.
 
  • Like
Reactions: twm
Rodolfo said:
For your use case the problem can be solved by creating a combined index for speeding the slow query:

SQL: 
ALTER TABLE `imgsha_data`.`chv_images` ADD INDEX `image_album_id_image_storage_id_image_is_approved` (`image_album_id`, `image_storage_id`, `image_is_approved`);

Then, doing INDEX type-hint the query now uses the index instead of traverse the entire dataset:

SQL: 
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49469 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49468 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49467 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49450 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49449 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49448 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49447 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49446 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49409 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49387 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49219 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49160 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49100 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49084 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=49052 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48938 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48909 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48802 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48717 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48672 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48626 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48604 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48549 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48504 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48454 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48422 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48241 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48151 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48105 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48083 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48038 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=48031 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47880 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47857 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47784 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47776 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47772 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47767 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47711 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47664 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47628 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47567 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47539 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47537 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47508 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47425 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47415 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47402 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47312 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47038 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=47021 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=46789 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=46760 ORDER BY chv_images.image_id ASC LIMIT 0,5)
UNION ALL
(SELECT * FROM chv_images USE INDEX(image_album_id_image_storage_id_image_is_approved) LEFT JOIN chv_storages ON chv_images.image_storage_id = chv_storages.storage_id WHERE chv_images.image_is_approved = 1 AND chv_images.image_album_id=46576 ORDER BY chv_images.image_id ASC LIMIT 0,5)

Now, all this hassle is required only to support the album previews (thumbns below each album cover) shown in listings. This is because for each album the preview is generated aware of the user requesting it, so to list 64 albums the system spawns dozens of sub-queries that end up killing performance because for each album it needs to build the list of previews. Ideally, the preview list should be only generated once, and cached to don't struggle the system with this constant query.

If I remove the album previews from from listings we could boost performance without any extra headache, and I believe that most users won't bother at all with that subtle removal. I think that I will remove those, because this performance issue will sooner or later affect all installations.
Click to expand...
Thank you very much
 
You must log in or register to reply here.
Back
Top