Re: Getting Found Count When Using Limit
Interesting comment in the manual, though, where someone says they tried both methods and found the multiple statements to be faster than SQL_CALC_FOUND_ROWS. --jeff - Original Message - From: "Roger Baklund" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: "Fletcher Sandbeck" <[EMAIL PROTECTED]> Sent: Thursday, March 21, 2002 3:04 PM Subject: RE: Getting Found Count When Using Limit > * Fletcher Sandbeck > > Actually, easy thing to do through the APIs or most third-party > > languages that let you access MySQL. I'm looking for a way to > > do it using raw MySQL statements. > > > > I think I found my answer in the list archives. It's not > > possible without using multiple SQL statements. > > There is a way to do it if you are using version 4... from the manual: > > "SQL_CALC_FOUND_ROWS tells MySQL to calculate how many rows there would be > in the result, disregarding any LIMIT clause. The number of rows can be > obtained with SELECT FOUND_ROWS()." > > http://www.mysql.com/doc/S/E/SELECT.html > > > -- > Roger > > > - > 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 > - 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
RE: Getting Found Count When Using Limit
* Fletcher Sandbeck > Actually, easy thing to do through the APIs or most third-party > languages that let you access MySQL. I'm looking for a way to > do it using raw MySQL statements. > > I think I found my answer in the list archives. It's not > possible without using multiple SQL statements. There is a way to do it if you are using version 4... from the manual: "SQL_CALC_FOUND_ROWS tells MySQL to calculate how many rows there would be in the result, disregarding any LIMIT clause. The number of rows can be obtained with SELECT FOUND_ROWS()." http://www.mysql.com/doc/S/E/SELECT.html > -- Roger - 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
Re: Getting Found Count When Using Limit
On 3/21/02 at 12:51 PM, Allon Bendavid wrote: > Tough thing to do. Fortunately we thought this through for you in jTalk. > Keep your eyes peeled for a formal announcement next week. Actually, easy thing to do through the APIs or most third-party languages that let you access MySQL. I'm looking for a way to do it using raw MySQL statements. I think I found my answer in the list archives. It's not possible without using multiple SQL statements. [fletcher] -- Fletcher Sandbeck [EMAIL PROTECTED] Lasso Product Specialist [EMAIL PROTECTED] Blue World Communications, Inc. http://www.blueworld.com/ - 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
Re: Getting Found Count When Using Limit
Tough thing to do. Fortunately we thought this through for you in jTalk. Keep your eyes peeled for a formal announcement next week. -Allon On 3/21/02 11:04 AM, "Fletcher Sandbeck" <[EMAIL PROTECTED]> wrote: > I am trying to get the full found count from a query while using LIMIT to > return > just a portion of the found records. I can use two queries in a row to get > first the found count, then the found records, but that takes twice as long as > performing either query by itself. > > SELECT COUNT(*) FROM database.table WHERE query > SELECT fields FROM database.table WHERE query LIMIT 10 > > If I use "COUNT(*),fields" in the SQL statement I have to add a GROUP BY > clause. > If I GROUP BY my auto increment field then COUNT(*) always evaluates to 1. If > I > GROUP BY a constant in every row then I get one record back with the correct > total. > > Is there any way to get both the number of records found and the first set of > results using a single query? > > Thanks, > > [fletcher] > > > -- > Fletcher Sandbeck [EMAIL PROTECTED] > Lasso Product Specialist [EMAIL PROTECTED] > Blue World Communications, Inc. http://www.blueworld.com/ > > - > 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 > Allon Bendavid Imacination Software [EMAIL PROTECTED]http://www.imacination.com/ 805-650-8153 Visit Imacination and start selling on the Web today with Ch-Ching! - 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
Getting Found Count When Using Limit
I am trying to get the full found count from a query while using LIMIT to return just a portion of the found records. I can use two queries in a row to get first the found count, then the found records, but that takes twice as long as performing either query by itself. SELECT COUNT(*) FROM database.table WHERE query SELECT fields FROM database.table WHERE query LIMIT 10 If I use "COUNT(*),fields" in the SQL statement I have to add a GROUP BY clause. If I GROUP BY my auto increment field then COUNT(*) always evaluates to 1. If I GROUP BY a constant in every row then I get one record back with the correct total. Is there any way to get both the number of records found and the first set of results using a single query? Thanks, [fletcher] -- Fletcher Sandbeck [EMAIL PROTECTED] Lasso Product Specialist [EMAIL PROTECTED] Blue World Communications, Inc. http://www.blueworld.com/ - 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
