duanjuhuo8772
2014-10-21 12:07
浏览 31
已采纳

PHP,MySQL:是否可以缓存子查询?

I am working on a page-hit counter. The owner of a page will place on the page a little counter image with a link to my script in a web server; when somebody opens the page in their browser, my script will get invoked and generate an image of the hit number to be sent back to the browser.

From $_SERVER['HTTP_REFERER'] I get the page URL in order to identify the page ID: $page_id, and from $_SERVER['REMOTE_ADDR'] — viewer's host's IP: $ip, since I count only unique IPs. These data are collected in table Hits(page_id, ip).

In order to count the previous hits, I count the number of stored IPs for the given page:

$res1 = mysqli_query($DB, "SELECT ip FROM Hits WHERE page_id='$page_id'");
$number_of_hits = mysqli_num_rows($res1);

Now I need to test whether the newly arrived IP is not listed among the old ones, otherwise I would discard it and wouldn't increment the counter. I can either run a loop through the rows already found by the query, or issue another query:

$res2 = mysqli_query($DB, "SELECT ip FROM Hits WHERE page_id='$page_id' AND ip='$ip'");
$number_of_new_hit_occasions = mysqli_num_rows($res2);
if($number_of_new_hit_occasions == 0) ++$number_of_hits;

But the second query is essentially redundant and as resource-consuming, as the first one; it would be much more efficient just to search through the small result obtained in the first query, than through the whole table in the second one.

I wish I could store the first query in a virtual table in the memory named, say, IPs_for_given_page (maybe somehow referred in $res1), and reduce the second query to

$res2 = mysqli_query($DB, "SELECT ip FROM IPs_for_given_page WHERE ip='$ip'");
$number_of_new_hit_occasions = mysqli_num_rows($res2);

Is it possible in any way?

图片转代码服务由CSDN问答提供 功能建议

我正在使用页面点击计数器。 页面的所有者将在页面上放置一个小的计数器图像,其中包含指向Web服务器中脚本的链接; 当有人在他们的浏览器中打开页面时,我的脚本将被调用并生成要发送回浏览器的命中号码的图像。

来自 $ _ SERVER ['HTTP_REFERER '] 我获取页面网址以识别网页ID: $ page_id ,以及 $ _ SERVER ['REMOTE_ADDR'] - 查看者主机的IP: $ ip ,因为我只计算唯一的IP。 这些数据收集在表 Hits(page_id,ip)中。

为了计算之前的命中数,我计算给定页面存储的IP数量 :

  $ res1 = mysqli_query($ DB,“SELECT ip FROM Hits WHERE page_id ='$ page_id'”); 
 $ number_of_hits = mysqli_num_rows($ res1); \  n   
 
 

现在我需要测试新到的IP是否未列在旧的IP中,否则我会丢弃它并且不会增加计数器。 我可以通过查询已找到的行运行循环,或者发出另一个查询:

  $ res2 = mysqli_query($ DB,“SELECT ip FROM Hits WHERE page_id =  '$ page_id'AND ip ='$ ip'“); 
 $ number_of_new_hit_occasions = mysqli_num_rows($ res2); 
if($ number_of_new_hit_occasions == 0)++ $ number_of_hits; 
   \  n 
 

但第二个查询本质上是多余的,并且与第一个查询一样耗费资源; 搜索第一个查询中获得的小结果比通过第二个查询中的整个表搜索效率要高得多。

我希望我可以将第一个查询存储在第二个查询中。 内存中的虚拟表,例如 IPs_for_given_page (可能以某种方式在 $ res1 中引用),并将第二个查询减少到

   $ res2 = mysqli_query($ DB,“SELECT ip FROM IPs_for_given_page WHERE ip ='$ ip'”); 
 $ number_of_new_hit_occasions = mysqli_num_rows($ res2); 
   
 \  n 

有可能吗?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • doutang1873 2014-10-21 12:42
    已采纳

    There's no way to cache results as you mention. But there is a more efficient way to do what you're trying to do.

    To count rows, use this kind of query and retrieve the one-row result set. This is much faster than counting rows in a resultset.

    SELECT COUNT(*) FROM Hits WHERE page_id=<<whatever>>
    

    You can automate the deduplication of ip numbers by doing two things:

    First, create a compound unique index on (page_id,ip).

    Second, use this SQL statement to insert the hit row.

    INSERT IGNORE INTO Hits (page_id, ip) VALUES (<<whatever>>, <<whatever>>) 
    

    The unique index combined with the IGNORE clause together prevent MySQL from accepting duplicate hit rows for the same ip address.

    For what it's worth, the compound index I've suggested will also accelerate your COUNT query.

    点赞 打赏 评论

相关推荐 更多相似问题