Albums view very slow

[gs11]

My collection contains 767 albums (10014 songs).
When browsing by album the first load is very slow causing MySQL to use 100% CPU and finally causing a timeout in the browser.
Subsequent browses are quicker but scrolling to the bottom to display the next n albums results in the same behavior but no timeout since it's quicker.

Running sonerezh (git master) on a nginx/debian machine with 2.4 GHz CPU / 4GB RAM.

[gs11]

Ok, looked at the process list during load and the query is the culprit:

 SELECT `Song`.`id`, `Song`.`band`, `Song`.`album`, `Song`.`cover` FROM `sonerezh`.`songs` AS `Song` WHERE (`Song`.`id`, `Song`.`album`) IN (SELECT MIN(`subsong`.`id`), `subsong`.`album` FROM `sonerezh`.`songs` AS `subsong`   WHERE 1 = 1  GROUP BY `subsong`.`album`)  ORDER BY `Song`.`album` ASC  LIMIT 216, 36

Code is located in SongsController.php#L246

Why is a sub query (slow) used when the following statement yields the same result?

SELECT MIN(id), band, album, cover FROM songs GROUP BY album LIMIT 216, 36;

[gs11]

Newly created pull request #283 fixes this.

[davebiffuk]

With this change, the album view for my collection (~700 albums, ~7000 mp3 files) loads in just a second or so, hosted on a tiny ARM-based server - thanks!

[gs11]

Fix in PR #339