What is the least expensive method to get a row count for a SQL Query?

Question

When writing a query for paging on a web page what is the least expensive method to get a total row count? Is there a way to do this without running a query twice - one for the total and the next for the limit?

Using MySQL

Example: (I want to know if there is a less expensive way)

Get Count

SELECT COUNT(*) FROM table

Get Paging

SELECT mycolumns FROM table LIMIT 100

How can I get the total count without running 2 queries.

Answer 1

select count(*) from tablename

Is the most efficient way to get the number of rows in table tablename

Answer 2

select count(1) as counter from *table_name*

this is slighter better than using count(*) as your avoiding checking all the columns in the table.

Answer 3

You can run the first query with the SQL_CALC_FOUND_ROWS option, and then run this:

SELECT FOUND_ROWS();

http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_found-rows

Answer 4

This will give you an additional column called Count in each row that contains the total number of rows:

SELECT mycolumns, (select count(*) from table) as Count 
FROM table 
LIMIT 100