Tring to find where query is

Submitted by wilkins on Mon, 2013-04-01 11:29 in Price Tapestry

Hi david

just moving a site to a new server and trying to find an issue with load spikes, going through slow queries and found
this

SELECT id,name,normalised_name,image_url,description,price FROM `pt_products` WHERE id IN (2511306,3345235,2753424,2741413,954581,481669,4335615,2051404,1841167,4677425);

could you tell us where in the script this is and what is the purpose.

Brent

Hello Brent, That is one of

Submitted by support on Mon, 2013-04-01 14:32

Hello Brent,

That is one of the search results re-query statements, used after search results in search.php (constructed at line 374) and for related products in products.php (constructed at line 124).

However, it is a simple SELECT, on the primary key, so if it is showing up in slow queries I would be inclined to think that maybe an import was taking place at the same time and the `products` table was locked; but another possibility is that there is an issue with the keys on your database.

Could you perhaps create and run the following PHP code as describe.php and then run from your Price Tapestry installation folder, and post the output into a reply? This will show the index structure and more importantly whether they are enabled which may help shed some light...

<?php
  require("includes/common.php");
  header("Content-Type: text/plain");
  $sql = "DESCRIBE `".$config_databaseTablePrefix."products`";
  database_querySelect($sql,$result);
  print_r($result);
  $sql = "SHOW INDEXES IN `".$config_databaseTablePrefix."products`";
  database_querySelect($sql,$result);
  print_r($result);
  print "Done.";
?>

Thanks,
David.
--
PriceTapestry.com

Hi david see output

Submitted by wilkins on Mon, 2013-04-01 15:03

Hi david

see output below

Brent

{code saved}

Thanks Brent, That all looks

Submitted by support on Tue, 2013-04-02 08:41

Thanks Brent,

That all looks in order, and I've checked using DESCRIBE that such a query uses WHERE on the primary key, so my first assumption is what I would consider to be the case here (that it was taking place during an import and `pt_products` was locked, perhaps (which wouldn't in themselves then be the cause of a high server load at that point).

Do you see any entries in the log containing "GROUP BY" as these are the search / related products queries that for very large sites it may be necessary to look at MySQL configuration optimisation (this thread discusses MySQL optimisation for large installations).

If you'd like to post a few lines of your slow query log (I'll remove before publishing your reply) I'll happily take a look through and see what else I can suggest...

Cheers,
David.
--
PriceTapestry.com

Hi David Spent today going

Submitted by wilkins on Tue, 2013-04-02 19:21

Hi David

Spent today going through all queries and logs and have decided that the database is just to big, so decided to break down into categories. the problem is that all the product pages i have at present are listed on the search engines and get traffic.

So I have created a new DB with all the top/favorite products on, this now leads to a lot of 404 pages. Is there any way text can be added to the 404 page above the related products. The idea is to create a directory list and a drop down search box for each of the new category databases I create. This will help guide people who are looking for the products I have moved.

Regards

Brent

Hello Brent, Sure - in

Submitted by support on Wed, 2013-04-03 08:09

Hello Brent,

Sure - in products.php, look for the following code at line 158:

if (isset($related)) require("html/related.php");

...and REPLACE with:

  if (!isset($product)) require("html/noproduct.php");

  if (isset($related)) require("html/related.php");

...and then create your html/noproduct.php file containing the HTML required...

Cheers,
David.
--
PriceTapestry.com