問題描述
當你限制一個SQL查詢返回的行數時,通常用于分頁,有兩種方法可以確定總記錄數:
When you limit the number of rows to be returned by a SQL query, usually used in paging, there are two methods to determine the total number of records:
在原SELECT
中包含SQL_CALC_FOUND_ROWS
選項,然后通過運行SELECT FOUND_ROWS()
得到總行數:>
Include the SQL_CALC_FOUND_ROWS
option in the original SELECT
, and then get the total number of rows by running SELECT FOUND_ROWS()
:
SELECT SQL_CALC_FOUND_ROWS * FROM table WHERE id > 100 LIMIT 10;
SELECT FOUND_ROWS();
方法二
正常運行查詢,然后通過運行SELECT COUNT(*)
SELECT * FROM table WHERE id > 100 LIMIT 10;
SELECT COUNT(*) FROM table WHERE id > 100;
哪種方法最好/最快?
推薦答案
視情況而定.請參閱有關此主題的 MySQL 性能博客文章:要SQL_CALC_FOUND_ROWS
還是不SQL_CALC_FOUND_ROWS
?
It depends. See the MySQL Performance Blog post on this subject: To SQL_CALC_FOUND_ROWS
or not to SQL_CALC_FOUND_ROWS
?
只是一個簡短的總結:彼得說這取決于您的索引和其他因素.該帖子的許多評論似乎都說 SQL_CALC_FOUND_ROWS
幾乎總是比運行兩個查詢慢 - 有時最多慢 10 倍.
Just a quick summary: Peter says that it depends on your indexes and other factors. Many of the comments to the post seem to say that SQL_CALC_FOUND_ROWS
is almost always slower - sometimes up to 10x slower - than running two queries.
這篇關于哪個最快?SELECT SQL_CALC_FOUND_ROWS FROM `table`,或 SELECT COUNT(*)的文章就介紹到這了,希望我們推薦的答案對大家有所幫助,也希望大家多多支持html5模板網!