COUNT 쿼리 속도개선(메모)
마스터욱
0
86
0
0
2025-03-28 12:11:11
핵심요점
1. COUNT도 LIMIT를 주면 빠르다는 것
2. EXPLAIN SELECT 를 하면 정확하지는 않지만, 대략적인 카운터를 구할수 있으면서 쿼리속도를 단축시켜줌.
$paging['total'] = 0;
$paging['tmpTotal'] = 0;
if($is_paging)
{
/*
$sql = "
SELECT COUNT(idx) AS CNT
FROM ".$this->table['train']."
";
$ttt = $this->lib['db']->query_assoc($sql);
$ttt = $ttt['CNT'];
//2025-03-21, 속도가 넘 느려서 LIMIT 추가
$sql = "
SELECT COUNT(T.idx) AS CNT
FROM ".$this->table['train']." AS T
LEFT JOIN ".$this->lib['member']->table['member']." AS M3
ON M3.agency_master_hphone = T.agency_master_hphone
WHERE ".$where."
LIMIT ".$ttt."
";
$data = $this->lib['db']->query_assoc($sql);
$paging['total'] = $data['CNT'];
$paging['tmpTotal'] = $data['CNT'];
*/
/*
EXPLAIN SELECT idx FROM train WHERE {조건};
*/
$sql = "
EXPLAIN
SELECT idx
FROM ".$this->table['train']." AS T
WHERE ".$where."
";
$ttt = $this->lib['db']->query_assoc($sql);
if(DEV){
//print_r2($ttt);
}
$tmp = explode(" ", $ttt['rows']);
$paging['total'] = $tmp[0];
$paging['tmpTotal'] = $tmp[0];
}
