Tracking down where a specific query is built

[Noodleman]

i am in the process of tweeking my store to cope with a large volume of products. (DB currently is at 2.9 million).

 

I have everything working OK, with the exception of one last thing. After clicking a category for the first time, it can take a very long time to list the products. I was monitoring the databse using "show full processlist" and I noted that whenever there is a delay, it is caused by a count query. The specific query is as follows:

 

select
count(p.products_id) as total
from
products_description pd,
products p

left join manufacturers m
on p.manufacturers_id = m.manufacturers_id

left join specials s
on p.products_id = s.products_id,

products_to_categories p2c
where
p.products_status = '1'
and p.products_id = p2c.products_id
and pd.products_id = p2c.products_id
and pd.language_id = '1'
and p2c.categories_id = '73'

 

The query is gathering the count of products within the specific category. I assume for display purposes at the bottom of the page, and also for pagination. The only thing that changes is the categories_id value depending on the category selected. It is taking up to 30 seconds to return this value in some cateogories for the initial request (Before it gets cached). I want to modify the query to get the totals from a table which I maintain the product counts per category to make it much quicker.

 

I haven't been able to track down where the above query is being built/called from. Its the last one on my list.

 

I am using version 2.3.3.

 

can anybody offer any suggestions?

 

Thanks in advance,

 

Noodle

[Noodleman]

Nevermind.... I managed to figure it out.

 

the code I was looking for comes from includes\classes\split_page_results.php, function "splitPageResults".

 

I made the following modifications and it seems to work just fine. MUCH MUCH MUCH MUCH quicker now!

 

  //$count_query = tep_db_query("select count(" . $count_string . ") as total " . substr($this->sql_query, $pos_from, ($pos_to - $pos_from)));
  //$count = tep_db_fetch_array($count_query);


  // NOODLECODE Strip out the category ID from the query string
  $pos_fromcat = strrpos($this->sql_query, ' and p2c.categories_id = \'', 0);
  $ndl_str = substr($this->sql_query, ($pos_fromcat+26), $pos_to);
  $pos_tocat = strpos($ndl_str, '\'', 0);
  $ndl_str = substr($ndl_str, 0, $pos_tocat);

  // NOODLECODE Updated query to get the count
  $count_query = tep_db_query("SELECT count AS total FROM products_categories_count WHERE categories_id = " . $ndl_str);
  $count = tep_db_fetch_array($count_query);

[Noodleman]

I just realised that my earlier post broke the search queries, as these also use the same function to get a record count of the results from the search. Here is an updated modification to the function that makes it work correctly with search and category browsing.

 

only down side is that searches take a while, but this I can live with for now.

 

// NOODLECODE check if we are doing a search
IF(isset($HTTP_GET_VARS['keywords'])){
  $count_query = tep_db_query("select count(" . $count_string . ") as total " . substr($this->sql_query, $pos_from, ($pos_to - $pos_from)));
  $count = tep_db_fetch_array($count_query);
}ELSE{
// Weee, not searching so much be browsing
  // NOODLECODE Strip out the category ID from the query string
  $pos_fromcat = strrpos($this->sql_query, ' and p2c.categories_id = \'', 0);
  $ndl_str = substr($this->sql_query, ($pos_fromcat+26), $pos_to);
  $pos_tocat = strpos($ndl_str, '\'', 0);
  $ndl_str = substr($ndl_str, 0, $pos_tocat);

  // NOODLECODE Updated query to get the count
  $count_query = tep_db_query("SELECT count AS total FROM products_categories_count WHERE categories_id = " . $ndl_str);
  $count = tep_db_fetch_array($count_query);
}