Re: Join on single table

* Martin Norland <martin.norland@stjude.org>: > Matthew Weier O'Phinney wrote: > > I have a table which contains the following: > > id (primary key, auto incrementing) > > app_id (integer, foreign key) > > resource_id (integer, foreign key) > > word > > score > > > > (This is a search index.) I want to find all resource_ids from one app_id > > that match resource_ids in another app_id by word. > > Is this at or near what you're looking for? I don't see any need for > killing yourself making the temporary tables/etc. - but maybe I'm > missing something. (obviously replace both 'tablename's with your tables > name) > > SELECT b.app_id, b.resource_id, b.word, b.score FROM tablename AS a, > tablename AS b WHERE a.app_id != b.app_id AND a.word = b.word; I'd tried a similar query already. The issue with this particular approach is that the query takes forever to execute, even with good indexing. The temporary table approach speeds things up tremendously -- but it's still slower than I'd like. I finally hit on a solution last night, and did finally end up with a good index -- took several hours to run, but from now on I only have to do deltas. Basically, I do the following: * foreach resource_id in a single app_id, put a list of words into a temporary table * select resource_ids from another app_id by joining on the temporary table -- Matthew Weier O'Phinney | WEBSITES: Webmaster and IT Specialist | http://www.garden.org National Gardening Association | http://www.kidsgardening.com 802-863-5251 x156 | http://nationalgardenmonth.org mailto:matthew@garden.org | http://vermontbotanical.org