Login at Kodi Home

moontan77 · (This post was last modified: 2013-08-31, 17:44 by moontan77.)

If someone can help me trouble shoot the following issue i have it would be appreciated. I run mysql on a quad core pc and ssd and this query that is used by skin widgets is a killer as its spends so long running that python never seems to be available when trying to open addons so they take a few minutes to open. This is the query:

SELECT * FROM tvshowview WHERE (tvshowview.idShow IN (select tvshowview.idShow from tvshowview where (watchedcount > 0 AND watchedcount < totalCount) OR (watchedcount = 0 AND tvshowview.idShow IN (select episodeview.idShow from episodeview WHERE episodeview.idShow = tvshowview.idShow AND episodeview.resumeTimeInSeconds > 0))))

It took 4mins 35.07secs to run this query the other day and i have just wiped my tv shows from xbmc in the hope that rescanning them will fix this. I have a large database (20k+ episodes) and even after rescanning a small amount of these shows its already taking 5+ seconds to run this query. When scanning is finished it might not take 4 minutes but it looks like it will be a substantial amount of time.

This is my current mysql config file - http://pastebin.com/e8sWWEUT.

Just wondering does anyone see something there that might be an issue or know if any other items that could in a video library that could slow down queries.

I posted about this previously in the openelec forum and got this response - http://openelec.tv/forum/112-mysql-setup...uery#82913 (ignore bit about innodb as i already use that). The suggested query just took 0.48 sec compared to 4mins 35.07secs for the one above. I posted about this in the skin widgets thread but it isnt something that will change and i suppose this isnt an issue a lot of people are having.

Any suggestions would be appreciated!

**wsnipex** · 2013-08-31, 17:34

run the query trough "EXPLAIN EXTENDED" and pastebin the output. I'd guess its a missing index.

moontan77 · (This post was last modified: 2013-08-31, 22:30 by moontan77.)

heres a screenshot of the output

http://i.imgur.com/vXVfhEc.jpg

And here is the warnings output

mysql> SHOW WARNINGS;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message

|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1276 | Field or reference 'tvshowview.idShow' of SELECT #3 was resolved in SELECT #2

|
| Note | 1003 | select `tvshowview`.`idShow` AS `idShow`,`tvshowview`.`c00` AS `c00`,`tvshowview`.`c01` AS `c01`,`tvshowview`.`c02` AS `c02`,`tvshowview`.`c03` AS `c03`,`tvshowview`.`c04` AS `c04`,`tvshowview`.`c05` AS `c05`,`
tvshowview`.`c06` AS `c06`,`tvshowview`.`c07` AS `c07`,`tvshowview`.`c08` AS `c08`,`tvshowview`.`c09` AS `c09`,`tvshowview`.`c10` AS `c10`,`tvshowview`.`c11` AS `c11`,`tvshowview`.`c12` AS `c12`,`tvshowview`.`c13` AS `c13`,`tvs
howview`.`c14` AS `c14`,`tvshowview`.`c15` AS `c15`,`tvshowview`.`c16` AS `c16`,`tvshowview`.`c17` AS `c17`,`tvshowview`.`c18` AS `c18`,`tvshowview`.`c19` AS `c19`,`tvshowview`.`c20` AS `c20`,`tvshowview`.`c21` AS `c21`,`tvshow
view`.`c22` AS `c22`,`tvshowview`.`c23` AS `c23`,`tvshowview`.`strPath` AS `strPath`,`tvshowview`.`dateAdded` AS `dateAdded`,`tvshowview`.`lastPlayed` AS `lastPlayed`,`tvshowview`.`totalCount` AS `totalCount`,`tvshowview`.`watc
hedcount` AS `watchedcount`,`tvshowview`.`totalSeasons` AS `totalSeasons` from `myvideos75`.`tvshowview` where <in_optimizer>(`tvshowview`.`idShow`,<exists>(select 1 from `myvideos75`.`tvshowview` where ((((`tvshowview`.`watche
dcount` > 0) and (`tvshowview`.`watchedcount` < `tvshowview`.`totalCount`)) or ((`tvshowview`.`watchedcount` = 0) and <in_optimizer>(`tvshowview`.`idShow`,<exists>(select 1 from `myvideos75`.`episode` join `myvideos75`.`files`
join `myvideos75`.`tvshow` left join `myvideos75`.`seasons` on(((`myvideos75`.`seasons`.`idShow` = `myvideos75`.`tvshow`.`idShow`) and (`myvideos75`.`seasons`.`season` = `myvideos75`.`episode`.`c12`))) join `myvideos75`.`path`
join `myvideos75`.`bookmark` where ((`myvideos75`.`episode`.`idShow` = `myvideos75`.`tvshow`.`idShow`) and (`myvideos75`.`path`.`idPath` = `myvideos75`.`files`.`idPath`) and (`myvideos75`.`bookmark`.`type` = 1) and (`myvideos75
`.`bookmark`.`idFile` = `myvideos75`.`episode`.`idFile`) and (`myvideos75`.`files`.`idFile` = `myvideos75`.`episode`.`idFile`) and (`myvideos75`.`episode`.`idShow` = `tvshowview`.`idShow`) and (`myvideos75`.`bookmark`.`timeInSe
conds` > 0) and (<cache>(`tvshowview`.`idShow`) = `myvideos75`.`tvshow`.`idShow`)))))) and (<cache>(`tvshowview`.`idShow`) = `tvshowview`.`idShow`)))) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------+

