Hi,

LIMIT is applied AFTER select is executed and limits then number of
rows returned to the client. Since SELECT COUNT(*)... generates a single
row, LIMIT is useless.

You can do this :
SELECT <somefield> from .... LIMIT 100
and check the number of rows actually returned.
I don't know which programming language you use but you should have
an API function that returns the number of rows (numrows() in Perl)
so that you don't need a loop to count them.
(We use that trick and it is really fast as long as the WHERE clause
can use an index).

Hope this help
--
Joseph Bueno
NetClub/Trader.com

mos wrote :
> 
> I have a Where clause like:
>          select count(*) from table where ....  LIMIT 100
> 
> Unfortunately the Count(*) ignores the LIMIT clause entirely. Why?
> 
> It seems to me that if a "select * from table where ... limit 100" returns
> between 0 and 100 rows, you should be able to count it. Instead the count
> returns 55,000 or some ridiculously large number that has no bearing on the
> # of rows that will actually be returned (because of the LIMIT clause).
> Since this is running on a webserver, I don't want it to physically count
> more than 100 rows. Some of the tables may be over 1 million rows and
> counting that many rows when only 100 rows are returned is overkill.
> 
> Is there a way around this counting problem? The only solution I've come up
> with is to traverse all the rows returned by counting them in a loop. This
> seems pretty lame and I'm hoping someone can come up with a better solution.
> 
> Mike
> 
> sql,query
>

---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Reply via email to