Thanks for your reply, and sorry for the delay on my end (I work Wednesday - Sunday).
I delved into this further and discovered that the cause of the problem is not exactly what I thought it was. Long story short, Favorites doesn’t have a query caching related bug, at least not in terms of what the query caching feature is acutally doing, as opposed to what it might be expected to do (it seems broken to me).
To make a really basic example of the problem I did a fresh install of EE 1.5.2 / Favorites 2.1.4 and set up a few templates to implement Favorites. I tested it and realized I wasn’t having the same problem as on my production site.
It turns out the problem I was having is a result of a customization I made. I wanted to combine displaying favorites and deleting favorites on the same page, so that a user could view their favorites, and click a “delete this favorite” link that would reload the page and display the confirmation message and updated list of favorites. (This page is only meant to show a logged in user their favorites, not to allow users to view other users’ public favorites.) Like this:
{exp:favorites:save}
{exp:favorites:entries}
...
{/exp:favorites:entries}
Since Favorites is hardwired to interpret the request URI in specific, incompatible ways in relation to the {exp:favorites:save} and {exp:favorites:entries} tags, that wouldn’t work without taking special steps. So, I did the following:
{exp:favorites:save}
{exp:my_plugin:fix_uri}
{exp:favorites:entries}
...
{/exp:favorites:entries}
When the user deletes a favorite, the request URI is in the format /members/favorites/123/delete. {exp:favorites:entries} would try to look up the favorites for a member with member_id == 123. So, my_plugin::fix_uri() changes $IN->URI to /members/favorites to get {exp:favorites:entries} back on track.
The reason that causes me a problem with the SQL query caching feature is that when I change $IN->URI, that changes the cache directory from that point forward. Why would that make a difference? Good question. The only reason Favorites doesn’t normally have a problem with the query caching feature is that, as far as I can tell, the query caching feature is either totally broken (if it’s not working as intended) or virtually useless (if it is working as intended).
Let’s say query caching is enabled, there are no query cache files, and you view the page that uses {exp:favorites:entries}. That will create a directory, the name of which is based on the request URI, and a file in that directory for the SELECT query executed by {exp:favorites:entries}. My expectation of how the query caching feature would work, based on what’s documented about it, and common sense, is that the next time you view that page and {exp:favorites:entries} executes its SELECT query, the results would be retrieved from the cache file instead of the database. But that doesn’t happen.
Dumping data at strategic points in system/db/db.mysql.php reveals that on each request EE destroys all of the cache files associated with the current URI before the template is ever processed. On line 540 of system/core/core.system.php (in EE 1.6.2), an object of class Session is instantiated, which results in either an INSERT or UPDATE query being executed. The execution of any INSERT or UPDATE query, among others (not including SELECT), results in all query cache files associated with the current URI being destroyed.
That means that the only time data would be retrieved from the query cache files for any query executed as a result of anything in a template would be if that same query is executed later in the same request (and without an INSERT, UPDATE, or other “write\” query taking place in between).
So the reason there’s not a query caching problem with Favorites normally is that by the time {exp:favorites:entries} executes, all cache files that may have been stored in connection with the current URI have been destroyed. The cause of my problem was that when I just viewed the favorites (e.g. /members/favorites), EE created a query cache directory named based on that URI and stored the results of the {exp:favorites:entires} SELECT query there, then when I deleted a favorite (/members/favorites/123/delete) EE deleted all of the query cache files associated with that current URI at the beginning of the request, but not the ones associated with the first URI (/members/favorites). Right before {exp:favorites:entries}, I changed $IN->URI to /members/favorites, so EE retrieved the results of the SELECT query from the cache directory created for /members/favorites.
So sorry for the false alarm about Favorites. Based on the behavior I observed, and the Favorites source code, it seemed pretty straightforward what was happening, but I wasn’t aware of the issues with the query caching feature at the time.
I’ve attached a file showing my debugging output from system/db/db.mysql.php. This is from an otherwise fresh install of EE 1.6.2 / Favorites 2.1.4. In this case the directory created for query cache files was b2d594ed54650ca5f2d41718504f0835 and the file created for the SELECT query in {exp:favorites:entries} is 00c00932267355edd05914ea8ce63954. Each piece of debugging data is separated by a horizontal rule and shows what method is being executed. The file_exists = TRUE | FALSE line is showing whether the cache file for the {exp:favorites:entries} SELECT query exists. You can see that it exists at the beginning of the script. Then pretty early on there’s an UPDATE query on exp_sessions, at which point all of the query cache files associated with the current URI are deleted.
Searching the page for exp_favorites shows where the SELECT query executed by {exp:favorites:entries} occurs and that at that point the cache file for that query doesn’t exist, then it exists again after that query. How that feature is helping performance is a mystery to me.
