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

[Colin Guthrie]

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