Donate
I have one question about pagination.
$config = array('pagination_url' => Uri::create('category/'.$category),
'uri_segment' => 2,
'total_items' => 4,
'per_page' => 4,
'current_page'=>1
);
Pagination::set_config($config);
$countitem = DB::query('
SELECT
count(itemid)
FROM
items
JOIN
category
ON
categoryurl = "'. $category .'"
AND
categoryid = itemcategoryid
AND
quantity >= 1
')->execute();
$config = array('pagination_url' => Uri::create('category/'.$category),
'uri_segment' => 2,
'total_items' => $countitem,
'per_page' => 4,
'current_page'=>1
);
Pagination::set_config($config);
$dbquery = DB::query('SELECT
categoryurl,
categoryname,
thumbnail,
itemid,
itemname,
newprice
FROM
category
JOIN
items
ON
categoryurl = "'. $category .'"
AND
categoryid = itemcategoryid
AND
quantity >=1
JOIN
photo
ON
itemid = photoitemid
LIMIT '. Pagination::$per_page .'
')->execute()->as_array();
if(Cache::get('countitem')){
$countitem = \Fuel\Core\Cache::get('countitem');
}else{
$countitem = \Fuel\Core\DB::query('
SELECT
count(itemid)
FROM
items
JOIN
category
ON
categoryurl = "'. $category .'"
AND
categoryid = itemcategoryid
AND
quantity >= 1
')->execute();
Cache::set('countitem',$countitem,3600*3);
}
$config = array('pagination_url' => Uri::create('category/'.$category),
'uri_segment' => 2,
'total_items' => $countitem,
'per_page' => 4,
'current_page'=>1
);
Harro Verton wrote on Monday 24th of October 2011:You will always need to run two queries, one to get the total count, and one to get the records of the current page. First thing I notice is that your two queries are not identical, so that can't be right. Only the select part should be different. Secondly, I don't see an offset, so you're always loading records starting from the first. As to the speed, in general code first, and optimize later. And before you optimize, analyse. Since you haven't got a clue why it is so much slower, why do you think caching will help?
Thank you !Harro Verton wrote on Tuesday 25th of October 2011:Caching might be a solution, but then again, it might not be. If you have a fast database server with lots of memory, and slow or busy local disks, caching might even slow things down. For me, optimizing an application is an iterative process. You start with the application, which should be coded properly (so any slowness is not due to sloppy coding). You then analyse the application, and make a (lets say) top 5 of speed issues. Optimize those. Then iterate until you're happy. If it turns out it's a query, then adding caching is as simple as encapsulating the query in a Cache::call().
Harro Verton wrote on Monday 24th of October 2011:You will always need to run two queries, one to get the total count, and one to get the records of the current page.
SELECT SQL_CALC_FOUND_ROWS * FROM table LIMIT 10 OFFSET 100
It looks like you're new here. If you want to get involved, click one of these buttons!
