We wish to implement faceted searching, and faster search results for our system. I've researched a bit, and Solr seems to be an amazing thing, exactly what we need. But, none of us have worked with Solr before, and before we start overhauling our system to work nicely with Solr, I want to know how to migrate data from MySQL to Solr, or set it up so Solr can read the database. The catch is that the data is in many different tables.
For example, a product might have attributes, childs, pricing, tags, all in different tables. Would Solr be able to access all of these tables properly, and connect them to the right product?
Or, would we need to create a (huge) xml file in which each product is placed, with all the relevant attributes? We're at just shy of 50.000 products now, but we expect growth up to hundreds of percents in the coming two years.
Solr comes with a contrib module called DataImportHandler [1] which is able to gather data from several sources, database included.
It is highly configurable and easy to use. I suggest you to try that, as it is IMHO the simplest way for your scenario.