extragear/multimedia/amarok/src

[Peter C. Ndikuwera]

SVN commit 583113 by pndiku:

Using a GROUP BY in a query rather than DISTINCT is faster 
(up to 10 times in my tests) for PostgreSQL.
Please test for MySQL and SQLite.

CCMAIL: amarok at kde.org


 M  +5 -13     collectiondb.cpp  


--- trunk/extragear/multimedia/amarok/src/collectiondb.cpp #583112:583113
@@ -2317,11 +2317,7 @@
     if ( !withCompilations )
         qb.setOptions( QueryBuilder::optNoCompilations );
 
-    qb.setOptions( QueryBuilder::optRemoveDuplicates );
-    //FIXME max: quick hack to improve the DB response time
-    //without it the query looks something like
-    //SELECT DISTINCT artist.name FROM artist,tags WHERE 1 AND tags.deviceid IN (1,2,3) ORDER BY LOWER (artist.name)
-    //---> very bad...has to be fixed for all CollectionDB::*List methods
+    qb.groupBy( QueryBuilder::tabArtist, QueryBuilder::valName );
     qb.setOptions( QueryBuilder::optShowAll );
     qb.sortBy( QueryBuilder::tabArtist, QueryBuilder::valName );
     return qb.run();
@@ -2340,11 +2336,7 @@
     if ( !withCompilations )
         qb.setOptions( QueryBuilder::optNoCompilations );
 
-    qb.setOptions( QueryBuilder::optRemoveDuplicates );
-    //FIXME max: quick hack to improve the DB response time
-    //without it the query looks something like
-    //SELECT DISTINCT composer.name FROM composer,tags WHERE 1 AND tags.deviceid IN (1,2,3) ORDER BY LOWER (composer.name)
-    //---> very bad...has to be fixed for all CollectionDB::*List methods
+    qb.groupBy( QueryBuilder::tabComposer, QueryBuilder::valName );
     qb.setOptions( QueryBuilder::optShowAll );
     qb.sortBy( QueryBuilder::tabComposer, QueryBuilder::valName );
     return qb.run();
@@ -2362,7 +2354,7 @@
     if ( !withCompilations )
         qb.setOptions( QueryBuilder::optNoCompilations );
 
-    qb.setOptions( QueryBuilder::optRemoveDuplicates );
+    qb.groupBy( QueryBuilder::tabAlbum, QueryBuilder::valName );
     qb.setOptions( QueryBuilder::optShowAll );
     qb.sortBy( QueryBuilder::tabAlbum, QueryBuilder::valName );
     return qb.run();
@@ -2383,7 +2375,7 @@
     if ( !withCompilations )
         qb.setOptions( QueryBuilder::optNoCompilations );
 
-    qb.setOptions( QueryBuilder::optRemoveDuplicates );
+    qb.groupBy( QueryBuilder::tabGenre, QueryBuilder::valName );
     qb.setOptions( QueryBuilder::optShowAll );
     qb.sortBy( QueryBuilder::tabGenre, QueryBuilder::valName );
     return qb.run();
@@ -2401,7 +2393,7 @@
     if ( !withCompilations )
         qb.setOptions( QueryBuilder::optNoCompilations );
 
-    qb.setOptions( QueryBuilder::optRemoveDuplicates );
+    qb.groupBy( QueryBuilder::tabGenre, QueryBuilder::valName );
     qb.setOptions( QueryBuilder::optShowAll );
     qb.sortBy( QueryBuilder::tabYear, QueryBuilder::valName );
     return qb.run();