Lucene vs "select .. like .."

I am writing web application where users create content (posts on forum). I开发者_开发技巧s it better to use select .. like .. or Lucene? What are advantages of Lucene in not advanced searching.

---

Lucene is hugely more powerful/flexible, but a SELECT ... LIKE might be a good starting point. Finish your app with SELECT ... LIKE then you can add in Lucene if you need to, as it's a lot more work.

You might want to get your app to the point where it's sufficiently heavily used to even justify the Lucene time investment.

Short answer: SELECT ... LIKE is probably good enough to start with.

---

Leucene will likely be faster for large datasets because it can use a full text index. A select ... like query on a traditional relational database can typically only use an index if the the argument to like does not begin with a wildcard. For example:

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 do a lot of the second kind of query, it's unlikely to scale well. If you're using MySQL with the MyISAM table engine (default, but doesn't support foreign keys), you can use MySQL's full text indexing capabilities, but the syntax is different and MySQL-specific. It doesn't use the like keyword.

---

You shouldn't consider messing around with Lucene yourself. Instead, you should rather try stand-alone products like Solr or Elastic Search or libraries like Hibernate Search. Unfortunately, my personal favorite, Compass, is abandoned now. I've tried Hibernate Search a while ago and dropped it in favor of Compass. Now, it looks as if Elastic Search is probably the easiest way to provide advanced search capabilities.

Additionally, some RDBMSs support more or less advanced fulltext search capabilities, e.g. MySQL, MS SQL and Oracle.