2 edb Apr 04, 2009 21:33

Hi, EdB. You are right. This is where the function EXTRACT is used, so the entire block will be now:
elseif( !empty($m) )
{ // We want to restrict on an interval:
// if $m, don't use EXTRACT, because probably negates the use of the mysql cache
// (see http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html)
$hourmin = ' 00:00:00';
$hourmax = ' 23:59:59';
$year = substr($m, 0, 4);
$date_min = $year . '-01-01' . $hourmin;
$date_max = $year . '-12-31' . $hourmax;
if( strlen($m) > 5 )
{
$month = substr($m, 4, 2);
$date_min = $year . '-' . $month . '-01' . $hourmin;
$date_max = $year . '-' . $month . '-31' . $hourmax;
}
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.'\'' );
// end
/* $this->WHERE_and( 'EXTRACT(YEAR FROM '.$this->dbprefix.'datestart)='.intval(substr($m,0,4)) );
if( strlen($m) > 5 )
$this->WHERE_and( 'EXTRACT(MONTH FROM '.$this->dbprefix.'datestart)='.intval(substr($m,4,2)) );
if( strlen($m) > 7 )
$this->WHERE_and( 'EXTRACT(DAY FROM '.$this->dbprefix.'datestart)='.intval(substr($m,6,2)) );
if( strlen($m) > 9 )
$this->WHERE_and( 'EXTRACT(HOUR FROM '.$this->dbprefix.'datestart)='.intval(substr($m,8,2)) );
if( strlen($m) > 11 )
$this->WHERE_and( 'EXTRACT(MINUTE FROM '.$this->dbprefix.'datestart)='.intval(substr($m,10,2)) );
if( strlen($m) > 13 )
$this->WHERE_and( 'EXTRACT(SECOND FROM '.$this->dbprefix.'datestart)='.intval(substr($m,12,2)) ); */
$start_is_set = true;
$stop_is_set = true;
}
}
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.
Thanks :D
Appreciate the info cuz I'd have never EVER guessed my way to something like this.
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
Is that the block you replaced with the snippet you provided?
BTW I know nothing about speeding up or slowing down servers other than "it happens". But I figure if you've got a method to make the server work less and do the same stuff then heck yeah I wants it!