May 3, 2012

Search recent data with mysql, Full text search old data with solr

Question by paganotti

I build a web application in php and mysql. I want to search data doing full text search on 3-4 table. I’m considering for it lucene solr project.

My question is:
Is it correct save today data in separate mysql table, then and it make a simple search only for that today data and if it find enything, it try query on lucene for full text search but in old data( from yesterday to past) ?

My web app save data in mysql but I want that user can full text search on that data. I can run a job that 4 times for day import data from msql to lucene solr, but if data on mysql change by user and solr doesn’t already start importing and indexing data, user should be able in any case to search and see recent modified data.

What is the best approach for integrate lucene solr with mysql and php?

Is it possible send a modify command to solr ftom php for update data that user changed but be sure to commit data on solr only if a mysql
transaction go well done, so finaly i have commited data in mysql and commited data in lucene and if something goes wrong on web application rollback all as in mysql as in solr?

Answer by Starx

If you are thinking of implementing Lucene, the number of table does not matter. As per for the best approach, I would say to implement Zend’s Zend Search Lucene.

The lucene library like Zend Search Lucene have there own mechanism of tokenizing the data and search through them. They are basically fed with data from the database, from which they will build the search files and store them separately, if something goes wrong, then just rebuild the search.

About the possibility of update the Lucene data when the database gets updated, relies completely on what solution you implement. Zend Search Lucene on other hand, can do it.

...

Please fill the form - I will response as fast as I can!