Caching query results

Submitted by clare on Wed, 2011-07-06 13:25 in Price Tapestry

Hi,
In search.php there are several SELECT statements and I was wondering of any of them are in particular called for the query to find all products from a merchant (eg site.com/merchant/argos/) or likewise the brand and category searches. Whether there is a one in particular statement that is called when the query is a search from the search box, rather than the merchant/brand/category search that return all that merchant/brand/category products.

the reason is because I was thinking to only cache the merchant/brand/category queries, as those results are always the same, whereas a search from the product search box, is unlikely to be called regularly and may take up cache space that the other queries could use.

I was just going to put SQL_CACHE after SELECT in the statements that would be relevant.

Do you think this this going to be possible, or are the select statements structured in a way that would make it difficult?

Also if this is a really bad idea, then please say, as I am kind of experimenting and am not sure whether this may have some effect I am not aware of.

Hi Clare, Sure that would be

Submitted by support on Wed, 2011-07-06 13:34

Hi Clare,

Sure that would be possible - the merchant/category/brand queries generated by search.php are segregated from normal user queries, so the SQL_CACHE flag can be added. Within that file, you'll find a switch() statement beginning at line 71 where the search SQL is constructed depending on the query type, and the first case is:

      case "merchant":

        // pass through to category

      case "category":

        // pass through to brand

      case "brand":

Scroll down for the $sql construction relevant to that section, beginning:

$sql = "SELECT SQL_CALC_FOUND_ROWS

(line 108 in 12/10A distribution)

...and experiment replacing with:

$sql = "SELECT SQL_CACHE SQL_CALC_FOUND_ROWS

However....

MySQL is often configured to cache by default, so the corollary to the above is actually to instruct user entered queries not to be cached. If this is the case, then instead of the above modification look for the default: section in the same case() statement, scroll down for the $sql construction relevant to that section, beginning:

$sql = "SELECT SQL_CALC_FOUND_ROWS

(line 253 in 12/10A distribution)

...and experiment replacing with:

$sql = "SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS

Cheers,
David.
--
PriceTapestry.com

Thanks very much David, I

Submitted by clare on Wed, 2011-07-06 13:43

Thanks very much David, I will give that a go.

Hi David, I just want to

Submitted by clare on Fri, 2011-07-08 08:30

Hi David, I just want to check whether this select statement would be a user entered search term that I might SQL_NO_CACHE or not...

(line 163 search.php 12/10A distribution)

 case "bw":

        $where = "search_name LIKE '".database_safe($parts[1])."%'";

        $sql = "SELECT SQL_CALC_FOUND_ROWS * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE ".$where.$priceWhere." GROUP BY name";

Hi Clare, There aren't

Submitted by support on Fri, 2011-07-08 08:35

Hi Clare,

There aren't actually any links into bw: searches, it's normally used where people want to create A-Z of products (so you link to bw:A) or an all products search (bw:) so shouldn't ever be a user-entered query...

Cheers,
David.
--
PriceTapestry.com

Ah that is actually the one

Submitted by clare on Fri, 2011-07-08 09:49

Ah that is actually the one i need to cache, so thanks, as I got it into my head it was a search term query and had no cached it.

I have now just done it exactly as you said to start with!

thanks