List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 21 1999 12:52am
Subject:Re: Question re: count()
View as plain text  
On Mon, 1999-09-20 11:35:55 -0700, Bob Bowker wrote:
> Can COUNT() be included in a MySQL query which has LIMIT x,y in it?

Yes, no problem with that.  LIMIT doesn't change a query, it just
limits which rows of the result are returned.

But be aware, that COUNT() normally is an aggregation function which
only counts the number of rows per group if you're using GROUP BY.

Using COUNT like in
  SELECT COUNT(*) FROM ... WHERE ...
is a special case, and counts the number of matching rows.
(You can use LIMIT here, too, but it's not very useful, because
 there is only one result row at all - not much to limit ...)

> I'm trying to avoid going to the well twice, first to get a total
> count then back again to get the first 10 - the real query is quite
> complex, and the table contains 250,000 records.

Nontheless this is the normal way to do it:
  1st, SELECT COUNT(*) FROM ... WHERE ...;
       --> retrieve total number of result rows
  2nd, SELECT ... FROM ... WHERE ... LIMIT ...;
       --> retrieve desired subset of result rows.


Maybe there's another better solution for your situation?
 - store complete result set in a temporary table
   (and get total count while doing that)
 - then fast selects from this temporary table

 With MySQL V3.23.x do:
  CREATE TEMPORARY TABLE tmp TYPE=heap
    SELECT ... FROM ... WHERE ...;
    --> mysql_num_rows
  SELECT * FROM tmp LIMIT ...;
  SELECT * FROM tmp LIMIT ...;
  SELECT * FROM tmp LIMIT ...;
  DROP TABLE tmp;

 With earlier versions do:
  CREATE TABLE tmp ( ...suitable field definitions... );
  INSERT INTO tmp SELECT ... FROM ... WHERE ...;
    --> mysql_num_rows
  SELECT * FROM tmp LIMIT ...;
  SELECT * FROM tmp LIMIT ...;
  SELECT * FROM tmp LIMIT ...;
  DROP TABLE tmp;


Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
Question re: count()Bob Bowker20 Sep
  • Re: Question re: count()Steve Ruby20 Sep
    • Re: Question re: count()Bob Bowker20 Sep
  • Re: Question re: count()Martin Ramsch21 Sep