using up all the MySQL resources with this query

Submitted by shopinn on Tue, 2013-10-15 03:14 in Price Tapestry

hi dave have just recieved this from my hosting company
I've had to take your site offline I'm afraid as it's been using up all the MySQL resources with this query due to the table having 500,000 rows:

SELECT *,SUM(views) AS totalviews FROM `pt_products` GROUP BY name ORDER BY totalviews DESC LIMIT 5

although there are over 500.000 products i have before had 800.000 without problems the ony thing i changed in config advanced
was
$config_searchDescription = true;
would this affect it
or is there anything else you could advise as a get around to this problem
or do i have to reduce the number of products
i have deleted the site and will do a new reinstall with the default options set
any advice much apreciated
regards
dave

Hello Dave, I can see why

Submitted by support on Tue, 2013-10-15 08:10

Hello Dave,

I can see why that query would be inefficient with such a large dataset, so I would in fact recommend re-writing that in a couple of ways. It looks like the objective is to obtain the full product record of the top 5 most viewed products, so I'm guessing that at the moment you have some code similar, if not identical to:

  $sql = "SELECT *,SUM(views) AS totalviews FROM `".$config_databaseTablePrefix."products` GROUP BY name ORDER BY totalviews DESC LIMIT 5";

  database_querySelect($sql,$products);

Now, assuming that the product page is updating views by name, there probably shouldn't be any need for the summation, so the first thing I would try, along with not selecting the full records in the first query is as follows:

  $sql = "SELECT name,views FROM `".$config_databaseTablePrefix."products` ORDER BY views DESC LIMIT 5";

  database_querySelect($sql,$products);

  $ins = array();

  foreach($products as $product)

  {

    $ins[] = "'".database_safe($product["name"])."'";

  }

  $in = implode(",",$ins);

  $sql = "SELECT *,views AS totalviews FROM `".$config_databaseTablePrefix."products` WHERE name IN (".$in.") GROUP BY name ORDER BY views DESC";

  database_querySelect($sql,$products);

A further optimisation could be made by putting an index on the views field as the database will then be able to use this making the initial query. A dbmod.php script as follows will add the index:

<?php
  require("includes/common.php");
  $sql = "CREATE INDEX views ON `".$config_databaseTablePrefix."products` (views)";
  database_queryModify($sql,$result);
  print "Done.";
?>

Run once from the Price Tapestry installation folder, and then delete the file.

Hope this helps! If you're not sure if you could post the full section of code in question and I'll check it out for you.

Cheers,
David.
--
PriceTapestry.com

hi david i have done the

Submitted by shopinn on Wed, 2013-10-16 03:38

hi david
i have done the above mods
also i have used the code below from an old post
with a limit of 6
would this be a safer way to randomise the featured products and keep the front page fresh

$sql = "SELECT DISTINCT(merchant) AS merchant FROM `".$config_databaseTablePrefix."products` ORDER BY RAND() LIMIT 1";
database_querySelect($sql,$result);
$merchant = $result[0]["merchant"];
$sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` WHERE merchant='".database_safe($merchant)."' ORDER BY RAND() LIMIT 3";
its purely for cosmetic reasons not to specifically push certain products
or would be greatful for any better ideas
thanks again
dave

Hi Dave, Yes - that's a good

Submitted by support on Wed, 2013-10-16 08:52

Hi Dave,

Yes - that's a good way to do random products from a very large product database, reduces the load significantly.

Cheers,
David.
--
PriceTapestry.com