PHP，MySQL：是否可以缓存子查询？

Question

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?

Answer 1

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.