Record count in categories and filters - A neverending story - Cobalt 7

Quote

Total posts: 1,636
24 Nov 2012 07:47

With reference to the blog post of optimization and the attached comments as well as the discussion here i think there is a need to go over the issue of numbers in filters and categories again.

As I see it in the moment, we have to ways of counting.

1) The extensive counting, which takes everything in consideration like expired records, private records etc -- depending on the visiting user, computed on the record properties.

2) Fast counting to take all existing records per condition.

Following the 2 upper discussions, the counting in the filter links is based on method 2, while counting on categories is based on method 1).

I would suggest to add a counting for categories also based on condition 2) since i believe it depends on the case of the application.

I made some test with a section , having 53K records and 20 categories. for these tests, i used the display without records.

Including the existing category numbering it takes ~1.747 sec, excluding the numbering it takes ~1.145 sec. I modified the category count now with Method 2 and the same result including numbers could be achieved in ~1.165 sec. Actually the counting for the 20 categories takes only 0.02 sec. For me 1.1 against 1.7 is a big change.

Maybe adding the "fast count" to the category template would be a good idea.

But i also realized, that just the category display without records in the list (Parameter "Show records at section homepage" = "Do not show anything") is slower than a category with low number of records (< 1K) ... This shows that there is still quite some room for improvement.

Last Modified: 02 Mar 2014

Quote

stblink

Total posts: 501
11 Dec 2012 12:40

According to your calculations, yes, there is space for Cobalt speed improvement.

Is it possible for me to see your site that you mention? Would like to see it running with that many amount of records. You use shared hosting or have a VPS or Private Server ?

Quote

Sackgesicht VIP

Total posts: 1,636
11 Dec 2012 13:41

as of the moment, it is on a private server (i5 cpu) in an intranet setup.

I use a modified category index template for the category count. In my case. i dont have category levels.

This is the query:

$section=$cats_model->section->id;

$db = JFactory::getDbo () ;

$query = $db->getQuery ( true ) ;

$query->select ( "catid, COUNT(*) as number" ) ;

$query->from ( "#__js_res_record_category" ) ;

$query->where ( "section_id = $section" ) ;

$query->group('catid') ;

$db->setQuery ( $query ) ;

$num [] = $db->loadAssocList("catid","number") ;

i disabled the number display in the template parameter to avoid the "smart" counting and just added the result of the "fast" counting after the category title:

<a href="/<?php echo JRoute::_($category->link)?>"><?php echo $category->title; ?></a>

<?php if($params->get('tmpl_params.cat_nums') && $category->params->get('submission')):?>

    (<?php echo $category->records_num;?>)

<?php endif;?>

(<?php echo $num[0][$category->id];?>)

Quote

Sackgesicht VIP

Total posts: 1,636
11 Dec 2012 13:44

again the template code without smilies ..

<a href="/<?php echo JRoute::_($category->link)?>"><?php echo $category->title; ?></a>

<?php if($params->get('tmpl_params.cat_nums') && $category->params->get('submission')):?>

       (<?php echo $category->records_num; ?> )

<?php endif; ?>

(<?php echo $num[0][$category->id]; ?> )

Record count in categories and filters - A neverending story

Login (copy)