여기는 블로그가 아니라 메모장으로 쓰는군...ㅡ.ㅡ;;;
많은 사람들(나만 그런가?)이 paged list를 만들 때 전체 row수를 페이지에 나타나는 갯수로 나누어서 페이지를 나눠놓고 가져올 때는 limit로 가져올것이다. 그러면 페이지를 나타낼 때 전체수를 나누는 쿼리를 한번 더해야되는 수가 있고 그 쿼리도 만들어야 되는 경우가 발생한다. 물론 아래의 방법도 쿼리를 한번 더 해야되나 그 쿼리를 만들지 않아도 되고 그냥 found_rows()라는 것을 사용하면 된다.

문제는 이것이 subquery 안에서는 동작이 안된다. 가장 바깥에 있는 쿼리에서만 사용해야된다. 해보니 잘된다. 이걸로 totalcount를 가져오는 쿼리를 다 바꿔야겠다..흐극...

MySQL: Get total number of rows when using LIMIT August 11, 2006

Posted by Slobodan Kovacevic in : Programming , trackback

Every now and then you need to limit the number of rows MySQL returns, i.e. use the LIMIT clause. Result set pagination is by far the most often usage of LIMIT clause, since you usually want to select only rows you’ll be displaying on certain page.

The problem is that for pagination you also need total number of rows in a result set, so you know how many pages you’ll have. This usually means that you need to execute query two times. First query is for counting total number of rows without LIMIT. Second query is exactly the same as the first, just without LIMIT and it will actually retrieve required data. You would need two queries like these:

SELECT COUNT(*) FROM users WHERE name LIKE 'a%';

SELECT name, email FROM users WHERE name LIKE 'a%' LIMIT 10;

Now, this is not such a big problem when you have small result sets and/or simple queries. But if you have a complex query that joins several tables and takes a while to execute - well, you probably wouldn’t want to execute it twice and waste server resources.

Luckily since MySQL 4.0.0 you can use SQL_CALC_FOUND_ROWS option in your query which will tell MySQL to count total number of rows disregarding LIMIT clause. You still need to execute a second query in order to retrieve row count, but it’s a simple query and not as complex as your query which retrieved the data.

Usage is pretty simple. In you main query you need to add SQL_CALC_FOUND_ROWS option just after SELECT and in second query you need to use FOUND_ROWS() function to get total number of rows. Queries would look like this:

SELECT SQL_CALC_FOUND_ROWS name, email FROM users WHERE name LIKE 'a%' LIMIT 10;

SELECT FOUND_ROWS();

The only limitation is that you must call second query immediately after the first one because SQL_CALC_FOUND_ROWS does not save number of rows anywhere.

Although this solution also requires two queries it’s much more faster, as you execute the main query only once.

You can read more about SQL_CALC_FOUND_ROWS and FOUND_ROWS() in MySQL docs.