I have managed finally to get Solr working, with the help of all you guys, so THANK YOU!
And I have to say, I now understand why you recommended it, it's really powerful.
Now, to the issue...
I have indexed all "Searchable" information into Solr, and my plan is to query Solr, and then get the ID:s of the query-results (of all records that is, each has an ID field value), which I then use to query MySQL and fetch the rest of the information.
So, first query Solr, next solr sends back id:s for all ads which match the query, then I use the ID:s to query MySQL for the rest of the info.
My Q is, When doing the part with MySql, should I save all the received ID:s into an array, and then query mysql to find all records with those ID:s?
Should I do something like this? (might contain some code errors):
for ($i=0; $i
mysql_query("SELECT * FROM table_name WHERE ad_id=$id_from_solr[$i]");
}
The above seems not like a good solution, because it does a "new query" everytime it loops again!
How would you do it?
Follow-up Question:
Would adding a sort function into the MySQL query slow things down compared to WITHOUT using the sort function? for example:
ORDER BY insert_date ASC
And last Q:
Is there anyway to cache MySQL results so when sorting, I don't have to make a new query?
Thanks alot!
If you need more input, let me know and I will update this Q!
解决方案
Question 1 (retrieving IDs and then querying the database): why not return some of your fields from the Solr query so that you don't always have to hit the database as well?
Q2 (Performance and sorting): well, sorting represents an extra task to perform on your data, so it is bound to add a bit of work for the database: this can of course be minimized if you have an index on your ORDER BY column(s).
Q3 (catching MySql queries): you can either turn on the MySql cache (which will return a cached copy of your results if the request matched a previous one, assuming the data has not been changed in the interim), or use a caching layer outside of the database, such as EhCache: