[Ahuch]

I'm not sure which end this is on.. xbmc itself or the sql server I'm running. I did notice this in advanced logging however:

18:28:33 T:1808 DEBUG: CVideoDatabase::RunQuery took 147389 ms for 51 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))))

Every other query is at least tolerable.

[Ahuch]

Running on a Synology 1812+ NAS if that makes a difference as well.

Code:

mysql> explain SELECT * FROM tvshowview WHERE (tvshowview.idShow IN (select tvsh
owview.idShow from tvshowview where (watchedcount > 0 AND watchedcount < totalCo
unt) OR (watchedcount = 0 AND tvshowview.idShow IN (select episodeview.idShow fr
om episodeview WHERE episodeview.idShow = tvshowview.idShow AND episodeview.resu
meTimeInSeconds > 0))));
+----+--------------------+----------------+--------+---------------------------                                                                                                                                                             ---------+---------------------+---------+--------------------------------------                                                                                                                                                             ------------+------+--------------------------+
| id | select_type        | table          | type   | possible_keys                                                                                                                                                                                   | key                 | key_len | ref                                                                                                                                                                                                           | rows | Extra                    |
+----+--------------------+----------------+--------+---------------------------                                                                                                                                                             ---------+---------------------+---------+--------------------------------------                                                                                                                                                             ------------+------+--------------------------+
|  1 | PRIMARY            | <derived4>     | ALL    | NULL                                                                                                                                                                                            | NULL                | NULL    | NULL                                                                                                                                                                                                          |  304 | Using where              |
|  4 | DERIVED            | tvshow         | ALL    | NULL                                                                                                                                                                                            | NULL                | NULL    | NULL                                                                                                                                                                                                          |  304 | Using filesort           |
|  4 | DERIVED            | tvshowlinkpath | ref    | ix_tvshowlinkpath_1                                                                                                                                                                             | ix_tvshowlinkpath_1 | 5       | MyVideos75.tvshow.idShow                                                                                                                                                                                      |   11 | Using index              |
|  4 | DERIVED            | path           | eq_ref | PRIMARY                                                                                                                                                                                         | PRIMARY             | 4       | MyVideos75.tvshowlinkpath.idPath                                                                                                                                                                              |    1 |                          |
|  4 | DERIVED            | episode        | ref    | ix_episode_show2                                                                                                                                                                                | ix_episode_show2    | 5       | MyVideos75.tvshow.idShow                                                                                                                                                                                      |  137 |                          |
|  4 | DERIVED            | files          | eq_ref | PRIMARY                                                                                                                                                                                         | PRIMARY             | 4       | MyVideos75.episode.idFile                                                                                                                                                                                     |    1 |                          |
|  2 | DEPENDENT SUBQUERY | <derived5>     | ALL    | NULL                                                                                                                                                                                            | NULL                | NULL    | NULL                                                                                                                                                                                                          |  304 | Using where              |
|  5 | DERIVED            | tvshow         | ALL    | NULL                                                                                                                                                                                            | NULL                | NULL    | NULL                                                                                                                                                                                                          |  304 | Using filesort           |
|  5 | DERIVED            | tvshowlinkpath | ref    | ix_tvshowlinkpath_1                                                                                                                                                                             | ix_tvshowlinkpath_1 | 5       | MyVideos75.tvshow.idShow                                                                                                                                                                                      |   11 | Using index              |
|  5 | DERIVED            | path           | eq_ref | PRIMARY                                                                                                                                                                                         | PRIMARY             | 4       | MyVideos75.tvshowlinkpath.idPath                                                                                                                                                                              |    1 |                          |
|  5 | DERIVED            | episode        | ref    | ix_episode_show2                                                                                                                                                                                | ix_episode_show2    | 5       | MyVideos75.tvshow.idShow                                                                                                                                                                                      |  137 |                          |
|  5 | DERIVED            | files          | eq_ref | PRIMARY                                                                                                                                                                                         | PRIMARY             | 4       | MyVideos75.episode.idFile                                                                                                                                                                                     |    1 |                          |
|  3 | DEPENDENT SUBQUERY | bookmark       | ALL    | ix_bookmark                                                                                                                                                                                     | NULL                | NULL    | NULL                                                                                                                                                                                                          |   77 | Using where              |
|  3 | DEPENDENT SUBQUERY | episode        | ref    | id_episode_file_2,ix_episo                                                                                                                                                             de_show2 | id_episode_file_2   | 5       | MyVideos75.bookmark.idFile                                                                                                                                                                                    |  138 | Using where              |
|  3 | DEPENDENT SUBQUERY | files          | eq_ref | PRIMARY,ix_files                                                                                                                                                                                | PRIMARY             | 4       | MyVideos75.episode.idFile                                                                                                                                                                                     |    1 | Using where              |
|  3 | DEPENDENT SUBQUERY | path           | eq_ref | PRIMARY                                                                                                                                                                                         | PRIMARY             | 4       | MyVideos75.files.idPath                                                                                                                                                                                       |    1 | Using index              |
|  3 | DEPENDENT SUBQUERY | seasons        | ref    | ix_seasons                                                                                                                                                                                      | ix_seasons          | 10      | MyVideos75.episode.idShow,MyVideos75.                                                                                                                                                             episode.c12 |   11 | Using index              |
|  3 | DEPENDENT SUBQUERY | tvshow         | eq_ref | PRIMARY                                                                                                                                                                                         | PRIMARY             | 4       | MyVideos75.episode.idShow                                                                                                                                                                                     |    1 | Using where; Using index |
+----+--------------------+----------------+--------+---------------------------                                                                                                                                                             ---------+---------------------+---------+--------------------------------------                                                                                                                                                             ------------+------+--------------------------+
18 rows in set (1.17 sec)

