开发者

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

开发者 https://www.devze.com 2022-12-21 04:28 出处:网络
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

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.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号