将MySQL查询结果存储在会话或cookie中以限制数据库调用

问题描述：

I have a ingredient mysql database. I let the user save to their profile home cabinet what ingredient per category they select. I have 10 categories, so I will be making 10 queries per profile. My query is setup like so:

select I.Iname, I.Ipic, I.IID, I.ICat 
from Ingredient I 
left outer join Cabinet C on I.IID = C.IId 
where Login.LoginID='{$_COOKIE['login']}' and I.ICategory ='fruit';

-- is ran 10 times since i have 10 different categories

--for each row returned it outputs divs under the specific category header.

Now my question is there a more optimized way of doing this as to storing their cabinet data in session objects, arrays or using a cookie? Or is running 10 of these queries not that much of a performance impact. What if someone is returning 100 rows for each category? Or should I just query all categories and loop through the result to place each row in its specific area.

cabinet table :

loginID | ingredientID 
----------------------
   1    |     3 
   1    |     4

我有一个成分mysql数据库。我让用户保存到他们的个人资料家庭橱柜他们选择的每个类别的成分。我有10个类别，因此每个配置文件我将进行10次查询。我的查询设置如下： p>

 选择I.Iname，I.Ipic，I.IID，I.ICat 
来自成分I 
left外连接柜C上的I.  IID = C.IId 
其中Login.LoginID ='{$ _ COOKIE ['login']}'和I.ICategory ='fruit';  
  code>  pre> 
 
   - 因为我有10个不同的类别而被运行10次 p> 
 
 
  - 对于返回的每一行，它输出div 特定的类别标题。
  p> 
 
 
现在我的问题是有一种更优化的方法来将它们的文件柜数据存储在会话对象，数组或使用cookie吗？ 或者运行其中10个查询并没有太大的性能影响。 如果有人为每个类别返回100行怎么办？ 或者我应该只查询所有类别并循环结果以将每一行放在其特定区域。 p> 
 
 
 cabinet table： p> 
 
 
  loginID  |  ingredientID 
 ---------------------- 
 1 |  3 
 1 |  4 
  code>  pre> 
  div>

答

You have 2 possible choices:

Leave everything as is. There is nothing wrong with SQL queries per se. A database is not something necessary slow and hulky. It is just a storage, as well as a session file (which can be in turn stored in the database, so, you'll end up in the same point but make things unnecessary complicated!). Databases intended to e queried! It is the only their destination! And they are smart enough even to cache your results.
Note that storing mysql results in cookie is not an option at all, under any circumstances, it will slow your site for sure. (It seems it always needs to be clarified that there is nothing wrong with cookies as well. it is just not intended to cache data)
Implement some Model in terms of MVC pattern and make all data requests abstract. So, you will be able to implement any caching techniques later, without altering other program code.

Also note, that no performance-wise question asked on the grounds of wild guess can make sense.
See, you were about to make even worse (using cookies), out of some grounndless assumption.
Always ask yourself about performance only if there is a certain problem to solve. Otherwise you will most likely just miss the point or even do things worse.

Update
After seeing your query I can say that there is no need to do 10 queries, - one is enough. Just order it by ingredient and then add divs dynamically in PHP. (If you have no idea how to do it, better create another question, to make things do not interfere)

And of course you have to escape your $_COOKIE['login'] if you don't do it already.

$cookie = mysql_real_escape_string($_COOKIE['login']);

and use that $cookie variable in the query.

Also note that using just cookied login will make your site extremely weak in term of security - anyone will be able to login under someone else's account.

将MySQL查询结果存储在会话或cookie中以限制数据库调用

相关推荐