[Ned Scott]

Can you get us a full debug log (wiki) (via xbmclogs.com or pastebin.org )?

[Ahuch]

Can you get us a full debug log (wiki) (via xbmclogs.com or pastebin.org )?

http://xbmclogs.com/show.php?id=12019

(different run but same results)

[Ahuch]

EDIT: Thought I fixed it, but apparently not haha

[moontan77]

i seem to have the same issue as you - http://forum.xbmc.org/showthread.php?tid...pid1424032.

Did you find a solution?

15:49:09 T:139864761595648 DEBUG: RunQuery took 118872 ms for 324 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))))

[Hansaplast]

I ran into the same problem running MySQL on a QNAP NAS.
I noticed two possible issues;

1) XBMC uses VIEWs in MySQL (which is a recipe for problems in my experience) - but I'd have to change XBMC code.
2) My tables appeared all to be MyISAM tables - which Ive found to be slower than InnoDB.

I modified all my tables to InnoDB and that resolved all speed issues instantly.

You might want to verify your my.cnf file to make sure InnoDB settings are set right.

See also: Tweaking4All - XBMC and MySQL
(you'll find all the needed ALTER statements there as well)

[moontan77]

Hi this is a copy of the my.ini file - http://pastebin.com/uVsnyf2v

Is there anything that needs to be changed in that? I ran those alter commands but made no difference to my unfortunately.

[Hansaplast]

Where you original tables MyISAM or InnoDB?

Anyhow, here is what I have used in my cnf file - restart MySQL of course after making modifications.
Oh and of course make sure that the paths are adequate for your config (the paths below is what I use on my QNAP - I did not alter them from the original cnf file).

Code:

[client]
port = 3306
socket        = /tmp/mysql.sock
[mysqld]
port = 3306
socket        = /tmp/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 48M
table_cache = 256
sort_buffer_size = 8M
net_buffer_length = 16K
read_buffer_size = 8M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
server-id    = 1
innodb_data_home_dir = /usr/local/mysql/var/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/var/
innodb_buffer_pool_size = 64M
innodb_additional_mem_pool_size = 32M
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout