Summary: | ServiceSqlQueryMaker creates inefficient SQL queries | ||
---|---|---|---|
Product: | [Applications] amarok | Reporter: | Wolfgang Lorenz <wl-chmw> |
Component: | Services/Jamendo | Assignee: | Amarok Developers <amarok-bugs-dist> |
Status: | RESOLVED UNMAINTAINED | ||
Severity: | normal | CC: | oskar.jauch, ralf-engels, tuomas |
Priority: | NOR | ||
Version: | 2.8.0 | ||
Target Milestone: | later | ||
Platform: | Compiled Sources | ||
OS: | Linux | ||
Latest Commit: | Version Fixed In: | ||
Bug Depends on: | |||
Bug Blocks: | 235184 | ||
Attachments: | patch for testing the behaviour of the SQL collection with genre-less tracks from Jamendo added to the library |
Description
Wolfgang Lorenz
2011-02-24 16:48:12 UTC
Could you please submit a patch to http://git.reviewboard.kde.org? Well, there's a problem there, as I have no patch (containing the changed SQL query). I have tested the query, using mysql-query-browser. I could, if you wish, commit the patch, which fills your db with the missing Jamendo entries. But this might leave you with an Amarok, that waits more than an hour for a single query. Oh... okay... Here you are: https://git.reviewboard.kde.org/r/100742/ I just played around a bit and here is another way to query the data (without joining any tables): SELECT DISTINCT jamendo_artists.id, jamendo_artists.name, jamendo_artists.description , jamendo_artists.country, jamendo_artists.photo_url, jamendo_artists.jamendo_url, jamendo_artists.home_url FROM jamendo_artists WHERE jamendo_artists.id IN ( SELECT jamendo_albums.artist_id FROM jamendo_albums WHERE jamendo_albums.id IN ( SELECT jamendo_genre.album_id FROM jamendo_genre WHERE jamendo_genre.name = 'Unknown' ) ); I think you should take that to the amarok-devel@kde.org mailing list, the developers will be easier to reach than through a bug report. Some comments: 1. the review request was discarded because of some issues. 2. leaving out the "tracks" table probably doesn't work because we need to group by track IDs. 3. The grouping by track ids is probably needed to filter out duplicate tracks that appear in several genres. Comment from me: I think it would make sense to allow a track being in several genres. Especially important for my soundtracks that might be "Soundtrack" and "Rock" at the same time. This is an automated message from the triager: Amarok 2.4.1 has been released on May 8 already. Could you please upgrade and test if you can still reproduce this bug? Without feedback within a month we will close this bug as resolved. Thank you for your understanding. I just updated Amarok and the problem still exists in 2.4.1. Thank you for the feedback. There has been a lot of work on the Amarok database since 2.4.1, please test Amarok 2.6.90 aka 2.7 beta or Amarok 2.7, to be released in a few days. Created attachment 76438 [details]
patch for testing the behaviour of the SQL collection with genre-less tracks from Jamendo added to the library
NOTE: the given patch is for testing purposes only and not meant to be applied to the Amarok sources for stable releases – Don't try this at home!
What the appended patch does:
* set genre in Jamendo tracks, that come without a genre to "Unknown-test"
* also, don't ignore genre "Unknown"
* print out running SQL queries with block information
What I did:
* applied patch to Amarok 2.6.90
* ran Amarok with empty configuration and databases
* updated Jamendo track list
* opened genre "Unknown-test"
Result (note delay, at the end):
amarok: BEGIN: QStringList Collections::ServiceSqlQueryMaker::runQuery(const QString&)
amarok: running SQL query: "SELECT DISTINCT jamendo_artists.id, jamendo_artists.name, jamendo_artists.description , jamendo_artists.country, jamendo_artists.photo_url, jamendo_artists.jamendo_url, jamendo_artists.home_url FROM jamendo_tracks LEFT JOIN jamendo_albums ON jamendo_tracks.album_id = jamendo_albums.id LEFT JOIN jamendo_artists ON jamendo_albums.artist_id = jamendo_artists.id LEFT JOIN jamendo_genre ON jamendo_genre.album_id = jamendo_albums.id WHERE 1 AND jamendo_genre.name = 'Unknown-test' GROUP BY jamendo_tracks.id;"
amarok: END__: QStringList Collections::ServiceSqlQueryMaker::runQuery(const QString&) [DELAY Took (quite long) 4.3e+03s]
Long story made short: The bug still exists.
Thank you for the feedback Partially reproducible with Amarok 2.8. Amarok doesn't hung but opening the unknown genre takes to long. Mind you, queries on the Jamendo db do take long as it is really huge, and it is a remote service, so that doesn't really help ... Than it isn't reproducible with Amarok 2.8 because it's possible to open the "unknown"-genre section. Closing as the old Jamendo API was apparently shut down some years ago and Amarok does not currently have functional Jamendo support. |