You are here:  » Creating a category and brand cache for very large sites

Active Forum Topics


Creating a category and brand cache for very large sites

Submitted by support on Sun, 2007-09-23 09:21 in

Hi everyone,

If you run a single very large Price Tapestry installation, you may reach a point where the category and brand index pages become a bit slow to load. This is because the query used to create the pages has to perform a complete table scan of the products table, and then sort the results, which for hundreds of thousands of products can take several seconds.

A simple solution that doesn't require too much modification is to create a couple of new tables in the database to hold a "cache" of the latest category and brand indexes; and then use a query against this table when displaying the category and brand index pages instead of the massive products table! The result should be an almost instantaneous display of the index pages.

Firstly, cache tables need to be built. This only needs to be done once. To create the tables, create the following script in the scripts/ directory of your Price Tapestry installation:

scripts/buildcachetables.php

<?php
  set_time_limit
(0);
  require(
"../includes/common.php");
  
$sql "DROP TABLE IF EXISTS `".$config_databaseTablePrefix."category_cache`";
  
database_queryModify($sql,$result);
  
$sql =
"
CREATE TABLE `"
.$config_databaseTablePrefix."category_cache` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`category` VARCHAR( 255 ) NOT NULL ,
KEY `category` (`category`)
) ENGINE = MYISAM DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
"
;
  
database_queryModify($sql,$result);
  
$sql "DROP TABLE IF EXISTS `".$config_databaseTablePrefix."brand_cache`";
  
database_queryModify($sql,$result);
  
$sql =
"
CREATE TABLE `"
.$config_databaseTablePrefix."brand_cache` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`brand` VARCHAR( 255 ) NOT NULL ,
KEY `brand` (`brand`)
) ENGINE = MYISAM DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
"
;
  
database_queryModify($sql,$result);
?>

Now, to build the tables, simply run this script just as you would import.php or any of the other automation scripts, for example:

$cd scripts
$php buildcachetables.php

Secondly, you need to create a new script to populate these new cache tables. Again, create the following script in your scripts/ directory:

scripts/buildcache.php

<?php
  set_time_limit
(0);
  require(
"../includes/common.php");
  print 
"Building category_cache table...";
  
$sql "TRUNCATE `".$config_databaseTablePrefix."category_cache`";
  
database_queryModify($sql,$result);
  
$sql "SELECT DISTINCT(category) as category FROM `".$config_databaseTablePrefix."products` ORDER BY category";
  if (
database_querySelect($sql,$rows))
  {
    foreach(
$rows as $product)
    {
      if (
$product["category"])
      {
        
$sql "INSERT INTO `".$config_databaseTablePrefix."category_cache` SET category='".database_safe($product["category"])."'";
        
database_queryModify($sql,$result);
      }
    }
  }
  print 
"done.\n";
  print 
"Building brand_cache table...";
  
$sql "TRUNCATE `".$config_databaseTablePrefix."brand_cache`";
  
database_queryModify($sql,$result);
  
$sql "SELECT DISTINCT(brand) as brand FROM `".$config_databaseTablePrefix."products` ORDER BY brand";
  if (
database_querySelect($sql,$rows))
  {
    foreach(
$rows as $product)
    {
      if (
$product["brand"])
      {
        
$sql "INSERT INTO `".$config_databaseTablePrefix."brand_cache` SET brand='".database_safe($product["brand"])."'";
        
database_queryModify($sql,$result);
      }
    }
  }
  print 
"done.\n";
?>

Then you can populate the cache tables by running the above script from the command line in the /scripts/ folder e.g.

php buildcache.php

The command can be included in your CRON job by appending to the existing CRON command (as generated by Setup > CRON since 14/06A) with the addition of:

;php buildcache.php

(the semi-colon character enables multiple commands to be chained into a single command line)

Finally, simple changes have to be made to the category and brand pages to use the new cache table instead of querying the products table. In categories.php, look for the following code on line 70;

$sql = "SELECT DISTINCT(category) as category FROM `".$config_databaseTablePrefix."products` ORDER BY category";

...and change this to:

$sql = "SELECT category FROM `".$config_databaseTablePrefix."category_cache` ORDER BY category";

Similarly, in brands.php, look for the following code on line 6;

