I've just discovered MySQL support which is really cool, thanks! I went ahead and setup both the video and music database to be in MySQL and the video database works really well.
I've discovered that the music database is pretty slow so I've switched back to SQLite for that currently. Not a major issue but I thought I'd post here in case the information is useful.
Selecting an Artist from the list generates this query which main one I've run across causing the issue;
Code:
mysql> select * from albumview where (idAlbum IN (select song.idAlbum from song where song.idArtist=8) or idAlbum IN (select song.idAlbum from song join exartistsong on song.idSong=exartistsong.idSong where exartistsong.idArtist=8) or idAlbum IN (select album.idAlbum from album where album.idArtist=8) or idAlbum IN (select exartistalbum.idAlbum from exartistalbum where exartistalbum.idArtist=8));
Run manually on the server you can see that it's pretty slow;
Code:
2 rows in set (2 min 35.76 sec)
Code:
mysql> SELECT COUNT(*) FROM albumview;
+----------+
| COUNT(*) |
+----------+
| 1278 |
+----------+
1 row in set (0.04 sec)
I haven't had a chance to dig into it and find a workaround.
Wow, that is really, really slow.
No obvious reasons why that query would be slow. I'd break it down doing each of the subqueries and see which bit is the slow bit and then see what it's doing that is silly. It's probably one of the song fetches as a guess.
Cheers,
Jonathan
Yeah, it is a bit
I should really be working but I've just taken a few minutes to look at it now
All the subqueries execute fine (0.02 secs etc.) so it's something to do with how the main query is being put together by MySQL. I should mention that this happens with all artists, not just one.
Running an explain on the main query;
Code:
mysql> EXPLAIN select * from albumview where (idAlbum IN (select song.idAlbum from song where song.idArtist=8) or idAlbum IN (select song.idAlbum from song join exartistsong on song.idSong=exartistsong.idSong where exartistsong.idArtist=8) or idAlbum IN (select album.idAlbum from album where album.idArtist=8) or idAlbum IN (select exartistalbum.idAlbum from exartistalbum where exartistalbum.idArtist=8));
+----+--------------------+-----------+-----------------+---------------+--------------+---------+---------------------------+-------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-----------+-----------------+---------------+--------------+---------+---------------------------+-------+-----------------------------------------------------+
| 1 | PRIMARY | album | ALL | NULL | NULL | NULL | NULL | 1278 | Using where |
| 1 | PRIMARY | artist | eq_ref | PRIMARY | PRIMARY | 4 | xbmc_music.album.idArtist | 1 | |
| 1 | PRIMARY | genre | eq_ref | PRIMARY | PRIMARY | 4 | xbmc_music.album.idGenre | 1 | |
| 1 | PRIMARY | thumb | eq_ref | PRIMARY | PRIMARY | 4 | xbmc_music.album.idThumb | 1 | |
| 1 | PRIMARY | albuminfo | ref | idxAlbumInfo | idxAlbumInfo | 5 | xbmc_music.album.idAlbum | 11 | |
| 5 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
| 4 | DEPENDENT SUBQUERY | album | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
| 2 | DEPENDENT SUBQUERY | song | ALL | NULL | NULL | NULL | NULL | 16664 | Using where |
+----+--------------------+-----------+-----------------+---------------+--------------+---------+---------------------------+-------+-----------------------------------------------------+
9 rows in set (0.00 sec)
I'm no DBA but the last line there looks like it's probably the culprit. I had a little play and created an index;
Code:
CREATE INDEX test_idx ON song(idAlbum);
Now running the same query again is much better;
Code:
2 rows in set (0.15 sec)
For completeness here is the new explain;
Code:
mysql> EXPLAIN select * from albumview where (idAlbum IN (select song.idAlbum from song where song.idArtist=8) or idAlbum IN (select song.idAlbum from song join exartistsong on song.idSong=exartistsong.idSong where exartistsong.idArtist=8) or idAlbum IN (select album.idAlbum from album where album.idArtist=8) or idAlbum IN (select exartistalbum.idAlbum from exartistalbum where exartistalbum.idArtist=8));
+----+--------------------+-----------+-----------------+---------------+--------------+---------+---------------------------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-----------+-----------------+---------------+--------------+---------+---------------------------+------+-----------------------------------------------------+
| 1 | PRIMARY | album | ALL | NULL | NULL | NULL | NULL | 1278 | Using where |
| 1 | PRIMARY | artist | eq_ref | PRIMARY | PRIMARY | 4 | xbmc_music.album.idArtist | 1 | |
| 1 | PRIMARY | genre | eq_ref | PRIMARY | PRIMARY | 4 | xbmc_music.album.idGenre | 1 | |
| 1 | PRIMARY | thumb | eq_ref | PRIMARY | PRIMARY | 4 | xbmc_music.album.idThumb | 1 | |
| 1 | PRIMARY | albuminfo | ref | idxAlbumInfo | idxAlbumInfo | 5 | xbmc_music.album.idAlbum | 11 | |
| 5 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
| 4 | DEPENDENT SUBQUERY | album | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
| 3 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
| 2 | DEPENDENT SUBQUERY | song | index_subquery | test_idx | test_idx | 5 | func | 13 | Using where |
+----+--------------------+-----------+-----------------+---------------+--------------+---------+---------------------------+------+-----------------------------------------------------+
9 rows in set (0.00 sec)
I'm not sure if it's completely optimised but 2 minutes 30 to 0.15 seconds is much better.
Thanks for the sounding board.
Hmm, I'm no DBA either, but that doesn't make any sense to me at all. Why would an index on song.idAlbum be any use, when it's not being used at all to lookup there, only to return the indices that the album query will need. An index on song.idArtist would make would explain things a little, but even unindexed that query would be fast I should think (there's only 1 artist id to lookup in the songs table, so at worse it's linear through the song table). Perhaps it's doing the joining before doing the match or something?
EDIT: Wait, I misread - the subqueries are fine but it's the main query that's screwing up somehow. My impression is that it's trying to be flash by combining the queries and screwing it up completely.
Still, no reason why the index isn't a good thing either way. Our "normal" policy has been to create indicies on string fields or link tables, not generally on integer values within tables. This may have to change with mysql entering the mix.
Cheers,
Jonathan
Yeah, it will be the MySQL query optimiser doing magic things which us mere mortals cannot understand.
From what I've read, the ALL in the type column means that it's doing a full scan of the table rather than using an index which is why it was slow and now isn't. I can't explain it more than that with my limited knowledge.
At any rate, yeah, it works now so I guess there might need to be a few indices thrown in there as needed.
Cheers,
hads
Indeed - given they know that the optimizer is crap in this instance, surely they should just drop down to splitting it into 2 separate queries instead!
That's a simple fix for us that means we don't need an index. I'm not sure what (if any) advantage there is in indexing everything just for the sake of it.
Perhaps some DBAs out there could give us a clue as to the best approach
Cheers,
Jonathan
Just thought I'd follow up quickly;
With the index I added above as a workaround, the music (and video) library is working fine for me with MySQL (r32246 PPA package).
So it only seems to be that one query MySQL has issues with.
Cheers for all the great work.
Thanks for that usefull hint.
Switching from artist list to the album list of a particular artist happens now almost instantly. Took up to 10 seconds before. That was realy annoying
Used version: XBMC 10.0 Dharma
Generally speaking, you would want to index anything that can be used to 'point' to data. This includes, oddly enough, 'ID' columns. Usually, full plaintext column are NOT used to point at data, and thus are not usually indexed (unless you wish to issue a database search of that column (to try to find a song by a particular name, for instance))
The fact that the XBMC team didn't think of indexing integers is... unusual. Perhaps it isn't a problem with SQLite, but for full blown database programs like MySQL it is almost always recommended. ID integers are usually the most commonly-indexed column in a database table... Perhaps it would be wise to convert on the MySQL forums in ways to optimise the database for a relatively large project like this...
Here are the indexes that helped a lot here:
Code:
CREATE INDEX idAlbum_idx ON song(idAlbum);
CREATE INDEX idArtist_idx ON song(idArtist);
CREATE INDEX idArtist_idx ON album(idArtist);
CREATE INDEX idArtist_idx ON exartistsong(idArtist);
CREATE INDEX idArtist_idx ON exartistalbum(idArtist);