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.
I have created a temporary table 'tmp1' that contains all resource_ids
from the second app_id (the one whose resources I wish to retrieve). I
am then looping through all resource_ids in the main table with the
first app_id, and doing the following:
* Creating a temporary table tmp2 with a single column 'word'
populated by the words associated with resource_id in the main
table
* Selecting all distinct resource_ids from tmp1 INNER JOIN'd on tmp2
on the word field
The issues I'm running into are that (1) each resource_id cycle takes a
good amount of time, and (2) I seem to be getting either too many
resource_ids or not enough.
(1) may be something I just have to deal with. As it is, I'm planning on
running the full indexing once, and then doing incremental updates, so
it may not be that big of an issue (unless it takes too much time to
create the initial index). As for (2), unfortunately, I'm not sur ehow
to really trouble shoot the issue. I know, for instance, that in once
case, I have a list of 343 words that generates a list of ~12,000
resource_ids (of a possible 18,000) -- but I don't quite know how to
spot check 300 values to be certain that this is reasonable.
In a previous incarnation of the script, I was looping through each word
of each resource_id and then selecting out of tmp1 based on the single
word value. The results were very different (very few matches), and,
again, the script ran long.
Any ideas?
--
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@xxxxxxxxxx | http://vermontbotanical.org
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
