我应该如何查询MySQL和如何缓存MySQL的结果？ [英] How should I query MySQL and how to cache the results from MySQL?

问题描述

我终于得到了Solr的工作，在所有的人的帮助下，谢谢你！
我现在明白你为什么推荐它了，它真的很强大。

现在，问题...

我已将所有可搜索信息索引到 Solr ，我的计划是查询Solr，然后获取<$ c $ （所有记录，即每个都有一个ID字段值）的查询结果，然后我用于查询MySQL c>并获取剩余的信息。
所以，首先查询Solr，下一个solr发送回id：s给所有匹配查询的广告，然后使用ID：s查询MySQL的剩余信息。

我的Q是，当使用MySql时，我应该将所有收到的ID保存到数组，然后 query mysql 找到具有这些ID的所有记录：s？
我应该这样做吗？ （可能包含一些代码错误）：

  for（$ i = 0; $ i< $ id_from_solr.length; $ i ++） {
 mysql_query（SELECT * FROM table_name WHERE ad_id = $ id_from_solr [$ i]）; 
} 
  

上面似乎不因为它每次重新循环时都会执行一个新查询。

如何做？

strong>后续问题：
与使用排序函数的WITHOUT相比，在MySQL查询中添加排序函数的速度会更慢吗？例如：

  ORDER BY insert_date ASC 
  

最后Q：
是否还有缓存MySQL结果，所以在排序时，我不必进行新的查询？

感谢alot！

如果你需要更多的输入，让我知道，我会更新这个Q！

解决方案

问题1 （检索ID，然后查询数据库）：为什么不从Solr返回一些字段

Q2 （性能和排序）：好的，排序代表一个额外的任务来执行你的数据，所以它必须为数据库添加一些工作：这当然可以最小化，如果你有一个索引 ORDER BY

Q3 （捕获MySql查询）：您可以打开MySql缓存（这将返回缓存的副本如果请求与前一个匹配，假定数据在过渡期间没有更改，则使用缓存层，例如EhCache：

http://ehcache.org/

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!

解决方案

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/

这篇关于我应该如何查询MySQL和如何缓存MySQL的结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！