Author Topic: related products is slow.  (Read 2045 times)

stinga

  • Contributing Developer
  • Full Member
  • *
  • Posts: 872
    • Squangle ltd
related products is slow.
« on: June 25, 2012, 10:12:10 am »
I changed
Code: [Select]

// Let's have a look wether the product has related products.

$q = "SELECT product_sku, related_products FROM #__{vm}_product,#__{vm}_product_relations ";
$q .= "WHERE #__{vm}_product_relations.product_id='$product_id' AND product_publish='Y' ";
//+
//stinga increased 4 related products to 6
//-
$q .= "AND FIND_IN_SET(#__{vm}_product.product_id, REPLACE(related_products, '|', ',' )) LIMIT 0, 6";
$db->query( $q );
to
Code: [Select]
// Let's have a look wether the product has related products.

$q = "select replace(pr.related_products,'|',',') related_products from #__{vm}_product_relations pr, #__{vm}_product p  where p.product_id='$product_id' and pr.product_id = p.product_id and p.product_publish='y'";
$db->query($q);
$db->next_record();

$q = "select product_sku, related_products FROM #__{vm}_product p, #__{vm}_product_relations pr WHERE pr.product_id in (". $db->f('related_products').") AND product_publish='Y' and pr.product_id=p.product_id limit 50";

//$q = "SELECT product_sku, related_products FROM #__{vm}_product,#__{vm}_product_relations ";
//$q .= "WHERE #__{vm}_product_relations.product_id='$product_id' AND product_publish='Y' ";
//+
//stinga increased 4 related products to 6
//-
//$q .= "AND FIND_IN_SET(#__{vm}_product.product_id, REPLACE(related_products, '|', ',' )) LIMIT 0, 6";
$db->query( $q );

With the 391539 prducts we have, it was taking just over a second to process. index.php is now generated in under 1 second, usually 0.4 of a second. Which is a long way from the 10 seconds plus it used to take.
Stinga.
614869 products in 747 categories with 15749 products in 1 category.
                                             Document Complete   Fully Loaded
                Load Time First Byte Start Render   Time      Requests      Time      Requests
First View     2.470s     0.635s     1.276s          2.470s       31            2.470s      31
Repeat View  1.064s     0.561s     1.100s          1.064s       4             1.221s       4