Version: Latest stable (using KDE KDE 3.5.4) Installed from: Compiled From Sources OS: Linux Am I the only one who have noticed that the MySQL queries for a large DB is painfully slow? I have a modern computer and not THAT many mp3s. Tables: artist: 3.115 rows album: 2.525 rows embed: 1.130 rows tags: 15.982 rows I saw for example these queries that took over 1 minute each to execute! DELETE FROM artist WHERE id NOT IN ( SELECT artist FROM tags ); DELETE FROM album WHERE id NOT IN ( SELECT album FROM tags ); So I just created indexes for these two fields: CREATE INDEX tags_artist_index ON tags (artist); CREATE INDEX tags_album_index ON tags (album); and now the above queries takes 0,04 seconds to execute! I also saw this query execute multiple times and every time taking over 1 minute: SELECT embed.deviceid, embed.url FROM embed LEFT JOIN tags ON (embed.url = tags.url AND embed.deviceid = tags.deviceid) WHERE tags.url IS NULL; Reduced the time to 1,31 seconds by adding four indexes (can probably be optimized even more): CREATE INDEX tags_deviceid_index ON tags (deviceid); CREATE INDEX embed_deviceid_index ON embed (deviceid); CREATE INDEX tags_url_index ON tags (url(20)); CREATE INDEX embed_url_index ON embed (url(20)); Those indexes also speeded up SELECT s.deviceid,s.url FROM statistics AS s LEFT JOIN tags AS t ON s.deviceid = t.deviceid AND s.url = t.url WHERE t.url IS NULL AND s.deviceid != -2; From 30 to 0,22 seconds. Have I missed something or is this usable for anyone else than me?
Now I saw that the sqlite-DB uses indeces on these columns but shouldn't the MySQL-DB do that as well? sqlite> .indices tags album_tag artist_tag composer_tag genre_tag sampler_tag url_tag year_tag sqlite> .indices embed embed_hash embed_url
This simple task worked for me. Amarok was almost unusable before running those simple querys in MySQL Query Browser. I had originally installed with a SQLite DB but converted to MySQL.
SVN commit 678474 by seb: Optimise: create additional indices as suggested by Gosta. I've tested with mysql, and confirm the speed increases. Can users of sqlite and psql please test this as well, thanks. BUG: 142999 CCMAIL: amarok@kde.org M +8 -0 collectiondb.cpp --- branches/stable/extragear/multimedia/amarok/src/collectiondb.cpp #678473:678474 @@ -735,6 +735,14 @@ query( "CREATE INDEX genre_idx ON genre( name );" ); query( "CREATE INDEX year_idx ON year( name );" ); + query( "CREATE INDEX tags_artist_index ON tags( artist );" ); + query( "CREATE INDEX tags_album_index ON tags( album );" ); + query( "CREATE INDEX tags_deviceid_index ON tags( deviceid ); "); + query( "CREATE INDEX tags_url_index ON tags( url(20) ); "); + + query( "CREATE INDEX embed_deviceid_index ON embed( deviceid ); "); + query( "CREATE INDEX embed_url_index ON embed( url(20) ); "); + query( "CREATE INDEX related_artists_artist ON related_artists( artist );" ); debug() << "Finished creating indices, stop ignoring errors" << endl;
SVN commit 678475 by seb: Forward port bug 142999 CCBUG: 142999 M +10 -3 collectiondb.cpp --- trunk/extragear/multimedia/amarok/src/collectiondb.cpp #678474:678475 @@ -715,7 +715,6 @@ "artist " + textColumnType() + "," "suggestion " + textColumnType() + "," "changedate INTEGER );" ) ); - query( "CREATE INDEX related_artists_artist ON related_artists( artist );" ); createIndices(); } @@ -734,7 +733,7 @@ //tables which are not created in that function. debug() << "Creating indices, ignore errors about already existing indices" << endl; - query( "CREATE UNIQUE INDEX url_tag ON tags( url, deviceid );" ); + query( "CREATE UNIQUE INDEX url_tag ON tags( url, deviceid );", true ); query( "CREATE INDEX album_tag ON tags( album );" ); query( "CREATE INDEX artist_tag ON tags( artist );" ); query( "CREATE INDEX composer_tag ON tags( composer );" ); @@ -760,6 +759,14 @@ query( "CREATE INDEX genre_idx ON genre( name );" ); query( "CREATE INDEX year_idx ON year( name );" ); + query( "CREATE INDEX tags_artist_index ON tags( artist );" ); + query( "CREATE INDEX tags_album_index ON tags( album );" ); + query( "CREATE INDEX tags_deviceid_index ON tags( deviceid );" ); + query( "CREATE INDEX tags_url_index ON tags( url(20) );" ); + + query( "CREATE INDEX embed_deviceid_index ON embed( deviceid );" ); + query( "CREATE INDEX embed_url_index ON embed( url(20) );" ); + query( "CREATE INDEX related_artists_artist ON related_artists( artist );" ); debug() << "Finished creating indices, stop ignoring errors" << endl; @@ -4784,7 +4791,7 @@ // If Database engine was changed, recreate DbConnections. destroy(); initialize(); -//PORT 2.0 CollectionView::instance()->renderView(); +//PORT 2.0 CollectionView::instance()->renderView(); PlaylistBrowser::instance()->loadPodcastsFromDatabase(); emit databaseEngineChanged();