The convenient method of counting the total number of rows of query results in MYSQL eliminates count of *

  • 2020-05-30 21:12:12
  • OfStack

Keywords of MYSQL:
Looking at the manual, it turns out that the purpose of this keyword is to count the total number of results that meet the filtering criteria at query time (not restricted by Limit).

Such as:
SELECT SQL_CALC_FOUND_ROWS tid FROM cdb_threads WHERE fid=14 LIMIT 1,10; 

So let's say I have 1,000 that satisfy this condition, so I'm going to return 10.
Immediate use
SELECT found_rows() AS rowcount; 

Then the return rowcount is 1000;
This saves a considerable amount of time by saving repeated queries for SELECT count(*) AS rowcount.

The following are the applications that should be put in the game:
function mail_list_sent( $uid, $start ) { 
//  Pay attention to SQL_CALC_FOUND_ROWS uid There is no comma between  
$query = "SELECT SQL_CALC_FOUND_ROWS uid, real_name, current_city, msg_uid, sender_flag, ". 
"msg_title, msg_content FROM " . TT_DBTABLEPRE . "mailbox as mb1, " . TT_DBTABLEPRE . 
"user as usr1 WHERE mb1.sender_id=usr1.uid AND mb1.sender_id=$uid AND sender_flag > 0 LIMIT $start, " . TT_PAGESIZE; 
$mails = $this->db->fetch_all( $query ); 
// The query SELECT , and the number of rows satisfying the condition LIMIT Clause has nothing to do  
$max_count = $this->db->fetch_first( "SELECT found_rows() AS rowcount" ); 
$tmp['state_code'] = 200; 
$tmp['info'] = "OK"; 
$tmp['list'] = $mails; 
$data = json_encode( $tmp ); 
return $data; 

Related articles: