http://www.chriscalender.com/?p=41

I’ve worked on this a bit further, and have found one tweak that will help with the query cache.

This is only a change to the 3rd line (the declaration and definition of the variable “query”).

Here is the new line of code:

local query = “/* ” .. string.gsub(proxy.connection.client["address"],”:%d+”,” “) .. “*/ ” .. string.sub(packet, 2)

The “gsub” call is what’s new.

Basically, this strips off the “:” and the trailing id. Therefore only returning the IP address. This will help a lot for the query cache.

Now, at least all queries that originate from the same server can use the query cache. Note queries that are the same, but originate from different IP addresses will result in 2 entries into the query cache (assuming it is enabled).

So instead of seeing queries like the following:

/* 127.0.0.1:11251 */ show processlist
/* 10.1.10.1:15091 */ select sleep(30)
/* 10.1.10.2:18166 */ select sleep(30)
/* 10.1.10.3:18934 */ select sleep(30)
/* 10.1.10.4:19446 */ select sleep(30)

They will now appear as:

/* 127.0.0.1 */ show processlist
/* 10.1.10.1 */ select sleep(30)
/* 10.1.10.2 */ select sleep(30)
/* 10.1.10.3 */ select sleep(30)
/* 10.1.10.4 */ select sleep(30)

please also see my little response to it
http://blogs.sun.com/kay/entry/query_cache_and_comments

In a nutshell: It totally depends on the version you are using.
Very nice, btw, and great to see you taking up blogging

SELECT * from my_table WHERE value = ‘this is /* a comment */ here’

(You really need a parser to determine this)

But the query cache doesn’t work for everyone. I use a similar technique to Chris’ tip to write either the function name or request_uri that required the query in a comment. i.e.

/* mypage.php?arg1=321312 */ SELECT * FROM my_table WHERE …;

That way this information can make my slow query log as well.