So I have a table that has a little over 5 million rows. When I use SQL_CALC_FOUND_ROWS the query just hangs forever. When I take it out the query executes within a second withe LIMIT ,25. My question is for pagination reasons is there an alternative to getting the number of total rows?
所以我有一个有500多万行的表。当我使用SQL_CALC_FOUND_ROWS时，查询将永远挂起。当我把它拿出来时，查询会在一秒内执行LIMIT，25。我的问题是分页原因是否有一个替代方法来获取总行数？
3 个解决方案
#1
6
SQL_CALC_FOUND_ROWS forces MySQL to scan for ALL matching rows, even if they'd never get fetched. Internally it amounts to the same query being executed without the LIMIT clause.
SQL_CALC_FOUND_ROWS强制MySQL扫描所有匹配的行，即使它们永远不会被获取。在内部，它相当于在没有LIMIT子句的情况下执行的相同查询。
If the filtering you're doing via WHERE isn't too crazy, you could calculate and cache various types of filters to save the full-scan load imposed by calc_found_rows. Basically run a "select count(*) from ... where ...." for most possible where clauses.
如果您通过WHERE进行的过滤不是太疯狂，您可以计算并缓存各种类型的过滤器，以节省calc_found_rows强加的全扫描负载。对于大多数可能的where子句，基本上运行“select count（*）from ... where ....”。
Otherwise, you could go Google-style and just spit out some page numbers that occasionally have no relation whatsoever with reality (You know, you see "Goooooooooooogle", get to page 3, and suddenly run out of results).
否则，你可以采用谷歌风格，只是吐出一些偶尔与现实没有任何关系的页码（你知道，你看到“Goooooooooooogle”，进入第3页，然后突然用完结果）。
#3
-1
You should choose between COUNT(*) AND SQL_CALC_FOUND_ROWS depending on situation. If your query search criteria uses rows that are in index - use COUNT(*). In this case Mysql will "read" from indexes only without touching actual data in the table while SQL_CALC_FOUND_ROWS method will load rows from disk what can be expensive and time consuming on massive tables.
您应该根据情况在COUNT（*）和SQL_CALC_FOUND_ROWS之间进行选择。如果查询搜索条件使用索引中的行 - 请使用COUNT（*）。在这种情况下，Mysql将仅从索引“读取”而不触及表中的实际数据，而SQL_CALC_FOUND_ROWS方法将从磁盘加载行，这对于大型表来说既昂贵又耗时。
More information on this topic in this article @mysqlperformanceblog.
本文中有关此主题的更多信息@mysqlperformanceblog。
#1
6
SQL_CALC_FOUND_ROWS forces MySQL to scan for ALL matching rows, even if they'd never get fetched. Internally it amounts to the same query being executed without the LIMIT clause.
SQL_CALC_FOUND_ROWS强制MySQL扫描所有匹配的行，即使它们永远不会被获取。在内部，它相当于在没有LIMIT子句的情况下执行的相同查询。
If the filtering you're doing via WHERE isn't too crazy, you could calculate and cache various types of filters to save the full-scan load imposed by calc_found_rows. Basically run a "select count(*) from ... where ...." for most possible where clauses.
如果您通过WHERE进行的过滤不是太疯狂，您可以计算并缓存各种类型的过滤器，以节省calc_found_rows强加的全扫描负载。对于大多数可能的where子句，基本上运行“select count（*）from ... where ....”。
Otherwise, you could go Google-style and just spit out some page numbers that occasionally have no relation whatsoever with reality (You know, you see "Goooooooooooogle", get to page 3, and suddenly run out of results).
否则，你可以采用谷歌风格，只是吐出一些偶尔与现实没有任何关系的页码（你知道，你看到“Goooooooooooogle”，进入第3页，然后突然用完结果）。
#2
#3
-1
You should choose between COUNT(*) AND SQL_CALC_FOUND_ROWS depending on situation. If your query search criteria uses rows that are in index - use COUNT(*). In this case Mysql will "read" from indexes only without touching actual data in the table while SQL_CALC_FOUND_ROWS method will load rows from disk what can be expensive and time consuming on massive tables.
您应该根据情况在COUNT（*）和SQL_CALC_FOUND_ROWS之间进行选择。如果查询搜索条件使用索引中的行 - 请使用COUNT（*）。在这种情况下，Mysql将仅从索引“读取”而不触及表中的实际数据，而SQL_CALC_FOUND_ROWS方法将从磁盘加载行，这对于大型表来说既昂贵又耗时。
More information on this topic in this article @mysqlperformanceblog.
本文中有关此主题的更多信息@mysqlperformanceblog。