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
