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 with开发者_如何学编程out 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.
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
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
select count(*) from tablename
Is the most efficient way to get the number of rows in table tablename
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.
精彩评论