Most efficient way to combine Solr results with MySQL data
On our new site (shopping site) we will be using Solr for our site search engine. In the Solr index, we keep a list of product IDs and a list of keywords for each product. The search query is performed by keywords.
Solr returns a list of product IDs. These IDs are then inserted into a MySQL query to fetch all product data from the database. MySQL also handles sorting results. For example, a MySQL query might look like this:
SELECT * FROM product WHERE id IN (1,4,42,32,46,...,39482) ORDER BY price ASC
We have about 100,000 products on our site. This method works great when there are several thousand results, but becomes slow when there are - for example, 50,000 results.
My guess is that the bottleneck is the WHERE IN clause. A long-term solution would be to move all product data to Solr so that it can handle sorting of results and also applying cleanup filters to the search (for example, the user might want to see products only in a certain price range). However, we are inexperienced with Solr and need a short term fix before we can implement this.
One option is to ditch Solr in the short term and store the keywords in a table in MySQL and search against that with a FULL-TEXT search.
Am I missing other parameters?
source to share
The main problem for you is that Solr is going to return results sorted by the number of matching keywords, but you want the results to be sorted by price. As you rightly say, moving all your data to Solr is the best option - you would be very happy with Solr for searching, sorting, faceting and pagination.
However, in the short term, it's worth adding a field price
to Solr. When you get a search query like tooth paste
you can issue a Solr query like
q=keywords:(tooth AND paste)&rows=10&fl=id&sort=price%20asc
to get only the first 10 results, and then do the pagination by specifying a parameter start
, so:
q=keywords:(tooth AND paste)&rows=10&start=10&fl=id&sort=price%20asc
source to share