$sql = "SELECT DISTINCT(brand) as brand FROM `".$config_databaseTablePrefix."products` ORDER BY brand";

...and change this to:

$sql = "SELECT brand FROM `".$config_databaseTablePrefix."brand_cache` ORDER BY brand";

Cheers,
David.

Submitted by AD_Mega on Sun, 2007-09-23 09:55

Do you have to import all the datafeeds after you do this? I have nothing in categories or brand now.

Submitted by support on Sun, 2007-09-23 09:59

You don't have to import, no - the only thing you have to do to load the cache tables is run the buildcache.php script.

If you have run that script, do you have a tool such as phpMyAdmin that you can use to double check the changes that have been made to the database? Go into your Price Tapestry database, and make sure that the new tables brand_cache and category_cache exist. The, check their structure (they should have an id field and a category/brand field...

Does everything look correct in the database?

Cheers,
David.

Submitted by AD_Mega on Sun, 2007-09-23 10:05

I got it to work thanks!

Submitted by AD_Mega on Mon, 2007-09-24 12:43

I just imported some datafeed and now brand isn't working again. I guess its too large.

Submitted by support on Mon, 2007-09-24 12:47

Hi,

Do you mean that the buildcache.php script failed? If you don't run this script after importing, the brand and category pages should be exactly as they were before the import. Can you let me know which part is failing and what error you are getting?

Cheers,
David.

Submitted by AD_Mega on Mon, 2007-09-24 12:56

I ran buildcache.php and it ran fine. When I click on brands it hangs and my browser is non responding.

Submitted by support on Mon, 2007-09-24 13:01

Hi,

Perhaps a non-buffered query is required for the number of brands you have on your server. Can you drop me an email with your current brands.php as an attachment and i'll re-write it to use non-buffered queries....

Cheers,
David.

Submitted by tbbd2007 on Thu, 2008-03-27 16:53

David,

As my main site now has over 4,000,000 products I would like to cache all the big database quieries into smaller tables that will be updated every night. I am assuming that this will speed up the page loading times as the homepage can take up to 45 seconds to load and sort the top 10 products drawn directly from the products table. Unfortunately I have found that with my host I am unable to manipulate any MySQL settings to increase performance. Can you please write me a modified version of the two scripts above to cache the brand, category, merchant and top 10 products to new tables and update them daily.

Kind regards

Stephen
Online Shopping For
The Big Business Directory

Submitted by support on Thu, 2008-03-27 16:55

Hello Stephen,

Do you have a mechanism to run cron jobs daily?

Cheers,
David.

Submitted by tbbd2007 on Thu, 2008-03-27 19:18

David,

Yes, I am running several cron jobs a day.

Kind regards

Stephen
Online Shopping For
The Big Business Directory

Submitted by tbbd2007 on Mon, 2008-04-07 09:54

David,

Any update on this yet?

Kind regards,

Stephen
Online Shopping For
The Big Business Directory

Submitted by support on Mon, 2008-04-07 10:08

Hi Stephen,

I'm sorry this is most just questions but it's quite a complex task i'm afraid.

- Have you already implemented the above caching technique for the category and brand tables?

- I see that /merchants/ is taking a long time (as you mention). Have you modified your site to import multiple merchants from one feed, and so the merchants query is having to read the products table instead of the feeds table (which should be very quick)?

Cheers,
David.

Submitted by tbbd2007 on Sat, 2008-04-26 13:28

David,

Yes, I have now done that. Is it at all possible to create a 'top_product_cache' table which updates daily by deleting it's previous content and then inserting the top 10 products by number of clicks. Then I can amend the homepage to pull data from the 'top_products_cache' table instead of trawling through all 4.4M products every time the homepage is loaded.

Kind regards,

Stephen
Online Shopping For
The Big Business Directory

Submitted by support on Mon, 2008-04-28 08:28

Hi Stephen,

Have you implemented any method of counting product clicks as yet? Note that it wouldn't be advisable to do this in the products table as it is emptied for each merchant during import; so any click count in that table would be lost.

There is is code in the following thread that describes a table structure for creating a product count table; together with the mods required in jump.php to update the new table, but as it is a bit convoluted with other info i'll describe the changes again here.

Basically, a new table would be required products_clickswith the following structure:

id - INT(11), autoincrement, Primary Key
name - VARCHAR(255), Indexed
clicks - INT(11)

Then, use a new version of jump.php as follows:

<?php
  
require("includes/common.php");
  
$sql "SELECT name,merchant,buy_url FROM `".$config_databaseTablePrefix."products` WHERE id=".database_safe($_GET["id"]);
  
database_querySelect($sql,$rows);
  
$product $rows[0];
  
$sql "UPDATE `".$config_databaseTablePrefix."feeds` SET clicks=clicks+1 WHERE merchant = '".$product["merchant"]."'";
  
database_queryModify($sql,$insertID);
  
$sql "UPDATE `".$config_databaseTablePrefix."products_clicks` SET clicks=clicks+1 WHERE name = '".$product["name"]."'";
  if (!
database_queryModify($sql,$result)) // returns number of affected rows, so INSERT if zero
  
{
    
$sql "INSERT INTO `".$config_databaseTablePrefix."products_clicks` SET clicks=1,name='".database_safe($product["name"])."'";
    
database_queryModify($sql,$result);
  }
  
header("Location: ".$product["buy_url"]);
  exit();
?>

This is the method described in the other thread; but there could of course be performance issues with 4 million+ products; so if this turns out to be unacceptable it may be better to revert to counting clicks in the products table itself (which can be updated by id, the primary key) and then to update the products_clicks table in a single process at the beginning of the import process. Your top_product_cache table can then be created as follows (same structure as before):

id - INT(11), autoincrement, Primary Key
name - VARCHAR(255), Indexed
clicks - INT(11)

...and this can be updated daily by the following script (running in the /scripts/ directory):

<?php
  set_time_limit
(0);
  require(
"../includes/common.php");
  
$sql "TRUNCATE `".$config_database_tablePrefix."top_product_cache`";
  
database_queryModify($sql,$result);
  
$sql "SELECT * FROM `".$config_database_tablePrefix."products_clicks` ORDER BY clicks DESC LIMIT 10";
  if (
database_querySelect($sql,$products))
  {
    foreach(
$products as $product)
    {
      
$sql "INSERT INTO `".$config_database_tablePrefix."products_clicks` SET clicks=".$product["clicks"].",name='".database_safe($product["name"])."'";
      
database_queryModify($sql,$result);
    }
  }
?>

Finally, to display the products on the homepage, simply use:

<?php
  $sql 
"SELECT * FROM `".$config_database_tablePrefix."top_product_cache` ORDER BY clicks DESC";
  if (
database_querySelect($sql,$products))
  {
    print 
"<ul>";
    foreach(
$products as $product)
    {
      if (
$config_useRewrite)
      {
        
$productHREF $config_baseHREF."product/".tapestry_hyphenate($product["name"]).".html";
      }
      else
      {
        
$productHREF $config_baseHREF."products.php?q=".urlencode($product["name"]);
      }
      print 
"<li><a href='".$productHREF."'>".$product["name"]."</a></li>";
    }
    print 
"</ul>";
  }
?>

Hope this helps!

Cheers,
David.

Submitted by tbbd2007 on Tue, 2008-04-29 18:54

David,

Thanks for that, I will see how it performs.

Kind regards,

Stephen
Online Shopping For
The Big Business Directory

Submitted by tbbd2007 on Wed, 2008-05-07 21:30

David,

I am getting the following error message 'Parse error: syntax error, unexpected '}' in /homepages/34/d233391694/htdocs/osf/co.uk/jump-test.php on line 13'. How do I rectify that?

