List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:July 18 2001 5:22pm
Subject:Re: MySQL get COUNT(*) on # rows AND return LIMITed results in 1 query ?
View as plain text  
In the last episode (Jul 18), S A said:
> Is there some way to take the following 2 queries & make them into
> one ?

Nope.  Do them separately (or do the count(*) once and cache it for the
rest of the session).

> I want to be able to in one query get the # of overall rows and do a
> LIMITed result set to get the first NN rows.
> 
> Is it possible to to do this in one query ? Presumable MySQL has to
> figure out what the overall # is to return the limited result set so
> it'd be more efficient to do this all at once.

No, mysql doesn't have to calculate the entire resultset; all it needs
to do it get the first record.  If you had 1 million records with
group_id=1, it could take a long time to calculate the count(*).
 
> SELECT COUNT(*) FROM posts WHERE group_id=1;

i.e. find first group_id index entry with the value of '1', scan
forward in the index until value != 1, return count.
 
> SELECT post_subject FROM posts WHERE group_id=1 LIMIT 10,10;

i.e. find first group_id index entry with the value of '1', skip 10
entries, then for the next 10 entries (assuming there are still records
with value of '1'), fetch the appropriate data record and return
'post_subject' to the user.

-- 
	Dan Nelson
	dnelson@stripped
Thread
MySQL get COUNT(*) on # rows AND return LIMITed results in 1 query ?S A18 Jul
  • Re: MySQL get COUNT(*) on # rows AND return LIMITed results in 1 query ?Dan Nelson18 Jul