DBMS) SQL_CALC_FOUND_ROWS

SQL_CALC_FOUND_ROWS란?

SQL_CALC_FOUND_ROWS는 MySQL에서 사용되는 쿼리 힌트로, SELECT문과 함께 사용하여 쿼리에서 페이징 처리를 할 때 유용합니다.

페이징 처리는 데이터베이스의 특정 범위 데이터를 가져오면서, 전체 데이터의 개수(totalCount)도 함께 구해야 할 때 자주 사용됩니다.

---

사용 목적

1. LIMIT로 제한된 데이터 가져오기:
   • 원하는 범위의 데이터만 가져오기 위해 LIMIT을 사용.
2. 전체 결과 개수 계산:
   • SQL_CALC_FOUND_ROWS를 사용하면 제한된 결과(LIMIT)뿐만 아니라 쿼리가 필터링된 전체 데이터 개수도 계산 가능.

---

사용 방법

1. 기본 쿼리:

   • 제한된 데이터를 가져오기 위해 LIMIT과 함께 사용.

   SELECT SQL_CALC_FOUND_ROWS column1, column2
   FROM table_name
   WHERE conditions
   LIMIT 10 OFFSET 0;

2. 전체 데이터 개수 가져오기:

   • 첫 번째 쿼리 직후 FOUND_ROWS()를 호출하여 전체 데이터 개수를 얻음.

   SELECT FOUND_ROWS();

---

예제

1. 페이징 처리 예제

-- Step 1: 데이터 가져오기 (LIMIT 적용)
SELECT SQL_CALC_FOUND_ROWS id, name
FROM users
WHERE age > 18
LIMIT 10 OFFSET 0;

-- Step 2: 전체 데이터 개수 가져오기
SELECT FOUND_ROWS();

결과

• 첫 번째 쿼리: 조건에 맞는 최대 10개의 데이터만 반환.
• 두 번째 쿼리: 조건에 맞는 전체 데이터 개수 반환.

---

장점

• 두 번의 쿼리를 작성하지 않고도 필터링된 데이터의 전체 개수와 제한된 결과를 함께 처리 가능.
• 페이징 처리 로직을 단순화할 수 있음.

---

단점

• 성능 문제:
  • 데이터 양이 많은 경우, SQL_CALC_FOUND_ROWS는 성능이 저하될 수 있음.
  • MySQL은 쿼리 실행 중 모든 결과를 계산한 뒤 일부 데이터만 반환하기 때문.
• 대안:
  • COUNT(*)를 사용하여 전체 개수를 별도로 계산:

    SELECT COUNT(*) FROM table_name WHERE conditions;

---

사용해야 할 때와 피해야 할 때

• 사용 권장:
  • 데이터의 양이 작고, 간단한 쿼리에서 페이징 처리를 구현할 때.
• 사용 지양:
  • 복잡한 쿼리나 대규모 데이터 처리에서는 성능 문제가 발생할 수 있으므로, 대신 COUNT(*)를 사용하는 것이 좋음.

---

결론

SQL_CALC_FOUND_ROWS는 페이징 처리와 같은 작업에서 편리하지만, 데이터베이스 성능 최적화 측면에서는 신중히 사용해야 합니다. 최신 MySQL 버전에서는 더 효율적인 방법이 권장되므로 상황에 따라 적절한 대안을 선택하는 것이 좋습니다.

---

실습

const allBooks = (req, res) => {
  let { category_id, newBook, limit, currentPage } = req.query;

  // limit : page당 보여줄 갯 수
  // currentPage : 현재 페이지(숫자)
  // offset : (current-1)*limit

  let offset = limit * (currentPage - 1);

  let sql = `SELECT *, (SELECT count(*) FROM likes WHERE liked_book_id = books.id) AS 'likes' FROM books `;
  let values = [];

  if (category_id && newBook) {
    // category별 신간
    sql += ` WHERE category_id = ? AND pub_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 month) AND NOW()`;
    values = [category_id];
  } else if (category_id) {
    // category별
    sql += ` WHERE category_id = ? `;
    values = [category_id];
  } else if (newBook) {
    // 신간
    sql += ' WHERE pub_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 month) AND NOW()';
  }

  sql += ` LIMIT ? OFFSET ?`;
  values.push(parseInt(limit), offset);

  conn.query(sql, values, (err, results) => {
    if (err) {
      console.error(err);
      return res.status(StatusCodes.BAD_GATEWAY).json({ error: '데이터베이스 오류입니다.' });
    }
    if (results.length) {
      return res.status(StatusCodes.OK).json({
        books: results,
        pagenation: { currentPage: currentPage },
      });
    } else {
      return res.status(StatusCodes.NOT_FOUND).end();
    }
  });
};

코드 상단에서 sql 변수에 SQL_CALC_FOUND_ROWS추가해야합니다.

let sql = SELECT SQL_CALC_FOUND_ROWS *, (SELECT count(*) 
					FROM likes 
					WHERE liked_book_id = books.id) AS 'likes' FROM books ;

또한 FOUND_ROWS() AS totalCount 로 통해서 SQL_CALC_FOUND_ROWS 데이터를 가져와야 합니다.