Bug 142999

Summary:	Slow database queries (especially MySQL) with optimization suggestions
Product:	[Applications] amarok	Reporter:	Gosta <gostaj>
Component:	general	Assignee:	Amarok Developers <amarok-bugs-dist>
Status:	RESOLVED FIXED
Severity:	normal
Priority:	NOR
Version:	1.4.5
Target Milestone:	---
Platform:	Compiled Sources
OS:	Linux
Latest Commit:		Version Fixed In:
Sentry Crash Report:

Description Gosta 2007-03-15 10:52:39 UTC

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?

Comment 1 Gosta 2007-03-16 11:08:44 UTC

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

Comment 2 Kevin Pattison 2007-06-03 11:23:36 UTC

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.

Comment 3 Seb Ruiz 2007-06-21 15:37:59 UTC

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;

Comment 4 Seb Ruiz 2007-06-21 15:39:45 UTC

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();