Kind regards,

Stephen
Online Shopping For
The Big Business Directory

Submitted by support on Thu, 2008-05-08 07:21

Hi Stephen,

My apologies; there was a semi-colon missing in the code above (end of line 12) - i've fixed this in the above post now.

Cheers,
David.

Submitted by paul30 on Sun, 2008-09-21 09:19

Wow this improves speed quite a bit (I currently have around 70.000 products and can see a BIG difference) - so for optimum performance is it recommended to have less products per category and in the same time more categories OR more products in each category and less overall categories? - Also is there any tips to tweak the database for even faster performance? And since Categories and Brands are performing a lot faster is there a mod to make product pages load also faster? - when clicked from category pages, sometimes it takes a second or 2 for the product to load and I currently have no traffic on the site yet.

Thanks a lot!

Submitted by support on Mon, 2008-09-22 08:36

Hi Pasha,

Even when using the cache, faster search results would be found with less products and more categories; as the SQL has to do a SELECT from the products table based on the category field - which although it is indexed because the query also contains a SORT clause every product in that category must be inspected by the database.

Regarding the page load time - could you perhaps email me a couple of links to slow loading pages and I'll check them out - i've seen this occur on some installations where long product names containing lots of short words, so there may be some opitimisations that could be done...

Cheers,
David.

