Geek asks and answers

Google-like search algorithm

I am trying to implement a search algorithm in my simple data structure. However, this is not "HOW DO I DO IT?" - the question, but rather "how can I optimize the algorithm?"

I am trying to keep a file index, and each file can be associated with any number of tags (which act as a category)

Here is how my data is structured:

Entries:

 ------------------------------------
|  id  | description | short | score | 
 ------------------------------------

Tags:

 -------------
|  id  | text |
 -------------

EntryTags:

 -------------------
| entry_id | tag_id |
 -------------------

In the search field, the search query will always turn into individual words, separated by plus (+).

in the following example, I will search for "blue + site + simple + layout"

- split searchterm up into array named t
- convert each word in array t into a number using the id from "Tags" table
- for each element in array t, select make new array for each element with "EntryTags" matching the search
- generate array A, where elements that are in all 4 arrays are put into
- generate array B, where elements that are in 3 of the 4 arrays are put into
- generate array C, where elements that are in 2 of the 4 arrays are put into
- generate array D with the last elemenets rest
- sort array A,B,C and D by the score parameter from the table
- output array A, then B, then C, then D

Of course, this is not optimized or something else, but my lack of experience with more complex SQL is a kick :(

, PHP mysqli ( , , , )

+3
2

Bloom ( , Google). . , , ... . Bloom , .

+5

Woah, (KISS), .

: SQL, , "point" " ". "" , "".

: http://www.jarrodgoddard.com/web-development/advanced-web-site-search-with-sql

SELECT title, filename, sum(relevance)
FROM (
SELECT title, filename, 10 AS relevance FROM page WHERE title like ‘%about%’
UNION
SELECT title, filename, 7 AS relevance FROM page WHERE filename like ‘%about%’
UNION
SELECT title, filename, 5 AS relevance FROM page WHERE keywords like ‘%about%’
UNION
SELECT title, filename, 2 AS relevance FROM page WHERE description like ‘%about%’
) results
GROUP BY title, filename
ORDER BY relevance desc;
-1

More articles:


All Articles