Getting total number of matches while limiting return.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
arpowers
Forum Commoner
Posts: 76
Joined: Sun Oct 14, 2007 10:05 pm
Location: san diego, ca

Getting total number of matches while limiting return.

Post by arpowers »

Hey guys,

What is the easiest way to limit the number of results returned by a query (e.g. limit 20...) while returning the total number of database matches.. (e.g. john has 28 friends... )
in that case you wouldn't want to show all returned results.

thanks
Andrew
User avatar
seppo0010
Forum Commoner
Posts: 47
Joined: Wed Oct 24, 2007 4:13 pm
Location: Buenos Aires, Argentina

Post by seppo0010 »

I think found_rows() is the easier way.
I´m not sure if it works in a SQL besides MySQL.
User avatar
arpowers
Forum Commoner
Posts: 76
Joined: Sun Oct 14, 2007 10:05 pm
Location: san diego, ca

Post by arpowers »

exactly exactly what I was looking for!

You are the best!
Thanks:D
Andrew
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

Wow.. I didn't know that existed. Very useful indeed.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

Yeah, I didn't know about that either. Usually I would've ran two separate queries to get that information, or made some clever use of count().
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Inkyskin
Forum Contributor
Posts: 282
Joined: Mon Nov 19, 2007 10:15 am
Location: UK

Post by Inkyskin »

Me too... this is going to help my pagination class no end :)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post by VladSun »

I read in "MySQL vs. PGSL" forums that using SQL_CALC_FOUND_ROWS/FOUND_ROWS(), indeed makes MySQL engine execute two separate queries, so it's not so good as it sounds :)
Just keep in mind about this and performance
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

VladSun wrote:I read in "MySQL vs. PGSL" forums that using SQL_CALC_FOUND_ROWS/FOUND_ROWS(), indeed makes MySQL engine execute two separate queries, so it's not so good as it sounds :)
Just keep in mind about this and performance
True I guess, but it'd only have to parse and optimise the query once and it might do other internal optimisations like storing information about the first 'pass' so it'd still be much quicker than doing the query twice yourself.
Post Reply