How to get number of results from MySQL query without getting all result data?

Question

For a Google Maps application, I built an AJAX call that dynamically checks whether there are markers in a given viewport range. All marker information is stored in a node (note that I am using projective coordinates x, y instead of lat,lng) and the text in the body field also matches a condition. The MySQL query is as follows:

var $text = 'Test';
$query = db_select('field_data_body', 'b');
$query->join('field_data_field_x', 'x', 'b.entity_id = x.entity_id');
$query
  ->fields('b', array('entity_id'))
  ->fields('x', array('field_x_value'))
$query->where('b.body_value LIKE :text', array(':text' => $text));
$query->where('x.field_x_value >= :num1', array(':num2' => $xmin));
$query->where('x.field_x_value <= :num2', array(':num2' => $xmax));
$result = $query->execute();
if ($result->rowCount() < $maximum) {
 process_data($row->entity_id, $row->field_x_value);
}

Now, whenever the amount of markers exceeds a number $maximum, a clustering mechamism sets in. For this purpose, I really need to

1) check if there a less than $maximum results,

and only in this case

2) pull that data and process it.

Since quite many markers are stored in my database, my MySQL query should be as slim as possible, ideally it would first only give me the number of results (similar to a COUNT* query) and pull the result data only if needed.

Can anybody advice me on whether my $query->execute()->rowCount() code does that job? Or does $query->execute() automatically pull all results, and rowCount() is a PHP counting routine? The $result object does not seem to contain the explicit data, therefore it seems plausible to me that the data might not have been pulled yet at that stage...

Thanks for your help!

Answer 1

MySQL has no mechanism like that; you'll need to run two queries, the first a count query, and the second for the data if your condition on the first query result passes.

The PDO rowCount() method isn't used for SELECT queries, just DELETE, INSERT and UPDATE queries.

You'll need the countQuery() method to determine the count with a separate query, then simply run the original query if the count query gives the right result