Lucene vs "select .. like .."

Question

I am writing a web application where users create content (forum posts). Is it better to use select .. like .. or Lucene? What are the benefits of Lucene in an unprocessed search.

Answer 1

You should not think about communicating with Lucene yourself. Instead, you should try standalone products like Solr or Elastic Search, or libraries like Hibernate Search . Unfortunately, my personal favorite, Compass , is now abandoned. I tried Hibernate Search a while ago and dropped it in favor of Compass. Now it looks like Elastic Search is probably the easiest way to provide advanced search capabilities.

In addition, some RDBMSs support more or less advanced full-text search capabilities, for example. MySQL, MS SQL and Oracle.

Answer 2

Lucene is extremely powerful / flexible, but SELECT ... LIKE might be a good starting point. End your application with SELECT ... LIKE, then you can add to Lucene if you need, as that works a lot more.

You might want your application to go so far as to be used enough to justify Lucene's time investment.

Short answer: SELECT ... LIKE is probably good enough for a start.

Answer 3

Leucene is likely to be faster for large datasets as it can use the full text index. A select ... like query in a traditional relational database can usually only use an index if the like argument does not start with a wildcard. For instance:

 select * from mytable where mycolumn like 'fred%'; -- may use an index on mycolumn select * from mytable where mycolumn like '%fred%'; -- cannot use an index on mycolumn 

If you need to make a lot of the second request, it is unlikely to scale well. If you use MySQL with the MyISAM table engine (by default, but do not support foreign keys), you can use the full-text indexing capabilities of MySQL , but the syntax is different and depends on MySQL. It does not use the like keyword.