MySQL: Get result count, but show only 1 record

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
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

MySQL: Get result count, but show only 1 record

Post by batfastad »

Hi everyone

I've written a MySQL query which works great, and on my page I want to show a figure for the total number of matches, but only actually show 1 match.
I realise that the total number of records returned by MySQL is governed by the "LIMIT x, y" clause... where x is the start number and y is the total to return.

My query could take up to a couple of seconds in some circumstances and I don't want the user to be waiting a few seconds to page through each result, just because I want to display the total result number.

Also it just doesn't feel right, getting MySQL to send all that data to PHP (2m+ records for some queries) when I only need the full data from 1 result.

Or does it make any difference?
Will LIMIT 100, 5000000 be the same as LIMIT 100, 1... in terms of my application performance?
My application only uses the 1st result, the user then pages to the next one and so on. Currently I get the record count by using the following in PHP after my query:

Code: Select all

$sql_result_count = mysql_num_rows($sql_result);
Is there a better/quicker way to do this?
Or is the best way to tell it to return all results, then just use the 1st result and ignore the rest?

Thanks, Ben
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Re: MySQL: Get result count, but show only 1 record

Post by Burrito »

you could try a 'SELECT COUNT()' as that won't use as much resources for your $result.

also, table optimization (indexing) and query optimization will help some too.
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL: Get result count, but show only 1 record

Post by batfastad »

Ah ok. So I run SELECT COUNT(); as a separate query, after my initial query?
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Re: MySQL: Get result count, but show only 1 record

Post by Burrito »

if your goal is to only show one row but you want to display the total number of rows, then I'd do two queries (one with the count, and one with the regular query LIMIT 1).
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL: Get result count, but show only 1 record

Post by batfastad »

Ok that sounds good :D

I have also just found this... http://dev.mysql.com/doc/refman/5.0/en/ ... found-rows

So I add SQL_CALC_FOUND_ROWS into my SELECT of the main query
Then run a 2nd query just after... SELECT FOUND_ROWS()

Seems to do what I want also, but I don't know which is best. Time for some benchmarking I guess
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Re: MySQL: Get result count, but show only 1 record

Post by Burrito »

wow nice....definitely gonna add that to my tool box.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL: Get result count, but show only 1 record

Post by VladSun »

We had a related discussion here recently. It appears that SQL_CALC_FOUND_ROWS has bad performance compared to count():
http://www.mysqlperformanceblog.com/200 ... ent-437130
So, obvious conclusion from this simple test is: when we have appropriate indexes for WHERE/ORDER clause in our query, it is much faster to use two separate queries instead of one with SQL_CALC_FOUND_ROWS.
Last edited by VladSun on Tue Jan 20, 2009 4:18 pm, edited 1 time in total.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL: Get result count, but show only 1 record

Post by batfastad »

Ah right, ok this is interesting.
So is that true for most queries then?

The queries I'll be running this on is the advanced search facility of my intranet solution, where a user types a raw SQL query in a read-only connection
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL: Get result count, but show only 1 record

Post by VladSun »

You can assume that it is always this way.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply