我应该如何查询MySQL以及如何缓存MySQL的结果？

Question

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<$id_from_solr.length; $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!

Answer 1

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:

http://ehcache.org/