How Should I Query Mysql And How To Cache The Results From Mysql?

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 t

Solution 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/

Solution 2:

For the first part, here's how I would write a single query statement:

$instr=implode(", ",$id_from_solr);
$stmt = "SELECT * FROM table_name WHERE ad_id IN (".$instr.")";