[MySQL] 게시판 limit 사용시 쿼리 한번에 페이징하기 - SQL_CALC_FOUND_ROWS & FOUND_ROWS

SQL_CALC_FOUND_ROWS!

이걸 왜 이제 알았단 말이냐!!  OTL...

 

FOUND_ROWS()

A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To obtain this row count, include a SQL_CALC_FOUND_ROWS option in theSELECT statement, and then invoke FOUND_ROWS() afterward:

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name -> WHERE id > 100 LIMIT 10; mysql> SELECT FOUND_ROWS();

The second SELECT returns a number indicating how many rows the first SELECT would have returned had it been written without the LIMIT clause

 

In the absence of the SQL_CALC_FOUND_ROWS option in the most recent SELECTstatement, FOUND_ROWS() returns the number of rows in the result set returned by that statement.

The row count available through FOUND_ROWS() is transient and not intended to be available past the statement following the SELECT SQL_CALC_FOUND_ROWSstatement. If you need to refer to the value later, save it:

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM ... ; mysql> SET @rows = FOUND_ROWS();

If you are using SELECT SQL_CALC_FOUND_ROWS, MySQL must calculate how many rows are in the full result set. However, this is faster than running the query again without LIMIT, because the result set need not be sent to the client.

SQL_CALC_FOUND_ROWS and FOUND_ROWS() can be useful in situations when you want to restrict the number of rows that a query returns, but also determine the number of rows in the full result set without running the query again. An example is a Web script that presents a paged display containing links to the pages that show other sections of a search result. Using FOUND_ROWS() allows you to determine how many other pages are needed for the rest of the result.

The use of SQL_CALC_FOUND_ROWS and FOUND_ROWS() is more complex for UNIONstatements than for simple SELECT statements, because LIMIT may occur at multiple places in a UNION. It may be applied to individual SELECT statements in the UNION, or global to the UNION result as a whole.

The intent of SQL_CALC_FOUND_ROWS for UNION is that it should return the row count that would be returned without a global LIMIT. The conditions for use ofSQL_CALC_FOUND_ROWS with UNION are:

• The SQL_CALC_FOUND_ROWS keyword must appear in the first SELECT of the UNION.

• The value of FOUND_ROWS() is exact only if UNION ALL is used. If UNION without ALL is used, duplicate removal occurs and the value of FOUND_ROWS() is only approximate.

• If no LIMIT is present in the UNION, SQL_CALC_FOUND_ROWS is ignored and returns the number of rows in the temporary table that is created to process the UNION.

Important

FOUND_ROWS() is not replicated reliably, and should not be used with databases that are to be replicated.

 

limit 사용시 페이징 쿼리를 할 때, 게시물의 전체 개수를 알아야 한다면 쿼리를 두번을 해야한다.

1. 해당 조건을 만족하는 게시물의 전체 수를 가져오는 쿼리

2. 그 페이지에서 보여줄 내용(제목, 작성자 등등..)을 가져오는 쿼리

 

여기서 조건이 복잡하거나 이런저런 상황(?)이 안좋을 경우, 쿼리를 두 번 날리는 것은 상당히 괴롭다.

여기서 사용할 수가 있는것이 MySQL 이 제공하는 function 이다.

 

페이징에서 쓸 수 있는 function 은 

select 시에 SQL_CALC_FOUND_ROWS  라는 option 을 준 후 간단히 가져올 수 있는FOUND_ROWS() 이다.

(사실상 쿼리는 두번이지만,  이 두번째 쿼리는 눈 깜짝할 사이에 이루어 진다.)

 

 

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

이렇게 하면 첫번째 쿼리로 10 rows의 데이터가 나오고 두번째 쿼리로 where절을 만족하는 개수가 나온다.   

ㅁ                                                                                               ㅁ                                                                                                            [출처] https://m.blog.naver.com/PostView.nhn?blogId=ukyo39&logNo=20125862385&proxyReferer=https:%2F%2Fwww.google.com%2F

MySQL 게시판 limit 사용시 쿼리 한번에 페이징하기 - SQL_CALC_FOUND_ROWS & FOUND_ROWS