[d0wnl0rd]

You should be able to reproduce the problem by running the same query (SELECT * FROM seasonview WHERE seasonview.idShow = N) where N is a valid tvshow id, in your case 405, using any client, eg. MySQL Workbench.

Testing here on a fairly low powered NAS (HP N36L, approximately Atom-class with 8GB RAM, v89 db) and this query completes in less than 0.281 seconds. Running without the WHERE clause returns 215 rows and completes in 0.297 seconds. This is for a library with 61 tvshows, 215 seasons and 2992 episodes, and produces scratch files of less than 100KB:

Just to give you a benchmark on a really low-powered NAS: mysql running on Synology DS210j provides me with 3,0x seconds running that query with a N-parameter (giving 4 rows), and 3.1x secondy for a full query (giving 231 rows). 81 tvshows, 458 seasons, 3.241 episodes.

The same query on nearly the same amount of data running on version 88 provides consistently half a second faster runtime. (87, 86 on the same level as 89)

(scratch head)
Does anyone know how to get an execution plan on mysql?
UPDATE: If I expand the query and enter the search criteria directly e.g. to:
select `seasons`.`idSeason` AS `idSeason`,`seasons`.`idShow` AS `idShow`,`seasons`.`season` AS `season`,`tvshowview`.`strPath` AS `strPath`,`tvshowview`.`c00` AS `showTitle`,`tvshowview`.`c01` AS `plot`,`tvshowview`.`c05` AS `premiered`,`tvshowview`.`c08` AS `genre`,`tvshowview`.`c14` AS `strStudio`,`tvshowview`.`c13` AS `mpaa`,count(distinct `episodeview`.`idEpisode`) AS `episodes`,count(`files`.`playCount`) AS `playCount` from (((`seasons` join `tvshowview` on((`tvshowview`.`idShow` = `seasons`.`idShow`))) join `episodeview` on(((`episodeview`.`idShow` = `seasons`.`idShow`) and (`episodeview`.`c12` = `seasons`.`season`)))) join `files` on((`files`.`idFile` = `episodeview`.`idFile`)) and seasons.idShow = 25) group by `seasons`.`idSeason`
then I do get an execution time of about 0,5x seconds.
So it looks to me as if the view is populating the complete dataset and only then it will be filtered according to the criteria.