[Martijn]

Thanks for the heads up, I didn't think of checking the add-on settings.
Looks like that'll work for me as well and would be okay across script updates.

Still weird that it would make such a big difference.
Could you please post two full Debug Log on xbmclogs.com with each settings and give the link so I can have our database experts look at this. You do use at least RC1 right?

[derekr]

derekr: See if you can figure out why the query is taking so long (i.e. do the same query on mysql directly, try profiling it etc.) It might be missing an index or some such and causing mysql to do something really silly like an effective O(n^2) query.

Cheers,
Jonathan

I've done the same query locally on the system, and it seems that it's taking roughly the same amount of time.

Here's some examples:

On my main server if I issue the select query manually (after starting mysqld, i.e. before queries are cached) I get:

Code:

mysql> 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))));
Empty set (5.39 sec)

On my backup server, using the same query (again, before query is cached):

Code:

mysql> 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))));
Empty set (53.95 sec)

I'm not sure about the huge disparity there (about one order of magnitude,) but my main server is more powerful and has the DB on an SSD, whereas my backup is a QNAP system with a number of 5400RPM drives in a RAID5, so I figured that had something to do with it.

I'm not sure about the indexes (I see some listed, but I'm not entirely sure if they're all present/correct) - basically what I'm doing is dumping the database on my 'main' server, and importing it into my 'backup' server, so I would expect they would be largely the same.

---

Thanks for the heads up, I didn't think of checking the add-on settings.
Looks like that'll work for me as well and would be okay across script updates.

Still weird that it would make such a big difference.
Could you please post two full debug log on xbmclogs.com with each settings and give the link so I can have our database experts look at this. You do use at least RC1 right?

I am using XBMC RC1 - the HTPC accessing the database is running Openelec 3.0 Beta5

I think the speed up I was seeing before was a red herring, as I think the server is caching these queries so they end up taking a lot longer - in subsequent tests, I re-launched the mysql server after each test and the results seem to be the same (in fact, the select query doesn't seem to change at all after re-verifying the debug log.)

Just for sake of comparison though, here are logs for a non-cached run: http://sprunge.us/WROd and a cached run: http://sprunge.us/RXJb

[jmarshall]

Can you do an EXPLAIN on that query and see whether there's something silly going on?

Then try breaking down the query a bit (e.g. execute the subqueries) to see which bit is slow.

[derekr]

Can you do an EXPLAIN on that query and see whether there's something silly going on?

Then try breaking down the query a bit (e.g. execute the subqueries) to see which bit is slow.

Here's what EXPLAIN shows about the entire query: http://pastebin.com/iwMLUxS8

I'm not terribly experienced with mysql, so I'm not entirely sure how to break down the full query properly, but here's what I tried:

Code:

mysql> select tvshowview.idShow from tvshowview where (watchedcount > 0 AND watchedcount < totalCount);
Empty set (0.31 sec)

mysql> select * from tvshowview where (tvshowview.idShow IN (select tvshowview.idShow from tvshowview where (watchedcount > 0 AND watchedcount < totalCount)));
Empty set (0.72 sec)

mysql> select * from tvshowview where (watchedcount = 0 AND tvshowview.idShow IN (select episodeview.idShow from episodeview WHERE episodeview.idShow = tvshowview.idShow AND episodeview.resumeTimeInSeconds > 0));
Empty set (0.62 sec)

mysql> 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));
Empty set (0.61 sec)

Just to make sure things weren't messed up, I tried to run the full thing again and got the ~53s again.

Code:

mysql> 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))));
Empty set (53.12 sec)

These results seem odd to me, is there some sort of buffer it's running up against where once you break that threshold it has to go and do a bunch of queries internally over again?

[derekr]

Here's a profile log from mysql for the entire query: http://xbmclogs.com/show.php?id=22254

(note: it's very big)