edit:dropped all tables and scanned in most of my tv shows in again (about 14k). Its taking 43 seconds roughly to run the query. Is is that just that this query is slow at handling large databases? or is there something that can be done to speed it up?

**Martijn** · 2013-09-01, 14:20

gonna move this to JSON-RPC section as the query is trigger by that.
maybe @Montellese has a clue

moontan77 · 2013-09-07, 16:03

forgive me as i'm no coder but can this

SELECT tvshow.* FROM tvshowview tvshow WHERE (tvshow.watchedcount > 0 AND tvshow.watchedcount < tvshow.totalCount) OR (tvshow.watchedcount = 0 AND EXISTS (SELECT episode.idShow FROM episodeview episode WHERE episode.idShow = tvshow.idShow AND episode.resumeTimeInSeconds > 0))

be applied to this below? (I am set up to compile xbmc/openelec to see if the above works better on my system but unfortunately I would not be knowledgeable enough with regards to making code changes). If anyone can help it would be appreciated.

https://github.com/xbmc/xbmc/blob/master...t.cpp#L890

else if (strType == "episodes")
{
if (m_field == FieldInProgress)
return "episodeview.idFile " + negate + " IN (select idFile from bookmark where type = 1)";
}
else if (strType == "tvshows")
{
if (m_field == FieldInProgress)
return GetField(FieldId, strType) + negate + " IN (select " + GetField(FieldId, strType) + " from tvshowview where "
"(watchedcount > 0 AND watchedcount < totalCount) OR "
"(watchedcount = 0 AND " + GetField(FieldId, strType) + " IN "
"(select episodeview.idShow from episodeview WHERE episodeview.idShow = " + GetField(FieldId, strType) + " AND episodeview.resumeTimeInSeconds > 0)))";
}
}

**Montellese** · (This post was last modified: 2013-09-09, 22:52 by Montellese.)

There are two things here that probably cause bad performance:

Using IN is very ineffective and should probably be replaced with EXISTS as EXISTS results in a simple true/false result which can be detected early on whereas IN requires a full result set of the subquery.
Using subqueries for simple WHERE conditions. This is mostly the case because smartplaylists try to create SQL WHERE conditions that are as generic and independent as possible because the smartplaylist logic can't really provide any JOINs etc so it can't make an assumption about the available tables.

The first is probably easier to takle because it can be done pretty localized for every query that uses IN. The second is probably more difficult. Ideally smartplaylists would be able to define not only the WHERE part of an SQL query but also any necessary tables and JOIN statements. A half-way solution would be to improve those queries where it is certain that the table is available (in your case that would be "tvshowview").

