Hi David,
how can I manage that the site is loaded quicker.
One of my sites has a big database of about 300,000 products from 8 merchants. Now when I surf to the site it lasts very long until the site is loaded. Other sites with a lower database not.I believe that the installing of random featured products is the reason why. Of course I could disable that feature, but that is not what I want. Could it be done that the site is first loaded and then the database is searched for the products, or is there a coding, that from eaxh merchent only the first product is token as featured product?
Regards
Roland
Hi DAvid,
yes I tried and found it verified. The site is loading at once if I do not have random featured products installed.
Regards
Roland
Hi Roland,
Ok - we'll need to look at another way of doing your random product selection. Bear with me and i'll look into other options...
Cheers,
David.
Hi David,
I will not be impatient just to ask you if you made any progress.
Regards
Roland
Hi Roland,
Would you be happy to i) pick a random merchant and then ii) pick random products from that merchant? This should be much quicker as you the merchant name is indexed by the database so the selection will be faster?
If this sounds OK, could you post the code you were using previously and i'll modify it for you to do this...
Cheers,
David.
I also removed the random freature from one of my sites that had about 500,000 items as it was so slow , my other sites have less than 50,000 and the mod works fine on them , is it possible to have the random feature but it only picks items from 2 merchants?
the reason is it should then run ok as there will be less items to choose from also the reason to have 2 merchants is that if one feed is down there other will still display items in the featured part.
is this possible?
Hi Jonny,
Yes - I think it will be much quicker. Without seeing the existing code you are using, i'm assuming that any code to select random products is using PHP something like this at the start (for 5 random products):
<?php
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` ORDER BY RAND() LIMIT 5";
// ... rest of random products code ...
?>
To change this to select 2 random merchants, try changing this as follows:
<?php
$sql = "SELECT merchant FROM `".$config_databaseTablePrefix."feeds` ORDER BY RAND() LIMIT 2";
database_querySelect($sql,$rows);
$where = "WHERE merchant IN ('".database_safe($rows[0]["merchant"])."','".database_safe($rows[1]["merchant"])."')";
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` ".$where." ORDER BY RAND() LIMIT 5";
// ... rest of random products code ...
?>
(Roland - the same will apply to your code)
If you're not sure how to integrate this change post the code you're currently using for featured products and I'll add the changes for you...
Cheers,
David.
cheers , from what I have learnt so far I think I should be able to figure this out
cheers
This is what I now have below but im getting a MYSQL error
$sql = "SELECT merchant FROM `".$config_databaseTablePrefix."`feeds ORDER BY RAND() LIMIT 2";
database_querySelect($sql,$rows);
$where = "WHERE merchant IN ('".database_safe($rows[0]["merchant"])."','".database_safe($rows[1]["merchant"])."')";
$sql = "SELECT * FROM `".$config_databaseTablePrefix."`products ".$where." ORDER BY RAND() LIMIT 9";
if (database_querySelect($sql,$rows))
{
$sqlNames = array();
foreach($rows as $featured)
{
$sqlNames[] = "'".$featured["name"]."'";
}
$sqlIn = implode(",",$sqlNames);
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE name IN (".$sqlIn.") GROUP BY name";
database_querySelect($sql,$rows);
$featured["products"] = $rows;
foreach($featured["products"] as $k => $product)
{
if ($config_useRewrite)
{
$featured["products"][$k]["productHREF"] = "product/".tapestry_hyphenate($product["name"]).".html";
$featured["products"][$k]["reviewHREF"] = "review/".tapestry_hyphenate($product["name"]).".html";
}
else
{
$featured["products"][$k]["productHREF"] = "products.php?q=".urlencode($product["name"]);
$featured["products"][$k]["reviewHREF"] = "reviews.php?q=".urlencode($product["name"]);
}
}
}
if (isset($featured)) require("html/featured.php");
require("html/footer.php");
?>Hi Jonny,
Sorry about that - the apostrophe arund the table names is in the wrong place in both the SQL statements. The first 4 lines of your code should be this:
$sql = "SELECT merchant FROM `".$config_databaseTablePrefix."feeds` ORDER BY RAND() LIMIT 2";
database_querySelect($sql,$rows);
$where = "WHERE merchant IN ('".database_safe($rows[0]["merchant"])."','".database_safe($rows[1]["merchant"])."')";
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` ".$where." ORDER BY RAND() LIMIT 9";Cheers,
David.
cheers but still getting the error
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/sites/****.co.uk/public_html/includes/database.php on line 21
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/sites/*****/public_html/includes/database.php on line 26
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/sites/******/public_html/includes/database.php on line 21
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/sites/******/public_html/includes/database.php on line 26
sorry , all sorted , my mistake , all works a treat now
cheers!!!
Hi David,
I would prefer if the code does this:
Look for the merchant, picking the first product in the database, going to next merchant picking first product....etc.
But if this is not codable I would also prefer what you proposed.
The code I use is:
$sql = "SELECT name FROM `".$config_databaseTablePrefix."products` ORDER BY RAND() LIMIT 5";
Regards
Roland
Hi Roland,
That should be feasible. Beforehand, could you try the modification as described above to make sure that it is fast enough on your site, so change:
$sql = "SELECT name FROM `".$config_databaseTablePrefix."products` ORDER BY RAND() LIMIT 5";..to:
$sql = "SELECT merchant FROM `".$config_databaseTablePrefix."feeds` ORDER BY RAND() LIMIT 2";
database_querySelect($sql,$rows);
$where = "WHERE merchant IN ('".database_safe($rows[0]["merchant"])."','".database_safe($rows[1]["merchant"])."')";
$sql = "SELECT name FROM `".$config_databaseTablePrefix."products` ".$where." ORDER BY RAND() LIMIT 5";If that works fine, then we can look at picking 1 from each (n) merchants...
Cheers,
David.
Hi David,
it works very much faster but still not so fast as I want. Perhaps the next step is fast enough.
Regards
Roland
Hi Roland,
Have you been able to prove that it is the random products modification causing the slow load. You can check this by commenting out that section of index.php. If it is, then we can look at optimising that somehow for you...
Cheers,
David.