0

I need help in fulltext search in mysql. I am looking for book named dreams. when i search for dreams i got

  1. India in my dreams the greatest of all dreams vision
  2. Dictionary of dreams - understanding dreams
  3. Psyche on dreams & beyond dreams
  4. Without dreams
  5. Only dreams
  6. Dreams
  7. some other books

Actually i am looking for dreams How to make 6th book to first one.

I want to ignore repeated words in this case dreams.

you can search here

I am facing these type of issues in different titles.

VMAtm
  • 27,943
  • 17
  • 79
  • 125
sankar.suda
  • 1,097
  • 3
  • 13
  • 26

4 Answers4

0

Probably not relevant anymore, but if anyone comes looking I would reccommend using the levenshtein function on top to solve this. See below for more information and a full implementation in mysql

https://dba.stackexchange.com/questions/40450/mysql-full-text-search-increase-relevance-for-exact-matches

Levenshtein: MySQL + PHP

mils
  • 1,878
  • 2
  • 21
  • 42
0

The relevance of columns with the search word repeated are the higest, The only solution I can see is to do a union query for exact matches with your standard full text search e.g:

SELECT * FROM books 
WHERE title = 'Dreams'
UNION
SELECT * FROM books
WHERE MATCH(title) AGAINST("Dreams")

this will show the exact match first.

or you could

SELECT * FROM books
WHERE MATCH(title) AGAINST("Dreams")
ORDER BY (CASE WHEN title = 'Dreams' THEN 1 ELSE 0 END),.....
Kevin Burton
  • 11,676
  • 2
  • 24
  • 37
  • Hi, Thank you for answers. It will helpful. the problem is performance. we have 10 million books. I tried union previously.. it is taking long time to perform search.. is there any alternative in single query.. – sankar.suda Sep 14 '11 at 11:08
  • to speed up the equality check in the union you could add a standard index on title, the case when solution would probably be your best bet though. – Kevin Burton Sep 14 '11 at 11:51
0

Do you want the exact case to be the first suggestion?

Try in order by:

"ORDER BY ('{$str_search}' = title) DESC"

This will make the exact case searchword you searched for appear first.

2 more solutions:

"ORDER BY ('{$str_search}' LIKE title) DESC"
"ORDER BY ('{$str_search}%' LIKE title) DESC"

3rd solution can be used to order up all that starts on your search word.

Note: This solution is pretty much the same as the CASE WHEN one.

Robin Castlin
  • 10,956
  • 1
  • 28
  • 44
-1

I can only suggest the usage of regular expressions : http://dev.mysql.com/doc/refman/5.1/en/regexp.html

touffy
  • 147
  • 2