Submitted by paul30 on Mon, 2008-09-22 09:10

To tell you the truth I can't as it happens random and I could not find a pattern to replicate the issue...

Most of the time the products load with 0.5 to 1 second delay (if you visit the product more than once it loads instantly the second time and after) but sometime it takes around 3 o 7 seconds for a product to load, again, visiting the same product for the second time will get you to the same page instantly.

Regarding the long names, I dont know if it is related at all, but I do have a pretty long names for around 35% (or more) of the products (is there anything I can do about it?)

An example of one of the products with a long name is :

Hal-Leonard-Jazz-Play-Along-Series-Volume-40-Bossa-Nova-10-Latin-Jazz-Favorites-BookCD-Standard.html

Thanks in advance.

Pasha

Submitted by cq on Mon, 2008-09-22 17:40

Hi David,

I don't quite understand you said run the below scripts. Can you explain further.

$cd scripts
$php buildcachetables.php

$cd scripts
$php buildcache.php

Is it http://www.myurl.com/buildcachetables.php

Secondly, is it to say that i need to reinstalled the feeds or reimporting the feeds in the admin section.

Thirdly, in the categories.php and brands.php, this quite simple. ok no problem. Is it the same for merchants.php if i want to speed up.

Fourthly, like the below example. Are there any changes to be made.

  print "<table width='728' border='0' cellspacing='0' cellpadding='2'>";
  print "<tr width='182'>";
  $sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE category = 'abc' ORDER BY RAND() LIMIT 1";
  database_querySelect($sql,$products);
  foreach($products as $product)
  {
  if ($product["image_url"])
  {
    $productHREF = $config_baseHREF."product/".str_replace(" ","-",$product["name"]).".html";
    print "<td align='center' width='182'>";
    print "<br/>";
    print "<a href='".$productHREF."' ><img width='120' height='120' border=0 src='".$product["image_url"]."' alt='".$product["name"] ."' /></a><br>";
    print "<br/>";
    print "<font size=1> <a href='".$productHREF."'> ".$product["name"]."</a><br>";
    print "<br/>";
    print $config_currencyHTML.$product["price"]." <br /></font>\n";
    print "<br/>";
?>
<?php
    print "</td>";
  }
  else
  {
    print "<td>&nbsp;</td>";
  }
}
print "</tr>";

Lastly, i read from the comments, multiple merchants one feed and cron job. What is the functions. I don't understand. Glad if you can explain.

many thanks

cheers
jack

Submitted by support on Tue, 2008-09-23 07:32

Hello Jack,

The scripts to build the cache tables can be run from the browser if you wish; just like the URL you posted. However; the ideal way to run them is from the command line. Where you see this:

$cd scripts
$php buildcachetables.php

...these are commands to be typed in when you are logged in to your server, either using Telnet or more likely SSH. If you are not sure, ask your host if you have command line access. If you are able to login, the "$" in the above commands is not something you type, but is the "Command Prompt" (like c:\> on a Windows computer), so you just go straight to typing in "cd script" - or whatever path you need to take to get to the scripts directory.

For example, on a lot of hosting accounts, your "home" directory is one level above the website directory - which might be in a folder called "public_html" (or similar). So, in this case, the first command would actually be:

$cd public_html/scripts

...and then you can run the script using the php command.

There is no benefit to speeding up the merchant index in the same way; as it is already a fully optimised SELECT from a single database table.

Regarding "multiple merchants", I have a patch available that allows you to register a field containing the merchant name for use with feeds that have more than one merchant. If you want this patch, let me know and I will email it to you...

Cheers,
David.

Submitted by cq on Tue, 2008-09-23 08:16

Hi David,

I ran the buildcachetables.php. Then buildcache.php. Login to phpmyadmin saw the cache table for brand and category. Then i changed the line in brands.php and categories.php. But when i browsed mysite both the brand and category are empty.

