0

I need to implement a search option for user comments that are stored in a MySQL database. I would optimally like it to work in a similar manner to a standard web page search engine, but I am trying to avoid the large scale solutions. I'd like to just get a feel for the queries that would give me decent results. Any suggestions? Thanks.

Improve this question
3
  • 4
    Take a look at the docs: dev.mysql.com/doc/refman/5.0/en/fulltext-search.html Commented May 17, 2011 at 13:00
  • 1
    That's not really the kind of thing SQL is build for. Commented May 17, 2011 at 13:01
  • Granted, I didn't use a question mark anywhere in my text. The question is: Does anyone have any suggestions as to how I might go about accomplishing this? Commented May 17, 2011 at 13:16

2 Answers 2

Reset to default
5

It's possible to create a full indexing solution with some straightforward steps. You could create a table that maps words to each post, then when you search for some words find all posts that match.

Here's a short algorithm:

  1. When a comment is posted, convert the string to lowercase and split it into words (split on spaces, and optionally dashes/punctuation).
  2. In a "words" table store each word with an ID, if it's not already in the table. (Here you might wish to ignore common words like 'the' or 'for'.)
  3. In an "indexedwords" table map the IDs of the words you just inserted to the post ID of the comment (or article if that is what you want to return).
  4. When searching, split the search term on words and find all posts that contain each of the words. (Again here you might want to ignore common words.)
  5. Order the results by number of occurrences. If the results must contain all the words you'd need to find the union of your different arrays of posts.
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

As an entry point, you can use MySQL LIKE queries.

For example if you have a table 'comments' with a column named 'comment', and you want to find all comments that contain the word 'red', use:

SELECT comment FROM comments WHERE comment LIKE '% red %';

Please note that fulltext searches can be slow, so if your database is very large or if you run this query a lot, you will want to find an optimized solution, such as Sphinx (http://sphinxsearch.com).

Improve this answer

6 Comments

I'm not really worried about speed or efficiency, but I would like to be able to also take a phrase into consideration, such as " netbook for Eluna trip ". But that's where I get a little lost. I could explode the string and run a LIKE on each term, but I probably don't want results for each instance of the word "for". How is this typically handled?
If you want to search for the entire phrase, encapsulate it in a single LIKE clause: SELECT comment FROM comments WHERE comment LIKE '% netbook for Eluna trip %';
But what if they misspell a word in the phrase, such as "netbok", does that mean no results are returned?
Matches will be precise, so mispelled words will match only comments with the same mispelling. The process of implementing a "Did you mean..." feature (ala Google Search) is extremely complex, and you should probably start by relying on a third-party tool to provide such a service. If you choose to roll your own, you will need to create a database of words associated with their common mispellings, and reference that dictionary with each search.
I think after researching the suggestions, fulltext search is the solution I was looking for.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.