MySQL: Get result count, but show only 1 record
Posted: Tue Jan 20, 2009 9:31 am
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:
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
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);Or is the best way to tell it to return all results, then just use the 1st result and ignore the rest?
Thanks, Ben