I tried again this time created a new feed and do a registered and full import in admin. Thereafter i ran buildcache.php. Still empty.

Can you let me know if it is correct steps.

thanks
jack

Submitted by support on Tue, 2008-09-23 09:48

Hello Jack,

When you login to MySQL and you see the tables, after running the scripts to build the cache, can you check the tables to see if they have data?

If they do not, let me know and if you could email me your buildcache.php and modified brands.php and categories.php i'll check them out for you...

Cheers,
David.

Submitted by cq on Tue, 2008-09-23 10:58

Hi David,

I will email you in shortwhile.

thanks
jack

Submitted by cq on Tue, 2008-09-23 11:03

Hello David,

I don't thing seeing any data in the database.

thanks
jack

Submitted by support on Tue, 2008-09-23 11:09

Thanks, Jack - I have replied via email.

Cheers,
David.

Submitted by cq on Tue, 2008-09-23 11:23

Hi David,

It works already. Many thanks. I think i forgot to upload the buildcache.php file in scripts/buildcache.php. Anyway i upload the buildcache.php file to the location. Then change the line in brands.php and categories.php as well. Then upload those files to the server. Then run url/buildcache.php. It shows the results in one line how many category and brands.

Everything works fine.

thank you so much.

cheers
jack

Submitted by paul30 on Wed, 2008-09-24 20:01

I am sorry for the possibly "stupid" question but:

What should be "routine" when re-importing feeds (or adding merchants) with this mod applied?

I mean since the categories and brands are cached, do we need to first delete the cached tables prior importing a new merchant? (Or lets say I made some more mappings and need the changes to be visible)

Also is it normal that when you delete and than de-register a feed, reviews, category_cache and brand_cache tables dont delete anything?

Thanks in advance.
Pasha

Submitted by support on Thu, 2008-09-25 07:20

Hi Pasha,

I would re-run the build cache scripts after every import of all feeds (i.e if you update and import all feeds at the same time; do the build cache once after all feeds have been imported - no need to do it after every feed).

The cache tables would not be updated automated when de-registering; and reviews will remain in the database ready to be "back-filled" back into those products should they re-appear in the future...

Cheers,
David.

Submitted by clare on Fri, 2008-10-10 12:55

When using this buildcache script, would it make a difference if Rewrite is on?

Using the buildcache, my category urls are coming out wrong, writing site.com/electricals/ instead of site.com/category/electricals/, and I wondered if I have to put ($config_useRewrite) somewhere in the above code?

Submitted by support on Sat, 2008-10-11 08:12

Hi Clare,

It shouldn't make any difference - all that changes is how the category is selected from the database - there's no changes to code generating the links..

Feel free to email me a copy of your categories.php and i'll check it over for you...

Cheers,
David.

Submitted by webie on Wed, 2009-07-15 11:24

Hi Dave,

Is it possible to adjust categories cache so we can add product count to this tables and insert total number of product per catgory.

and the same for brand and merchant cache as well?

Kind regards

Darren

Submitted by support on Wed, 2009-07-15 11:58

Hi Darren,

Yes - that should be straight forward. Could you perhaps email me your cache build script so that I can see what you're working with at the moment and I'll recommend what to add...

Cheers,
David.

Submitted by stevebi on Mon, 2015-04-20 00:50

Hello David,

May I ask if the above modification can be supported by version 14/06A and especially Category Hierarchy?

Cheers

S

Submitted by support on Mon, 2015-04-20 09:09

Hello Steve,

I've updated the first past with relevant line numbers up to 15/01A (including LE) but this is for basic (feed) Category A-Z only, Category Hierarchy works quite differently, and since it is not generated from the `products` table would not benefit from caching as the hierarchy is stored optimally anyway, however as you know I'm looking at re-working the Category Hierarchy code to better support larger hierarchies so if you are currently experiencing a slowing of your Category A-Z as your hierarchy grows you should see an improvement once the codebase has been updated.

Cheers,
David.
--
PriceTapestry.com

Submitted by stevebi on Mon, 2015-04-20 09:38

Thank you David,

I will be waiting for the rework of Category Hieracrhy.

Concerning merchants page which has the logos also does it worth any cache?

Cheers

S

