Question:
MySQL fulltext search vs. Lucene?
Asked by:
Which one is better to use : mysql fulltext search or lucene indexing and searching?
Share your experience.
Share your experience.
Answer by:
Well,
on one hand using MySQL fulltext search requires you to define a table column as text or varchar and create index on it as a fulltext. Words in this columns are indexed if they are over certain length. Well this is not very good because if you want to change this you should restart mysql server with option:
--ft_min_word_len=# The minimum length of the word to be included in a
FULLTEXT index. Note: FULLTEXT indexes must be rebuilt
One benefit of using mysql fulltext is that all complex logic on indexing is hidden inside database. Mysql fulltext is much slower than lucene. You need to use MATCH AGAINST SQL queries.
Lucene is java framework, it uses implementation of index storage. It is complex to use, especially if you want to implement your own index storage in database. Whenever you add a new document to the index, a new object of the index has to be created to include the new document in the lucene index. But creation of a new object is not a major overhead. Though it sometimes does slow down the process of searching.
Lucene has some advanced options like:
-proximity search - find documents where there is one word between searchword1 and searchword2
-wildcard search - find documents which have word like searchword* or maybe search?word etc etc...
-fuzzy and similarity searches - find documents with words sounding similar to roam~ (will look for roam, foam etc...)
-Term boosting - you can boost a term to move relevant documents to the top. So for example, you can say that you want documents with word "lucene" to be more relevant than those with word "mysql". Then you can do something like -> lucene^4 mysql .
In MySql you can easily join fulltext search with other search criteria in WHERE clause. In Lucene, using additional search conditions requires your additional fields to be indexed also.
Sorting of results is very fast with lucene. Well, on mysql, if you expect your results to come out fast, you will have to forget sorting. Lucene have additional sorting mechanism that allows you to use external comparator (where you can make joins with your database tables), but you have to be lucene expert.
In lucene you can write your own analyzer of text. Well on mysql you will have to use native one.
on one hand using MySQL fulltext search requires you to define a table column as text or varchar and create index on it as a fulltext. Words in this columns are indexed if they are over certain length. Well this is not very good because if you want to change this you should restart mysql server with option:
--ft_min_word_len=# The minimum length of the word to be included in a
FULLTEXT index. Note: FULLTEXT indexes must be rebuilt
One benefit of using mysql fulltext is that all complex logic on indexing is hidden inside database. Mysql fulltext is much slower than lucene. You need to use MATCH AGAINST SQL queries.
Lucene is java framework, it uses implementation of index storage. It is complex to use, especially if you want to implement your own index storage in database. Whenever you add a new document to the index, a new object of the index has to be created to include the new document in the lucene index. But creation of a new object is not a major overhead. Though it sometimes does slow down the process of searching.
Lucene has some advanced options like:
-proximity search - find documents where there is one word between searchword1 and searchword2
-wildcard search - find documents which have word like searchword* or maybe search?word etc etc...
-fuzzy and similarity searches - find documents with words sounding similar to roam~ (will look for roam, foam etc...)
-Term boosting - you can boost a term to move relevant documents to the top. So for example, you can say that you want documents with word "lucene" to be more relevant than those with word "mysql". Then you can do something like -> lucene^4 mysql .
In MySql you can easily join fulltext search with other search criteria in WHERE clause. In Lucene, using additional search conditions requires your additional fields to be indexed also.
Sorting of results is very fast with lucene. Well, on mysql, if you expect your results to come out fast, you will have to forget sorting. Lucene have additional sorting mechanism that allows you to use external comparator (where you can make joins with your database tables), but you have to be lucene expert.
In lucene you can write your own analyzer of text. Well on mysql you will have to use native one.
Vote:
Please vote! Your opinion matters!
If you haven't found what you've looking for, post a question
