I think this one officially qualifies as a hack, a piece of code to optimize a particular part of the webshop leaving most of the code untouched ... with a 'WorksForMe' warning
I've a working version, not yet totally clean, but it does reduce the number of queries from 249 to 79 on a page with a few filters, and from 298 to 128 on another page (with more filters)
Now the actual gain is more difficult to measure. Overall the number of queries is a lot less, but there is now a more complex query for which I'd hope the result is faster than all the individual small queries.
I'm hitting the same database, thinking about how to test this now, or what kind of monitoring I can put in place to measure this with life visitor data ...
Here is the code if you want to try this yourself - note this is a development thread, if these instructions are not clear to you, you should probably not be trying this ...
the code change in products_filter box is
ADD
if ($specs_array['products_column_name'] == 'manufacturers_id' and $specs_array['filter_class'] == 'exact') {
$filters_select_array = $spec_object->CB_getManufacturerFilterCount ($filter_id, $specs_array['specifications_id']);
} else {
BEFORE
$previous_filter = 0;
$previous_filter_id = 0;
ADD
}
BEFORE
$box_text_vars = tep_get_filter_string ($specs_array['filter_display'], $filters_select_array, FILENAME_PRODUCTS_FILTERS, $var, $$var);
In the specifications class file, add a new function, a trimmed down, modified getFilterCount function just for manufacturers
public function CB_getManufacturerFilterCount($specification, $specifications_id) {
global $filters_select_array;
global $filter_index;
$raw_query_from = " from (" . TABLE_PRODUCTS . " p)
join (" . TABLE_PRODUCTS_TO_CATEGORIES . " p2c)
on (p.products_id = p2c.products_id)
left join specials s on (p.products_id = s.products_id)
";
$raw_query_where = " where p.products_status = '1' ";
if ($this->current_category_id != 0) { // Restrict query to the appropriate category/categories
$subcategories_array = array();
tep_get_subcategories ($subcategories_array, $this->current_category_id);
if (SPECIFICATIONS_FILTER_SUBCATEGORIES == 'True' && count ($subcategories_array) > 0) {
$category_ids = $this->current_category_id . ',' . implode (',', $subcategories_array);
$raw_query_where .= ' ' . "and p2c.categories_id in (" . $category_ids . ") ";
} else {
$raw_query_where .= " and p2c.categories_id = '" . $this->current_category_id . "' ";
}
} // if ($this->current_category_id
$applied_filters = $this->getAppliedFilters();
foreach ($applied_filters as $k => $v) {
if ($k == $specifications_id) {
continue;
}
$specs_array = $this->getSpecification($k);
$raw_query_addon_array = tep_get_filter_sql($specs_array['filter_class'], $specs_array['specifications_id'], $v, $specs_array['products_column_name'], $this->languages_id);
$raw_query_from .= $raw_query_addon_array['from'];
$raw_query_where .= $raw_query_addon_array['where'];
} // foreach($applied_filters
if ($specification == '0') { // show_all
$raw_query_start = "select 0 as filter, count(p.products_id) as count ";
$raw_query_group_by = '';
} else {
$raw_query_start = "select manufacturers_name as filter, count(p.products_id) as count ";
$raw_query_from .= " join (" . TABLE_MANUFACTURERS . " m)
on (p.manufacturers_id = m.manufacturers_id)
";
$raw_query_group_by = ' group by manufacturers_name';
}
$raw_query = $raw_query_start . $raw_query_from . $raw_query_where . $raw_query_group_by;
//print 'Raw Query: ' . $raw_query . '<br>';
$filter_count_query = tep_db_query ($raw_query);
while ($filter_count_result = tep_db_fetch_array ($filter_count_query)) {
$filters_select_array[$filter_index] = array ('id' => $filter_count_result['filter'],
'text' => htmlspecialchars($filter_count_result['filter']),
'count' => (string) $filter_count_result['count']
);
$filter_index++;
}
return $filters_select_array;
}
4