Submitted by support on Mon, 2015-04-20 10:02

Hi Steve,

You could experiment with merchant cacheing, the modified scripts/buildcachetables.php to add merchant_cache would be as follows;

<?php
  set_time_limit
(0);
  require(
"../includes/common.php");
  
$sql =
"
CREATE TABLE `"
.$config_databaseTablePrefix."category_cache` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`category` VARCHAR( 255 ) NOT NULL
) ENGINE = MYISAM ;
"
;
  
database_queryModify($sql,$result);
  
$sql =
"
CREATE TABLE `"
.$config_databaseTablePrefix."brand_cache` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`brand` VARCHAR( 255 ) NOT NULL
) ENGINE = MYISAM ;
"
;
  
database_queryModify($sql,$result);
  
$sql =
"
CREATE TABLE `"
.$config_databaseTablePrefix."merchant_cache` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`merchant` VARCHAR( 255 ) NOT NULL
) ENGINE = MYISAM ;
"
;
  
database_queryModify($sql,$result);
?>

And scripts/buildcache.php

<?php
  set_time_limit
(0);
  require(
"../includes/common.php");
  print 
"Building category_cache table...";
  
$sql "TRUNCATE `".$config_databaseTablePrefix."category_cache`";
  
database_queryModify($sql,$result);
  
$sql "SELECT DISTINCT(category) as category FROM `".$config_databaseTablePrefix."products` ORDER BY category";
  if (
database_querySelect($sql,$rows))
  {
    foreach(
$rows as $product)
    {
      if (
$product["category"])
      {
        
$sql "INSERT INTO `".$config_databaseTablePrefix."category_cache` SET category='".database_safe($product["category"])."'";
        
database_queryModify($sql,$result);
      }
    }
  }
  print 
"done.\n";
  print 
"Building brand_cache table...";
  
$sql "TRUNCATE `".$config_databaseTablePrefix."brand_cache`";
  
database_queryModify($sql,$result);
  
$sql "SELECT DISTINCT(brand) as brand FROM `".$config_databaseTablePrefix."products` ORDER BY brand";
  if (
database_querySelect($sql,$rows))
  {
    foreach(
$rows as $product)
    {
      if (
$product["brand"])
      {
        
$sql "INSERT INTO `".$config_databaseTablePrefix."brand_cache` SET brand='".database_safe($product["brand"])."'";
        
database_queryModify($sql,$result);
      }
    }
  }
  print 
"Building merchant_cache table...";
  
$sql "TRUNCATE `".$config_databaseTablePrefix."merchant_cache`";
  
database_queryModify($sql,$result);
  
$sql "SELECT DISTINCT(merchant) as merchant FROM `".$config_databaseTablePrefix."products` ORDER BY merchant";
  if (
database_querySelect($sql,$rows))
  {
    foreach(
$rows as $product)
    {
      
$sql "INSERT INTO `".$config_databaseTablePrefix."merchant_cache` SET merchant='".database_safe($product["merchant"])."'";
      
database_queryModify($sql,$result);
    }
  }
  print 
"done.\n";
?>

And for display, in merchants.php line 6:

$sql = "SELECT DISTINCT(merchant) as merchant FROM `".$config_databaseTablePrefix."products` ORDER BY merchant";

...and change this to:

$sql = "SELECT merchant FROM `".$config_databaseTablePrefix."merchant_cache` ORDER BY merchant";

Cheers,
David.
--
PriceTapestry.com

Submitted by sirmanu on Tue, 2016-10-11 09:46

Is any way that this script could work with sql from searchfilters.php?

PD:

$sql1 = "SELECT DISTINCT(category) FROM `".$config_databaseTablePrefix."products` WHERE ".$where.$priceWhere." AND category <> '' ORDER BY category";

$sql1 = "SELECT DISTINCT(brand) FROM `".$config_databaseTablePrefix."products` WHERE ".$where.$priceWhere." AND brand <> '' ORDER BY brand";

and so on..

Submitted by support on Tue, 2016-10-11 10:14

Hello sirmanu,

I don't think the cache tables would be of any benefit to filters I'm afraid since the search WHERE clause in effect needs to be applied.

(PS. I have just updated the buildcachetables.php script at the top of this thread to include DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci)

Cheers,
David.
--
PriceTapestry.com

