Get size of max possible result set

For my application most of my SQL queries return a specified number of rows. I'd also like to get the maximum possible number of results i.e. how many rows would be returned if I wasn't setting a LIMIT.

Is there a more efficient way to do this开发者_JAVA技巧 (using just SQL?) than returning all the results, getting the size of the result set and then splicing the set to return just the first N rows.

You can use SELECT COUNT(*), but this isn't ideal for large data sets.
A more efficient solution is to use SQL_CALC_FOUND_ROWS and FOUND_ROWS(): http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

First query:

SELECT SQL_CALC_FOUND_ROWS id, name, etc FROM table LIMIT 10;

Second query:

SELECT FOUND_ROWS();

You'll still need two queries, but you run the main query once, saving resources.

I'd also like to get the maximum possible number of results i.e. how many rows would be returned if I wasn't setting a LIMIT.

Use:

SELECT COUNT(*)
  FROM YOUR_TABLE

...to get the number of rows that currently exist in YOUR_TABLE.

Is there a more efficient way to do this (using just SQL?) than returning all the results, getting the size of the result set and then splicing the set to return just the first N rows.

Only fetch the rows/information you need.
Getting everything means a lot of data is going over the wire, that likely won't be used at all. In this situation, data is being cached - which means it can get stale because it isn't fresh from the database.

This sounds like pagination...

To get the specified number of rows you could use select count(*). Is that what you are looking for ?