What is the least expensive method for counting rows for an SQL query?

Question

When writing a paging request on a web page, what is the least expensive method to get the total number of lines? Is there a way to do this without executing the request twice - one for the total and the next for the limit?

Using MySQL

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

Get quantity

SELECT COUNT(*) FROM table

Get paging

SELECT mycolumns FROM table LIMIT 100

How can I get the total score without running 2 requests.

Answer 1

This will give you an additional column with a name Countin each row that contains the total number of rows:

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

Answer 2

SQL_CALC_FOUND_ROWS, :

SELECT FOUND_ROWS();

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

Answer 3

select count (*) from tablename

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

Answer 4

select count(1) as counter from *table_name*

this is better than using count(*)as you avoid checking all columns in the table.