Support » Developing with WordPress » Bug in $wpdb->get_var() COUNT(*) LIMIT ?

  • Executing this code:

    
    <php
    	$query = "SELECT COUNT(*) FROM " . $wpdb->options . " LIMIT 7";
    	echo 'Q = ' . $query . ', result = ' . $wpdb->get_var( $query ) . '<br />';
    

    prints the total number of entries in the options db table, while i expect 7

    Is this a bug or a feature?

Viewing 8 replies - 1 through 8 (of 8 total)
  • It’s not a bug or a feature. It’s the correct behaviour and you’d get the same result outside $wpdb. LIMIT 7 limits the result to 7 rows. You’re only getting 1: the number of options there are.

    Thanx for your reply.

    I need to know how many rows a certain (possibly complex) query will return, before actually query them. In case < 5000 i can internally cache them, else i need the id’s only to fetch them later. This is a tradeoff between memory usage and number of queries.

    I expected that in all cases $wpdb->get_var( "SLECT COUNT(*) ... LIMIT ...) would return the same value as count( $wpdb->get_col( "SELECT id ... LIMIT ..., ARRAY_A ) ). But i can live with the second method.

    @diondesigns Thanx for the suggestion, but ( see below ) this returns the total number as if there were no LIMIT clause, i.c. 1888, while i want to know if the number of items the query would return including the LIMIT clause is more ore less than e.g. 5000.
    The LIMIT clause could be e.g. LIMIT 100, but also LIMIT 7000.

    Q = SELECT SQL_CALC_FOUND_ROWS * FROM wp_options LIMIT 7, result = a:7:{i:0;O:8:”stdClass”:4:{s:9:”option_id”;s:1:”1″;s:11:”option_name”;s:7:”siteurl”;s:12:”option_value”;s:23:”https://beta.opajaap.nl”;s:8:”autoload”;s:3:”yes”;}i:1;O:8:”stdClass”:4:{s:9:”option_id”;s:1:”2″;s:11:”option_name”;s:8:”blogname”;s:12:”option_value”;s:15:”WPPA+ Beta test”;s:8:”autoload”;s:3:”yes”;}i:2;O:8:”stdClass”:4:{s:9:”option_id”;s:1:”3″;s:11:”option_name”;s:15:”blogdescription”;s:12:”option_value”;s:27:”Just another WordPress site”;s:8:”autoload”;s:3:”yes”;}i:3;O:8:”stdClass”:4:{s:9:”option_id”;s:1:”4″;s:11:”option_name”;s:18:”users_can_register”;s:12:”option_value”;s:1:”0″;s:8:”autoload”;s:3:”yes”;}i:4;O:8:”stdClass”:4:{s:9:”option_id”;s:1:”5″;s:11:”option_name”;s:11:”admin_email”;s:12:”option_value”;s:18:”opajaap@opajaap.nl”;s:8:”autoload”;s:3:”yes”;}i:5;O:8:”stdClass”:4:{s:9:”option_id”;s:1:”6″;s:11:”option_name”;s:13:”start_of_week”;s:12:”option_value”;s:1:”1″;s:8:”autoload”;s:3:”yes”;}i:6;O:8:”stdClass”:4:{s:9:”option_id”;s:1:”7″;s:11:”option_name”;s:15:”use_balanceTags”;s:12:”option_value”;s:1:”0″;s:8:”autoload”;s:3:”yes”;}}
    Q = SELECT FOUND_ROWS(), result = 1888

    Note this section in the MySQL documentation:

    In the absence of the SQL_CALC_FOUND_ROWS option in the most recent successful SELECT statement, FOUND_ROWS() returns the number of rows in the result set returned by that statement. If the statement includes a LIMIT clause, FOUND_ROWS() returns the number of rows up to the limit.

    That means the following two queries should provide what you want:

    $query = "SELECT option_id FROM {$wpdb->options} LIMIT 7000";
    $query = 'SELECT FOUND_ROWS()';
    

    Selecting the option ID should minimize server issues; the result set in the above query would require about 100K of memory, and that memory is freed when running the FOUND_ROWS() query.

    Thanx, however, this looks not substantially cheaper than querying the ids and count the returned array:

    
    $query = "SELECT option_id FROM {$wpdb->options} LIMIT 7000";
    $ids = get_row( $query );
    $count = count( $ids );
    

    vs

    
    $query = "SELECT option_id FROM {$wpdb->options} LIMIT 7000";
    $ids = get_row( $query );
    $query = 'SELECT FOUND_ROWS()';
    $count = get_var( $query );
    

    The difference is that you don’t need to fetch the initial query. I think you’ll see the difference if you use the following:

    $wpdb->query("SELECT option_id FROM {$wpdb->options} LIMIT 7000");
    $count = $wpdb->get_var('SELECT FOUND_ROWS()');
    

    The reason I say think is because the WordPress DBAL does some screwy things with freeing result sets, and that may be an issue here.

    That sounds good.
    The code fragment that i will use for the time being is: (plugin wp-photo-album-plus Version: 6.7.09.Beta.010 file functions.php in function wppa_do_get_thumbs_query() line 1926:)

    
    	// Do we need to get the count first to decide if we get the full data and probably cache it ?
    	if ( $count_first || $invers ) {
    
    		// Find count of the query result
    		$tempquery 	= str_replace( 'SELECT *', 'SELECT id', $query );
    		$wpdb->query( $tempquery );
    		$count 		= $wpdb->get_var( 'SELECT FOUND_ROWS()' );
    
    		// If less than 5000, get them and cache them
    		if ( $count <= 5000 && ! $invers ) {
    			$thumbs 	= $wpdb->get_results( $query, ARRAY_A );
    			$caching 	= true;
    		}
    
    		// If more than 5000, or inverse requested, use the ids only, and do not cache them
    		else {
    			$thumbs 	= $wpdb->get_results( $tempquery, ARRAY_A );
    			$caching 	= false;
    		}
    	}
    
    	// Need no count first, just do it.
    	else {
    		$thumbs 	= $wpdb->get_results( $query, ARRAY_A );
    		$caching 	= true;
    		$count 		= count( $thumbs );
    	}
    

    As you can see, i need the possibility to get the inverse of a query for one of the many search options of the plugin. Currently i do the query (ids only) of the selection, and later i query all ids and array_diff the initial query. This leads to performance issues when there are more than 100k items, and i want to get this plugin to work up to 1 million photos…
    Do you by any chance know a better method to do this?

Viewing 8 replies - 1 through 8 (of 8 total)
  • You must be logged in to reply to this topic.