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?

Interesting findings. Could you please make a bug report and post your information there? This forum isn't read by all developers.

Thanks for your reply, I will do that immediately.

https://bugs.kde.org/show_bug.cgi?id=142999

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.