Queuing songs is very slow with MySQL 5.7.18 (and possibly lower):
Code:
# Time: 2017-07-25T02:46:21.336164Z
# User@Host: xbmc[xbmc] @ xxx.xxx.xxx [192.168.1.2] Id: 191
# Query_time: 0.197212 Lock_time: 0.000169 Rows_sent: 1 Rows_examined: 130900
SET timestamp=1500950781;
SELECT songview.*,songartistview.* FROM songview JOIN songartistview ON songview.idSong = songartistview.idSong WHERE songview.idSong = 25345 ORDER BY songartistview.idRole, songartistview.iOrder;
This wasn't the case with MySQL 5.6.
When I try that query manually, it executes instantly. So I started logging all queries to see what's going on and find Kodi does:
Code:
2017-07-25T02:31:09.800543Z 15 Connect [email protected] on using TCP/IP
2017-07-25T02:31:09.800581Z 15 Query SET NAMES utf8
2017-07-25T02:31:09.800621Z 15 Query SET SESSION sql_mode = (SELECT REPLACE(@@SESSION.sql_mode,'ONLY_FULL_GROUP_BY',''))
2017-07-25T02:31:09.800663Z 15 Query SELECT @@SESSION.optimizer_switch
2017-07-25T02:31:09.800712Z 15 Query SET SESSION optimizer_switch = 'derived_merge=off'
2017-07-25T02:31:09.800760Z 15 Query show databases like 'xbmc_music60'
2017-07-25T02:31:09.800869Z 15 Init DB xbmc_music60
2017-07-25T02:31:09.800912Z 15 Query show databases like 'xbmc_music60'
2017-07-25T02:31:09.800993Z 15 Query show tables
2017-07-25T02:31:09.801116Z 15 Query SELECT songview.*,songartistview.* FROM songview JOIN songartistview ON songview.idSong = songartistview.idSong WHERE songview.idSong = 8627 ORDER BY songartistview.idRole, songartistview.iOrder
If I do all of the above manually, it takes just as long as it does from Kodi (~0.2 seconds). I found the culprit is:
Code:
2017-07-25T02:31:09.800712Z 15 Query SET SESSION optimizer_switch = 'derived_merge=off'
If I omit this, it's fine again.
Here's the output from EXPLAIN, if it helps:
Code:
mysql> EXPLAIN SELECT songview.*,songartistview.* FROM songview JOIN songartistview ON songview.idSong = songartistview.idSong WHERE songview.idSong = 8627 ORDER BY songartistview.idRole, songartistview.iOrder;
+----+-------------+-------------+------------+--------+---------------------------------+-----------------+---------+-----------------------------------+-------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+--------+---------------------------------+-----------------+---------+-----------------------------------+-------+----------+-----------------------------+
| 1 | PRIMARY | <derived3> | NULL | ref | <auto_key0> | <auto_key0> | 5 | const | 10 | 100.00 | Using where; Using filesort |
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 4 | const | 10 | 100.00 | NULL |
| 3 | DERIVED | role | NULL | ALL | PRIMARY | NULL | NULL | NULL | 6 | 100.00 | NULL |
| 3 | DERIVED | song_artist | NULL | ref | idxSongArtist_3,idxSongArtist_4 | idxSongArtist_4 | 5 | xbmc_music60.role.idRole | 4350 | 100.00 | Using where |
| 3 | DERIVED | artist | NULL | eq_ref | PRIMARY | PRIMARY | 4 | xbmc_music60.song_artist.idArtist | 1 | 100.00 | NULL |
| 2 | DERIVED | song | NULL | ALL | idxSong7,idxSong3,idxSong6 | NULL | NULL | NULL | 25620 | 100.00 | Using where |
| 2 | DERIVED | album | NULL | eq_ref | PRIMARY | PRIMARY | 4 | xbmc_music60.song.idAlbum | 1 | 100.00 | NULL |
| 2 | DERIVED | path | NULL | eq_ref | PRIMARY | PRIMARY | 4 | xbmc_music60.song.idPath | 1 | 100.00 | NULL |
+----+-------------+-------------+------------+--------+---------------------------------+-----------------+---------+-----------------------------------+-------+----------+-----------------------------+
~0.2 doesn't seem like a long time, but I help with an
Alexa skill to control Kodi via JSON-RPC and we have a few commands that add songs in bulk, and this extra delay causes the skill to time out.
