[jingai]

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.