| Summary: | lower() not needed in MySQL SELECT | ||
|---|---|---|---|
| Product: | [Applications] amarok | Reporter: | Alf Eaton <sites> |
| Component: | Collections/Local | Assignee: | Amarok Developers <amarok-bugs-dist> |
| Status: | RESOLVED FIXED | ||
| Severity: | normal | ||
| Priority: | NOR | ||
| Version: | 1.4.7 | ||
| Target Milestone: | --- | ||
| Platform: | Ubuntu | ||
| OS: | Linux | ||
| Latest Commit: | Version Fixed In: | ||
| Sentry Crash Report: | |||
| Attachments: | Patch to remove lower() from CollectionDB::artistAlbumList | ||
Created attachment 22162 [details]
Patch to remove lower() from CollectionDB::artistAlbumList
Do you know if this also the case for sqlite? I guess " ORDER BY lower( album.name );" ); could also be changed then According to this thread: http://www.mail-archive.com/sqlite-users@sqlite.org/msg26780.html http://www.mail-archive.com/sqlite-users@sqlite.org/msg26800.html SQLite queries are case-sensitive, so you have to use either lower() or "collate NOCASE". It looks like ORDER BY is case-insensitive as well in MySQL, as long as the fields aren't binary, so the lower() could be removed there as well. SVN commit 741761 by seb: Optimise checking if an artist has albums. the lower() function is not needed for mysql (but still for sqlite). Grep tells me that the only use for this method is for showing related artists in the context browser. I can confirm that this does not introduce regressions with mysql - please test for psql and sqlite. Check by enabling related artists in the context browser and viewing the results. Artists which are not in the collection should be listed in italics. Thanks to Alf Eaton for the patch BUG: 152749 M +11 -1 collectiondb.cpp WebSVN link: http://websvn.kde.org/?view=rev&revision=741761 SVN commit 741766 by seb: update changelog CCBUG: 152749 M +2 -0 ChangeLog WebSVN link: http://websvn.kde.org/?view=rev&revision=741766 |
Version: 1.4.7 (using KDE KDE 3.5.8) Installed from: Ubuntu Packages In MySQL, "SELECT x = y" statements are case-insensitive. The CollectionDB::albumListOfArtist function uses lower(artist.name) in a SELECT statement, which slows it down dramatically (removing the lower() speeds up the query 100x). Here's a patch: Index: collectiondb.cpp =================================================================== --- collectiondb.cpp (revision 740298) +++ collectiondb.cpp (working copy) @@ -2480,7 +2480,7 @@ { return query( "SELECT DISTINCT album.name FROM tags, album, artist WHERE " "tags.album = album.id AND tags.artist = artist.id " - "AND lower(artist.name) = lower('" + escapeString( artist ) + "') " + + "AND artist.name = '" + escapeString( artist ) + "' " + ( withUnknown ? QString() : "AND album.name <> '' " ) + ( withCompilations ? QString() : "AND tags.sampler = " + boolF() ) + deviceidSelection() + " ORDER BY lower( album.name );" );