EDIT: Any chance that you could do an SQL export of your video database so that I could use it for testing purposes? I only got ~3.5k episodes in my database so the query is pretty fast (on SQLite, don't have MySQL setup).

**Montellese** · 2013-09-09, 23:19

Completely untested but if you can build your own xbmc executable, please give https://github.com/Montellese/xbmc/compa...sql_optims a try. It contains the improvement for in progress tvshows and some others that replace IN + subquery with simple WHERE conditions.

moontan77 · 2013-09-10, 00:17

Thanks, much appreciated, i will try and test in the coming days. It looks like it might be mysql that is slow with the original query and not xbmc's inbuilt sqlite. I am scanning in my media on another pc without using mysql and so far it seems to be fast. I will test better this week. I've sent you a link to export of mysql database if you still want to check it.

moontan77 · (This post was last modified: 2013-09-14, 20:21 by moontan77.)

I'm finally getting around to compiling openelec but i'm getting this error with your patch. The xbmc version is 12.2-68a881d. Could i have a newer/older version of smartplaylist.cpp than what your patch is being applied to?

APPLY PATCH (common): /home/desktop/widgetfix/packages/mediacenter/xbmc/patches/xbmc-montellese.patch
patching file xbmc/playlists/SmartPlayList.cpp
Hunk #1 succeeded at 756 (offset -177 lines).
Hunk #2 succeeded at 897 (offset -179 lines).
Hunk #3 succeeded at 920 (offset -181 lines).
patching file xbmc/playlists/SmartPlayList.cpp
Hunk #1 succeeded at 635 (offset -177 lines).
Hunk #2 succeeded at 647 (offset -177 lines).
Hunk #3 succeeded at 745 (offset -177 lines).
patching file xbmc/playlists/SmartPlayList.cpp
Hunk #1 succeeded at 745 (offset -177 lines).
Hunk #2 succeeded at 825 (offset -177 lines).
Hunk #3 FAILED at 1015.
Hunk #4 FAILED at 1085.
2 out of 4 hunks FAILED -- saving rejects to file xbmc/playlists/SmartPlayList.cpp.rej
patching file xbmc/playlists/SmartPlayList.cpp
Hunk #1 succeeded at 825 (offset -177 lines).
Hunk #2 FAILED at 1015.
Hunk #3 FAILED at 1085.
Hunk #4 succeeded at 928 (offset -181 lines).
2 out of 4 hunks FAILED -- saving rejects to file xbmc/playlists/SmartPlayList.cpp.rej
make: *** [release] Error 1

this i think is the file being used by the openelec source i downloaded

http://pastebin.com/e2LWrJQh

**Montellese** · 2013-09-14, 21:01

My patch is based on almost current master which is way ahead of 12.2.

moontan77 · 2013-09-14, 21:07

ye i think i need to learn more about github! i thought the openelec compile had downloaded the latest xbmc source.

**popcornmix** · 2013-10-06, 14:38

I've tested the patches. Here is log from launching xbmc on Pi with Amber skin (using skin widgets for recently added/recommended). External MySQL database on a quite powerful PC.

This is top of tree Gotham:
http://pastebin.com/ivzXNb2k

This is with Montellese patches (with WHERE typos fixed):
http://pastebin.com/WY85tXqZ

(full log if of interest: http://xbmclogs.com/show.php?id=66651)

There is one big win in there:

Code:
12:23:47 T:2941252672   DEBUG: RunQuery took 14483 ms for 83 items query: SELECT * FROM tvshowview  WHERE (tvshowview.idShow IN (select tvshowview.idShow from tvshowview where (watchedcount > 0 AND watchedcount < totalCount) OR (watchedcount = 0 AND tvshowview.idShow IN (select episodeview.idShow from episodeview WHERE episodeview.idShow = tvshowview.idShow AND episodeview.resumeTimeInSeconds > 0))))

to

Code:
12:14:52 T:2941801536   DEBUG: RunQuery took 166 ms for 83 items query: SELECT * FROM tvshowview  WHERE ( ((tvshowview.watchedcount > 0 AND tvshowview.watchedcount < tvshowview.totalCount) OR (tvshowview.watchedcount = 0 AND EXISTS (SELECT 1 FROM episodeview WHERE episodeview.idShow = tvshowview.idShow AND episodeview.resumeTimeInSeconds > 0))))

These are still slow:

Code:
12:23:16 T:2941252672   DEBUG: RunQuery took 1849 ms for 1357 items query: select * from movieview  WHERE ((movieview.playCount IS NULL OR movieview.playCount < 1))

12:23:24 T:2941252672   DEBUG: RunQuery took 6823 ms for 12644 items query: select * from episodeview  WHERE ((episodeview.playCount IS NULL OR episodeview.playCount < 1))

and annoyingly get called three times before the GUI even appears. They would be worth improving.

**Martijn** · 2013-10-06, 14:41

well that's definitely a start Smile

When i ran the same patch on an Atom i did have some difference but not that extensive.

**Montellese** · 2013-10-06, 18:24

I updated the branch with the WHER/WHEREE c&p fixes. Thanks for pointing them out @popcornmix. There's not much optimisation that can be done on the other two queries you pointed out since those are already simple WHERE conditions. I'll create a PR for my branch ASAP.

In general there are many queries that are run far too often in xbmc but it's not easy to get rid of them because there may be cases where they need to be run multiple times...

**popcornmix** · 2013-10-06, 18:31

(2013-10-06, 18:24)Montellese Wrote: In general there are many queries that are run far too often in xbmc but it's not easy to get rid of them because there may be cases where they need to be run multiple times...

I understand it is hard for SmartPlayList.cpp to do anything about getting multiple calls, but it seems this is a bug in something at a higher level.
Is this likely to be the skin (Amber) or the Skin Widgets addon?

Calling the same slow SQL queries 3 times each before the GUI is even up and running doesn't seem right.