Hi David,
My hosting company has provided some evidence of slow queries (1.7 GB worth of old slow query logs when last checked) and from the output below they all appear to come from PT.
How can I optimise the database tables and clear up these queries?
Is there a cron job I could set up to do this automatically every week?
### 559 Queries
### Total time: 3749, Average time: 6.70661896243292
### Taking 2 to 94 seconds to complete
### Rows analyzed 422 - 235414
SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('XXX') AS relevance FROM `products` WHERE category = 'XXX' AND MATCH name AGAINST ('XXX') AND name <> 'XXX' GROUP BY name ORDER BY relevance LIMIT XXX;
SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('Glass Board Shorts White') AS relevance FROM `products` WHERE category = 'Mens Clothing' AND MATCH name AGAINST ('Glass Board Shorts White') AND name <> 'Glass Board Shorts White' GROUP BY name ORDER BY relevance LIMIT 3;
### 306 Queries
### Total time: 1961, Average time: 6.40849673202614
### Taking 2 to 19 seconds to complete
### Rows analyzed 690 - 205891
use fsdukXXX;
SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('XXX') AS relevance FROM `products` WHERE category = 'XXX' AND MATCH name AGAINST ('XXX') AND name <> 'XXX' GROUP BY name ORDER BY relevance LIMIT XXX;
use fsduk1;
SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('Harlequin Romance Series At the Midnight Hour The Harlequin Romance Series US Version') AS relevance FROM `products` WHERE category = 'DVD' AND MATCH name AGAINST ('Harlequin Romance Series At the Midnight Hour The Harlequin Romance Series US Version') AND name <> 'Harlequin Romance Series At the Midnight Hour The Harlequin Romance Series US Version' GROUP BY name ORDER BY relevance LIMIT 3;
### 382 Queries
### Total time: 1048, Average time: 2.7434554973822
### Taking 2 to 52 seconds to complete
### Rows analyzed 875 - 110824
SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('XXX') AS relevance FROM `products` WHERE MATCH name AGAINST ('XXX') AND name <> 'XXX' GROUP BY name ORDER BY relevance LIMIT XXX;
SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('AVALON ORGANICS BATH SHOWER GEL LAVENDER 32 OZ') AS relevance FROM `products` WHERE MATCH name AGAINST ('AVALON ORGANICS BATH SHOWER GEL LAVENDER 32 OZ') AND name <> 'AVALON ORGANICS BATH SHOWER GEL LAVENDER 32 OZ' GROUP BY name ORDER BY relevance LIMIT 3;
### 139 Queries
### Total time: 737, Average time: 5.30215827338129
### Taking 2 to 55 seconds to complete
### Rows analyzed 309 - 43921
use WWOPTDB;
SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('XXX') AS relevance FROM `products` WHERE category = 'XXX' AND MATCH name AGAINST ('XXX') AND name <> 'XXX' GROUP BY name ORDER BY relevance LIMIT XXX;
use WWOPTDB;
SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('Glass Board Shorts White') AS relevance FROM `products` WHERE category = 'Mens Clothing' AND MATCH name AGAINST ('Glass Board Shorts White') AND name <> 'Glass Board Shorts White' GROUP BY name ORDER BY relevance LIMIT 3;Regards,
Ben
Hi David,
Thanks for the information. I have added that change and will monitor the effects.
Regards,
Ben
I am having major slowdowns on this same type of query from products.php.
I tried the optimization that you suggested, and it helped, but the queries are still very slow. Is there any other way to further optimize this?
Thanks
-Jon
Hi Jon,
What sort of size database are you working with (approx total products), and what hosting are you using (i.e. shared / VPS / dedicated server etc.)?
Cheers,
David.
Hi,
I am finding that this particular query is causing problems too. I have switched it off on some sites or on sites where I have better categorisation, made the query drill down in to smaller groups of products to search through. It would be great if this could be looked at by the expert! This can run slowly and cause timeouts on sites with as few as 13,000 products if there is a category with a large number of products in it.
Cheers.
Keeop
I'm on a shared server, and my database is getting pretty large with around 283,000 products.
My site doesn't get a whole lot of traffic though. Are there any optimizations you could suggest besides upgrading my hosting plan or reducing the number of products? Even if I must sacrifice some functionality...
thanks
Hi,
Wow - that's good going for shared hosting!
If you are finding performance issues, it may be worth disabling related products ($config_useRelated in config.php) otherwise I wouldn't suggest disabling anything unless you are noticing specific problems.
Ultimately, it may be worth looking at the separate category installations as described in this thread;
http://www.pricetapestry.com/node/205
...as this may make your site easier to navigate as well as improving performance...
Cheers,
David.
Now that I look, I already have $confug_useRelated = false in my config.php
I'm still getting tons of slow queries that look like this:
SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('Fender American Standard Jazz Bass Lefty Black Rosewood Fretboard') AS relevance FROM `products` WHERE MATCH name AGAINST ('Fender American Standard Jazz Bass Lefty Black Rosewood Fretboard') GROUP BY name ORDER BY relevance DESC LIMIT 4;
If there is no way to speed that query up, I'm going to look at reducing the number of products imported by only importing products priced above a certain price. Is there a good technique for this?
Thanks again.
-Jon
Hi Jon,
Certainly (and doing so would definitely speed that query up). In includes/admin.php look for the following code on line 168:
$record[$admin_importFeed["field_price"]] = tapestry_decimalise($record[$admin_importFeed["field_price"]]);...and REPLACE this with:
$record[$admin_importFeed["field_price"]] = tapestry_decimalise($record[$admin_importFeed["field_price"]]);
if ($record[$admin_importFeed["field_price"]] < 50.00) return;(replacing 50.00 with the lower limit you wish to restrict import to)
Cheers,
David.
Hello David. Where can I change this code in the New Distribution?
$record[$admin_importFeed["field_price"]] = tapestry_decimalise($record[$admin_importFeed["field_price"]]);
...and REPLACE this with:
$record[$admin_importFeed["field_price"]] = tapestry_decimalise($record[$admin_importFeed["field_price"]]);
if ($record[$admin_importFeed["field_price"]] < 50.00) return;
(replacing 50.00 with the lower limit you wish to restrict import to)
Hello Ben,
I notice that these are all "Relevant Products" queries, and it seems that the WHERE clause to exclude the current product is what is causing the slow-down in this particular configuration.
An alternative way to achieve the same result would be to modify this part of the code so that the main product is not excluded by the SQL, but is simply removed from the result set afterwards; however note that this would also exclude the category comparison (if category is set).
To try this, in products.php, look for the following code, beginning at line 73:
$where .= "MATCH name AGAINST ('".database_safe($q)."') AND name <> '".database_safe($product["products"][0]["name"])."'";$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('".database_safe($q)."') AS relevance FROM `".$config_databaseTablePrefix."products` WHERE ".$where." GROUP BY name ORDER BY relevance LIMIT 3";
$searchresults["numRows"] = database_querySelect($sql,$rows);
...and REPLACE this with the following:
$where = "MATCH name AGAINST ('".database_safe($q)."')";$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('".database_safe($q)."') AS relevance FROM `".$config_databaseTablePrefix."products` WHERE ".$where." GROUP BY name ORDER BY relevance DESC LIMIT 4";
$searchresults["numRows"] = database_querySelect($sql,$rows);
array_shift($rows);
This should speed things up considerably as the entire query is being derived from an index rather than a table scan...
Cheers,
David.