Use SOLR on Mysql relational database. Use SOLR for only product table

Question

Hi,

I am a bit confused as to where SOLR usage ends and where it begins.

I use php with a relational mysql db for a shopping site where all tables are related to the product table joining the tables as theyre queried. Needless to say its too slow!

e.g.

• Category table - catid, catname, catdesc
• Brand table - brandid, brandname, branddesc
• Product table - productid, productname, productdesc, catid, brandid
• (I also use ranges for price ranges etc)

I am wondering whether I should use SOLR to index the whole relational schema or whether just to index the product table alone and let my application work as it currently does.

If I just switch the product table to use SOLR are there any caveats to this?

e.g. in mysql I can do a fulltext search while joining the brand table. This will allow brands to also be searched upon. Is it possible to achieve the same thing just by switching the product table to SOLR? Are there any other caveats I should be looking out for.

I also would like to create a new table for "searches". This would allow me to use keywords in a mysql table in the following way:

Searches table - searchterm (e.g. lipstick), synonyms (e.g. lipstick, lips etc.)

ie. this would allow me to search upon multiple terms at the same time - a good time to use SOLR facets maybe instead of storing searches in mysql?, or should I just use mysql to store the searches and pull the products from SOLR?

Any help is gladly appreciated

Answer 1

NO NEED TO SWITCH

You don't want to "switch" -- just like using full-text indexing in MySQL (or using something like Sphinx), the full-text index is separate from the database tables.

What you want to do is figure out what you're searching for and index that in Solr -- it may well be just products. That's certainly an easy first step.

Basically you'll:

• index the appropriate column(s) into Solr
• use Solr for the searching
• use the Solr results to point back to the records from the database

I'm more Ruby and Java than PHP, but you'll basically be talking to Solr for the full-text search and using that to find the records you want to display.