Got a little problem here. Have two tables:

| id | word | count |

and

| wordid | locationid |

Basically this is for running a search engine. Now the words are added to the first table and then if the word exists in entry 1, for example, then a line is added to the second table.

My problem is that for search engine relevancy results I need to know the number of times a word appears. E.g. I want to be able to know, with a figure, that the word "the" appears very often (high percentage) and that the word "php" appears less often.

This could be done during searching by doing a count but I believe i would need to do a count for every single word, so a 6 word search is going to need 6 counts - an idea I don't like.

What would be thus better is to store the % in a count column. The best would be to store the percentage directly e.g. 0.5 for 1/200 words being that one.

If I do that my next problem is updating it and that is what I am really asking here. I can see two solutions, one is to only update it every day for example using a cron script and the other is to update the whole thing every time an article is added. I don't think the latter is going to work really, being too server intensive as every single word record would have to be updated.

My only other thought is that when adding a record to the second table, the first table has a count query done on it. I guess a query such as:

UPDATE words SET count = count+1 WHERE word IN (all the words). Thing is I am not sure how that could be easily adpated so that if the same word appears twice is gets the number increase twice. Clearly if I knew the number of times one word appears it would be easy to get a percent.

Any one with some ideas on this?