Submitted by macland on Thu, 2020-05-07 11:31

Hi I think I could use this fix since right now my categories are loaded from the +1,8 million big tables of products.

I can see the last reply is some years old, so before I update I just wanted to check if this update is still compatible for current versions? (I use the sub-pages mod you made in node 6121)

Thanks in advance :-)

Submitted by support on Thu, 2020-05-07 13:00

Hi,

Yes, above is all still compatible, I updated the line number to modify in categories.php for 18/10A (as the script now includes category hierarchy support) otherwise, once all set-up just chain buildcache.php with your existing cronjob e.g.

cd /path/to/scripts;/usr/bin/php cron.php;/usr/bin/php buildcache.php

Regarding the sub-pages (node 6121) in brandsIndex.php where you have the following code at line 4:

  $sql = "SELECT DISTINCT(UCASE(SUBSTRING(brand,1,1))) as letter FROM `".$config_databaseTablePrefix."products` ORDER BY brand";

...REPLACE with:

  $sql = "SELECT DISTINCT(UCASE(SUBSTRING(brand,1,1))) as letter FROM `".$config_databaseTablePrefix."brand_cache` ORDER BY brand";

And similarly in categoriesIndex.php where you have the following code at line 4:

  $sql = "SELECT DISTINCT(UCASE(SUBSTRING(category,1,1))) as letter FROM `".$config_databaseTablePrefix."products` ORDER BY category";

...REPLACE with:

  $sql = "SELECT DISTINCT(UCASE(SUBSTRING(category,1,1))) as letter FROM `".$config_databaseTablePrefix."category_cache` ORDER BY category";

And then in brands.php where you have the following code at line 6:

  $sql = "SELECT DISTINCT(brand) as brand FROM `".$config_databaseTablePrefix."products` WHERE brand LIKE '".database_safe($_GET["letter"])."%' ORDER BY brand";

...REPLACE with:

  $sql = "SELECT brand FROM `".$config_databaseTablePrefix."brand_cache` WHERE brand LIKE '".database_safe($_GET["letter"])."%' ORDER BY brand";

And finally in categories.php where you have the following code at line 70:

      $sql = "SELECT DISTINCT(category) as category FROM `".$config_databaseTablePrefix."products` WHERE category LIKE '".database_safe($_GET["letter"])."%' ORDER BY category";

...REPLACE with:

      $sql = "SELECT category FROM `".$config_databaseTablePrefix."category_cache` WHERE category LIKE '".database_safe($_GET["letter"])."%' ORDER BY category";

Cheers,
David.
--
PriceTapestry.com

Submitted by macland on Wed, 2020-05-13 10:10

Thank you very much David, works like a charm :-)

One small thing. Right now I'm only showing categories and brands with 50 or more products in them, in order to show a bit fewer brands.

So my category-sql is like this:

$sql = "SELECT category,COUNTDISTINCT(name)) as numProducts FROM `".$config_databaseTablePrefix."products` WHERE category LIKE '".database_safe($_GET["letter"])."%' GROUP BY category HAVING numProducts >= 50 ORDER BY category";

Would it be possible to add a line of code somewhere in this process, fx. in buildcache.php to only include categories and brands with a certain minimum number of products?

Submitted by support on Wed, 2020-05-13 10:42

Hi,

Sure - you can essentially use almost exactly that query in buildcache.php - so for categories, where you have the following around line 7:

  $sql = "SELECT DISTINCT(category) as category FROM `".$config_databaseTablePrefix."products` ORDER BY category";

...REPLACE with:

  $sql = "SELECT category,COUNT(DISTINCT(name)) as numProducts FROM `".$config_databaseTablePrefix."products` WHERE category <> '' GROUP BY category HAVING numProducts >= 50";

And similarly for brands at line 23:

  $sql = "SELECT DISTINCT(brand) as brand FROM `".$config_databaseTablePrefix."products` ORDER BY brand";

...REPLACE with:

  $sql = "SELECT brand,COUNT(DISTINCT(name)) as numProducts FROM `".$config_databaseTablePrefix."products` WHERE brand <> '' GROUP BY brand HAVING numProducts >= 50";

Cheers,
David.
--
PriceTapestry.com

Submitted by macland on Thu, 2020-05-14 08:02

Fantastic, thank you so much :-D