On Thursday 28 September 2006 17:18, Ben wrote: > explain select > distinct public.album.id > from > public.album,public.albumjoin,public.track,umdb.node > where > node.dir=2811 > and albumjoin.album = public.album.id > and public.albumjoin.track = public.track.id > and levenshtein(substring(public.track.name for 75), > substring(node.file for 75)) <= 10 > and public.album.id in > (select album from albumjoin group by album having count(*) between 15 and 25) > group by public.album.id > having count(*) >= 5; If I'm reading this right, you want all the albums with 15-25 entries in album join having 5 or more tracks that are (soundex type) similar to other nodes. Knowing that, you can also try something like this: select a.album from (select album,track from albumjoin group by album having count(1) between 15 and 25) a join public.track t on (a.track = t.id) join umdb.node n on (levenshtein(substring(t.name for 75), substring(n.file for 75)) < 9) where n.dir = 2811 group by a.album having count(1) > 4; This removes two of your tables, since you were only interested in albums with 15-25 albumjoins, and weren't actually using any album data other than the ID, which albumjoin supplies. Your subselect is now an integral part of the whole query, being treated like a temp table that only supplies album IDs with 15-25 albumjoins. From there, add on the track information, and use that to restrict the matching nodes. Your explain should be better with the above. Just remember with the levenshtein in there, you're forcing a sequence scan on the node table. Depending on how big that table is, you may not be able to truly optimize this. -- Shaun Thomas Database Administrator Leapfrog Online 807 Greenwood Street Evanston, IL 60201 Tel. 847-440-8253 Fax. 847-570-5750 www.leapfrogonline.com
