My b2evolution Version: 2.x
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
if( strlen($m) > 7 )
$day = substr($m, 6, 2);
$date_min = $year . '-' . $month . '-' . $day . $hourmin;
$date_max = $year . '-' . $month . '-' . $day . $hourmax;
$this->WHERE_and( $this->dbprefix.'datestart <= \''. $date_max.'\'' );
$this->WHERE_and( $this->dbprefix.'datestart >= \''. $date_min.'\'' );
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...