the task is to implement text search in MySQL in my project(PHP/Zend Framework 2 + MySQL). The issue is that text fields are not big at all, it is mostly VARCHAR fields or joined fields like city names, company names and so on, about 5-10 fields for each entity. So currently I decided to choose Lucene(zend framework 2 module - Zend Search), but will it be effective to use technologies like Lucene or Sphinx for small varchar fields?
Thank you.
Sure, Lucene or Sphinx can work with any varchar columns that contain text.* They don't have to be huge.
Any fulltext indexing solution is hundreds or thousands of times better than using LIKE '%word%'!
You might be interested in my presentation, Fulltext Search Throwdown.
You can also watch a recording of me delivering that presentation as a webinar: http://www.percona.com/webinars/2012-08-22-full-text-search-throwdown (it's free but requires registration).
* Lucene and Sphinx can do some things with numeric columns too.
PS: I was the project lead on Zend Framework 1.0. Zend_Search_Lucene was an interesting experiment at circa 2007, but it's way outdated, relative to Apache Lucene/Solr, and Zend_Search_Lucene is orders of magnitude slower than the Java implementation. I wouldn't bother with it.