如何制作“频繁搜索”引擎？

Question

My first idea was start storing every word in the database ignoring words with 2 or fewer characters and every time a word is repeated just add one to a counter row (say importance) to appear first on the list of frequent searches. Sounds good until you think that normally you search for various words not just one word, eg.: for big house, you may want to store big house as a frequent search, not big and house.

I'm a little bit confused about how to do this and do it right. Has anyone done something similar? What do you think about the right way to do this?

Answer 1

Hmm, I would create 2 tables

Searches and SearchFrequency

Searches would contain all searches, and search frequency would be a list of searches that has been repeated so it would look like so

------------------------------------------------------
frequency_id     frequency_sid     frequency_counter
------------------------------------------------------
1                3                 33
2                56                66
3                33                128
.....

Then you can do

SELECT * FROM Searches,SearchFrequency WHERE search_id = frequency_sid ORDER BY frequency_counter DESC LIMIT 30

and just update the tables so,

id = INSERT INTO Searches ....
INSERT INTO SearchFrequency (frequency_sid,frequency_counter) VALUES (id,frequency_counter + 1);

This would keep both tables updated and you can also then track individal searchs with IP,Related Searches etc etc.

You can then also set up a SearchKeywordsFrequency table so that you can explode the searches and store the individual words, and then create a many-to-many relation ship with SearchFrequency