On our new site (a shopping site), we will use Solr for our site's search engine. In the Solr index we keep a list of product id's, and a list of keywords for each product. The search query is done against the keywords.
Solr returns a list of product id's. These id's are then inserted into a MySQL query to select all product data from the database. MySQL also handles the sorting of results. E.g., the MySQL query might look like:
SELECT * FROM product WHERE id IN (1,4,42,32,46,...,39482) ORDER BY price ASC
We have around 100,000 products on the site. This method works fine when there are a couple of thousand of results, but becomes slow when there are - for example - 50,000 results.
My assumption is that the bottleneck is the "WHERE IN" clause. A long-term solution will be to move all product data to Solr so it can handle sorting the results and also applying refine filters to the search (e.g., perhaps the user only wants to view products 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 abandon Solr in the short-term and store keywords in a table in MySQL and do the search against this using a FULL-TEXT search.
Am I missing any other options?
VIEW?