{"action":"created","issue":{"url":"https://api.github.com/repos/xbmc/xbmc/issues/8574","labels_url":"https://api.github.com/repos/xbmc/xbmc/issues/8574/labels{/name}","comments_url":"https://api.github.com/repos/xbmc/xbmc/issues/8574/comments","events_url":"https://api.github.com/repos/xbmc/xbmc/issues/8574/events","html_url":"https://github.com/xbmc/xbmc/pull/8574","id":121868155,"number":8574,"title":"Improve season and tvshow views query","user":{"login":"phate89","id":1254959,"avatar_url":"https://avatars.githubusercontent.com/u/1254959?v=3","gravatar_id":"","url":"https://api.github.com/users/phate89","html_url":"https://github.com/phate89","followers_url":"https://api.github.com/users/phate89/followers","following_url":"https://api.github.com/users/phate89/following{/other_user}","gists_url":"https://api.github.com/users/phate89/gists{/gist_id}","starred_url":"https://api.github.com/users/phate89/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/phate89/subscriptions","organizations_url":"https://api.github.com/users/phate89/orgs","repos_url":"https://api.github.com/users/phate89/repos","events_url":"https://api.github.com/users/phate89/events{/privacy}","received_events_url":"https://api.github.com/users/phate89/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":12,"created_at":"2015-12-12T17:37:04Z","updated_at":"2015-12-13T21:27:41Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/xbmc/xbmc/pulls/8574","html_url":"https://github.com/xbmc/xbmc/pull/8574","diff_url":"https://github.com/xbmc/xbmc/pull/8574.diff","patch_url":"https://github.com/xbmc/xbmc/pull/8574.patch"},"body":"Hi. After discovering how slow season_view queries are in MySQL thanks to http://trac.kodi.tv/ticket/16387 and after I found out that that query wasn't ok (wrong playcount in case of more than one folder) I started to look a way to improve it.\r\nThat's the best solution I could find..\r\nWith a new column in tvshow I can avoid to group twice, avoid to query a view twice and avoid even some join jumping straight to the path we want. That's all stuff that sqlite optimize very well but mysql not.\r\nAccording to my tests on my slow AMD E450:\r\na query like `SELECT * FROM season_view` is 300% faster than before in mysql and slightly faster in sqlite\r\na query like `SELECT * FROM tvshow_view` is 30% faster than before in mysql and slightly faster in sqlite.\r\nWith this changes the paths in the view should be the same as before. IF we want we can change the  code to keep the firsth path as default instead of showing the last one (right now grouping we get the last path)\r\nEvery comment and improvement is very welcome..\r\n"},"comment":{"url":"https://api.github.com/repos/xbmc/xbmc/issues/comments/164299739","html_url":"https://github.com/xbmc/xbmc/pull/8574#issuecomment-164299739","issue_url":"https://api.github.com/repos/xbmc/xbmc/issues/8574","id":164299739,"user":{"login":"jmarshallnz","id":406571,"avatar_url":"https://avatars.githubusercontent.com/u/406571?v=3","gravatar_id":"","url":"https://api.github.com/users/jmarshallnz","html_url":"https://github.com/jmarshallnz","followers_url":"https://api.github.com/users/jmarshallnz/followers","following_url":"https://api.github.com/users/jmarshallnz/following{/other_user}","gists_url":"https://api.github.com/users/jmarshallnz/gists{/gist_id}","starred_url":"https://api.github.com/users/jmarshallnz/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jmarshallnz/subscriptions","organizations_url":"https://api.github.com/users/jmarshallnz/orgs","repos_url":"https://api.github.com/users/jmarshallnz/repos","events_url":"https://api.github.com/users/jmarshallnz/events{/privacy}","received_events_url":"https://api.github.com/users/jmarshallnz/received_events","type":"User","site_admin":false},"created_at":"2015-12-13T21:27:41Z","updated_at":"2015-12-13T21:27:41Z","body":"First off, note that I'm not involved any longer, so any decision is not mine to make. i.e. feel free to ignore! :)\r\n\r\nOne presumes that as you never get all seasons from all shows in practice, once you filter on a show first it gets a lot quicker anyway? If so, the above query seems a good starting point.\r\n\r\nGiven that it's the fetching of path that slows everything down, I'd be tempted to try and remove it completely and see what breaks. The main thing is making sure the info dialog etc. works (so refreshing and updating a show) which IIRC may already be OK. After that the stuff that breaks is things like skins using the path to find images. For the latter, you could potentially fill the path in the background info loaders, though I'd go for just leaving it out to encourage the art system to be used properly rather than constantly polling media folders.\r\n\r\nFor seasons views, as far as I'm aware you shouldn't need the path for anything, and if anything, the path has nothing to do with the season anyway (it's one of the paths that the tvshow is assigned, so is essentially meaningless) so there's a good reason for killing it entirely there."}}