We (lahaine.org, b2evo 2.4.6) were getting this error, specially when we used the archives filter in items, in order to modify an old post.

The error appeared always in ItemList2::count_total_rows(), and investigating we found two things: first looking at ulimit -a there was no problem with the mysql cache. Then, this article: http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html, which says "Many functions, such as CURRENT_DATE, NOW, RAND and others, negate the use of the cache."

We looked at _itemquery.class.php to see what happens when using archive filter, and found that if( !empty($m) ), meaning that a date was selected, the code make use of the function EXTRACT (year, month, etc.) in the query. We modified the code going back to the old good

etc., and it worked. The error is gone. So we arrive to the conclusion (even if we couldn't find any source in mysql.com to confirm it) that EXTRACT also negates the use of the mysql cache, and it wouldn't be used. But in b2evo 3.1 still is, maybe the developers can take a look at it...

Hi Fede. Could you identify the file and lines you started with and ended with? I looked at /inc/items/model/_itemquery.class.php and found one "!empty($m)" only once, but in an elseif - not a good old fashioned if. In other words I found

Our solution is not very elegant, but effective. So, if it's true that EXTRACT negates the use of the mysql cache (and it seems so, as now the error is gone), when you don't use this function the mysql server is working less, because it uses the cache. It's important for us, as we have about 40.000 posts. It's all we know about speeding up or slowing down servers.