MySQL: Inefficient queries?
#1
I've been using the MySQL functionality of XBMC for a while and after trying a number of things I've noticed that bringing up the Artists list in the Music section is extremely slow so today I decided to do some troubleshooting.

To give an idea of performance, my database is:
722 Artists
12806 Tracks
953 Albums

A "select *" for any of the individual tables takes less than 0.25 seconds.

Going into any of the other XBMC menus (Songs, Albums, Shows, Videos, etc) is pretty snappy, but, going into Artists takes about 7 seconds.

I put a trace on MySQL and found the following happened for the Artists query:
Code:
758 Connect   [email protected] on
                  758 Query     SET NAMES utf8
                  758 Query     show databases like 'xbmc_music18'
                  758 Query     show tables
                  758 Init DB   xbmc_music18
                  758 Query     show databases like 'xbmc_music18'
                  758 Query     show tables
                  758 Query     SELECT idVersion FROM version
                  758 Query     select * from artist where strArtist like 'Various artists'
                  758 Query     select * from artist where (idArtist IN (select song.idArtist from song) or idArtist IN (select exartistsong.idArtist from exartistsong) or idArtist IN (select album.idArtist from album) or idArtist IN (select exartistalbum.idArtist from exartistalbum ))  and artist.strArtist != "" and artist.idArtist<>2
                  758 Query     select * from artistinfo join artist on artist.idArtist=artistinfo.idArtist where artistinfo.idArtist = 3
                  758 Query     select * from artistinfo join artist on artist.idArtist=artistinfo.idArtist where artistinfo.idArtist = 4
                  758 Query     select * from artistinfo join artist on artist.idArtist=artistinfo.idArtist where artistinfo.idArtist = 9
                  758 Query     select * from artistinfo join artist on artist.idArtist=artistinfo.idArtist where artistinfo.idArtist = 12
                  758 Query     select * from artistinfo join artist on artist.idArtist=artistinfo.idArtist where artistinfo.idArtist = 62
                  758 Query     select * from artistinfo join artist on artist.idArtist=artistinfo.idArtist where artistinfo.idArtist = 76
                  758 Query     select * from artistinfo join artist on artist.idArtist=artistinfo.idArtist where artistinfo.idArtist = 78
                  758 Query     select * from artistinfo join artist on artist.idArtist=artistinfo.idArtist where artistinfo.idArtist = 80
                  758 Query     select * from artistinfo join artist on artist.idArtist=artistinfo.idArtist where artistinfo.idArtist = 81
                  758 Query     select * from artistinfo join artist on artist.idArtist=artistinfo.idArtist where artistinfo.idArtist = 96
                  758 Query     select * from artistinfo join artist on artist.idArtist=artistinfo.idArtist where artistinfo.idArtist = 97
                  758 Query     select * from artistinfo join artist on artist.idArtist=artistinfo.idArtist where artistinfo.idArtist = 112
                  ----snipped----

It seems that XBMC performs a recursive query against the "artistinfo" table for each of the artist ID's returned by the artist search and this is where the delay is coming from.

Is there any way to stop XBMC from querying the artistinfo as an option in advancedsettings? Or having it perform the query on the fly is someone stays on an artist for longer than a second?

Thanks for any pointers or help.
Reply
#2
Your best bet is joining the tables in the initial query. There's no particularly reason why it can't be joined directly that I can see - you still have a 1:1 mapping involved.

You'd need to update the way the info is fetched - see GetArtistsByWhere() in MusicDatabase.cpp.

Cheers,
Jonathan
Always read the XBMC online-manual, FAQ and search the forum before posting.
Do not e-mail XBMC-Team members directly asking for support. Read/follow the forum rules.
For troubleshooting and bug reporting please make sure you read this first.


Image
Reply
#3
This is fixed in the MusicDatabase fixes in Pull Request #1090. This is the commit, you might be able to back port it if you felt so inclined. It wouldn't be a lot of work to port just this commit into master.

https://github.com/night199uk/xbmc/commi...27b7925729
Reply

Logout Mark Read Team Forum Stats Members Help
MySQL: Inefficient queries?0