Searching Large Database

[Anon]

I've been running a search engine for a while that has become slow recently, since there's more traffic, and more entries in the database.

I've thought about creating an index to return a faster search... but a good thing about the current system is that it also checks for sucession of words. Ie if a user enters 4 words, entries that have several of those words together show up higher than just the words individually.

Is there a way I can have the best of both worlds? My current system of going through a live database is too slow, yet a word index looses accuracy. Keep in mind the search is going through 60 thousand records.

Thanks

[Anon]

I've never done this, and for all I know it could bring your app to a screeching halt, but...

You could try creating an 'index' table that has:

1) The word
2) The word's position in the document
3) The URI to the document

Index 1 and 2. Then, do (something like):

SELECT STDEV(position) as prox, uri FROM INDEX_TABLE WHERE word IN ('word1','word2','word3') GROUP BY uri, prox ORDER BY prox

The smaller the standard deviation, the closer the words are together. I don't think the above code will work as-is, but you could give it a try.

--Ernest