Page 1 of 1

MySQL: Get result count, but show only 1 record

Posted: Tue Jan 20, 2009 9:31 am
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

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

Posted: Tue Jan 20, 2009 9:41 am
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.

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

Posted: Tue Jan 20, 2009 9:49 am
by batfastad
Ah ok. So I run SELECT COUNT(); as a separate query, after my initial query?

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

Posted: Tue Jan 20, 2009 9:52 am
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).

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

Posted: Tue Jan 20, 2009 9:56 am
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

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

Posted: Tue Jan 20, 2009 9:59 am
by Burrito
wow nice....definitely gonna add that to my tool box.

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

Posted: Tue Jan 20, 2009 2:02 pm
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.

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

Posted: Tue Jan 20, 2009 2:55 pm
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

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

Posted: Tue Jan 20, 2009 4:20 pm
by VladSun
You can assume that it is always this way.