[Fwd: Re: 100% CPU while searching in collection browser]

[Dr. Diesel]

Yes I think you are correct, the ballooning is likely caused by the
join being too general.  Probably a number of ways to best handle this
problem.  So far simply changing it to "SELECT DISTINCT" has made
Amarok usable again!!!!  YES!!

I wonder if this could be happening on other queries?

On Sat, Mar 1, 2008 at 1:23 PM, Colin Guthrie <gmane at colin.guthr.ie> wrote:
> Dr. Diesel wrote:
>  > I made a backup of the DB and tested the above:
>  >
>  > mysql> alter ignore table images add primary key(path,deviceid);
>  > Query OK, 185659 rows affected (3.12 sec)
>  > Records: 185659  Duplicates: 178419  Warnings: 0
>  >
>  > mysql>
>  >
>  > I must be missing something here, after purging 178K records then
>  > again running this query:
>  >
>  > mysql> SELECT images.deviceid,images.path FROM images, artist, tags
>  > WHERE images.artist = artist.name AND artist.id = tags.artist AND
>  > tags.sampler = 1 AND images.album  LIKE 'Metal Blade 15th Anniversary
>  > Disc 1' ESCAPE '/'  AND images.deviceid IN (1,2,3,-1);
>  >
>  > I still get:
>  >
>  > 38068 rows in set (0.22 sec)
>  >
>  > Just as before?
>  >
>  > So far "select distinct" is the only work-a-around.  What am I missing?
>
>  Isn't it ballooning up the results because it's joining tags and images
>  only on artist name and not the ablum too?
>
>  I think you could reduce the results by including album too like:
>
>  SELECT images.deviceid,images.path FROM images, artist, album, tags
>
> WHERE images.artist = artist.name AND artist.id = tags.artist AND
>  images.album = album.name AND tags.album = album.id AND tags.sampler = 1
>
> AND images.album  LIKE 'Metal Blade 15th Anniversary Disc 1' ESCAPE '/'
>   AND images.deviceid IN (1,2,3,-1);
>
>  I'm conditions to think in JOINs tho' so I'd write this as:
>
>  SELECT images.deviceid,images.path FROM images INNER JOIN artist ON
>  images.artist=artist.name INNER JOIN album ON images.album=album.name
>  INNER JOIN tags ON artist.id=tags.artist AND album.id=tags.album WHERE
>
> tags.sampler = 1 AND images.album  LIKE 'Metal Blade 15th Anniversary
>  Disc 1' ESCAPE '/'  AND images.deviceid IN (1,2,3,-1);
>
>  Which /should/ be functionally equivilent (not sure if it works on all
>  DBs tho').
>
>  You'd still want a DISTINCT in there tho and I think this is perfectly
>  valid to use in this circumstance. It should hopefully filter out a few
>  more unnecessary results prior to the DISTINCT tho'.
>
>  PS I've not looked at the code so I may be making some invalid
>  assumptions here.
>
>  Col
>
>
>
>  _______________________________________________
>  Amarok mailing list
>  Amarok at kde.org
>  https://mail.kde.org/mailman/listinfo/amarok
>



-- 
projecthuh.com
All of my bits are free, are yours?  Fedoraproject.org