MySql Full text or Sphinx or Lucene or anything el

Question

I am currently using MySql and have a few tables which i need to perform boolean search on. Given the fact my tables are Innodb type, I found out one of the better ways to do this is to use Sphinx or Lucene. I have a doubt in using these, my queries are of the following format,

Select count(*) as cnt, DATE_FORMAT(CONVERT_TZ(wrdTrk.createdOnGMTDate,'+00:00',:zone),'%Y-%m-%d') as dat from t_twitter_tracking wrdTrk  where  wrdTrk.word like (:word) and   wrdTrk.createdOnGMTDate between :stDate and :endDate group by dat;

the queries have a date field which needs to be converted to the timezone of the logged in user and then the field used to do a group by.

Now if i migrate to Sphinx/lucene will I be able to get a result similar to the query above. Am a beginner in Sphinx, which of these two should i use or is there anything better.

Actually groupby and search ' wrdTrk.word like (:word)' is a major part of my query and I need to move to boolean search to enhance user experience. My database has approximately 23652826 rows and the db is Innodb based and MySql full text search doesnt work.

Regards Roh

Answer 1

only you need to index the data properly and will got the result

Answer 2

Since you only need the counts, I believe it would be better for you to keep using MySQL. If you have a performance problem, I suggest you use explain() and possibly better indexing to improve your queries. Only if full-text search is a major part of your use-case you should move to using Sphinx/Solr.

Read Full Text Search Engine versus DBMS for a more comprehensive answer.

Answer 3

save your count in a meta table, keep it updated. or use myisam, which maintains its own count. mongodb also maintains its own count. cache the count in memcache. counting each time you need to know the count is a silly use of resources.

Answer 4

Yes. Sphinx can do this. I don't know what LIKE (:word) does, but you can do a query like @word "exactword" in sphinx search.