How can I count the number of rows matching a query, but only return a subset in SQL?

Question

I currently have this code:

$result = $db->prepare("SELECT * FROM `users` WHERE MATCH (`name`, `blurb`) AGAINST (:quer IN BOOLEAN MODE) LIMIT $rpage, $max_show;"); $result->execute(array(":quer" => $query)); $count = $db->prepare("SELECT COUNT(*) FROM `users` WHERE MATCH (`name`, `blurb`) AGAINST (:quer IN BOOLEAN MODE);"); $count->execute(array(":quer" => $query)); 

The first query captures a bunch of rows from the table. The second counts lines that match the same criteria as the first, which allows pagination.

Is it possible to combine these two queries into one? And will it be more effective?

Answer 1

check using SQL_CALC_FOUND_ROWS. You add this to your selection in the first query, and then just call SELECT FOUND_ROWS () after. You can learn more about this here: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows . and yes, it will be more efficient than running a search query 2 times.

Answer 2

According to the criteria of MySQL experts in mysqlperformanceblog , running two separate queries will probably be faster than combining both in a single query.