Home » Issue Tracker » Ubercart

Bestsellers SQL causing slowdown

Submitted by codexmas@drupal.org on Fri, 11/30/2007 - 10:24.
Project: 
Ubercart
Category: 
bug report
Priority: 
critical
Status: 
active

I recently updated a site to the latest bzr version of ubercart.
In this update was some code that pulled in the contents of the bestsellers block.
After several orders would not complete, due to this query causing the server to lock everything until it was done.

$sql = 'SELECT nd.nid, nd.title,
              (SELECT SUM(op.qty)
                FROM {uc_order_products} AS op
                WHERE op.nid = nd.nid) AS sold
            FROM {node} AS nd
            WHERE nd.type IN ("'. implode('","', $product_types) .'")
            ORDER BY sold DESC
              LIMIT '.$gridwidth;

I didn't have the block turned on before, but this new code addition in:
function theme_uc_catalog_browse pulls it in

The site has around 5000 products and around 7500 orders, which have almost 30000 order products.
I am only guessing that there is either some optimization required or a rethink on how to get the bestseller information when there are that many rows, or even 10 times that many just incase.

I am running on MySQL 5.0.45 and PHP 4.4.7
The database tables are MyISAM.

Thanks,
Gord.
#1Submitted by Lyle on Fri, 11/30/2007 - 11:22.

Re: Bestsellers SQL causing slowdown

Replace it with this query and see if that helps.

<?php
  $sql = 'SELECT nd.nid, nd.title, SUM(op.qty) AS sold
    FROM node AS nd
    LEFT JOIN uc_order_products AS op ON op.nid = nd.nid
    WHERE nd.type IN ("'. implode('","', $product_types) .'")
    GROUP BY nd.nid
    ORDER BY sold DESC
    LIMIT '. $gridwidth;
?>

You should be able to turn off the bestseller block in the Catalog settings. That will keep it from showing up on those pages as well.

#2Submitted by codexmas@drupal.org on Fri, 11/30/2007 - 12:20.

Tested your query in phpMyAdmin

SELECT nd.nid, nd.title, SUM(op.qty) AS sold
    FROM node AS nd
    LEFT JOIN uc_order_products AS op ON op.nid = nd.nid
    WHERE nd.type IN ("product","media","printed","gift","product_kit")
    GROUP BY nd.nid
    ORDER BY sold DESC
            LIMIT 3

Seems to still be very slow.
Any ideas?

Edit: Here is the result
Showing rows 0 - 2 (3 total, Query took 244.8461 sec)

Thanks,
Gord.

#3Submitted by Ryan on Fri, 11/30/2007 - 12:42.

Holy crap. Are you using

Holy crap. Sticking out tongue Are you using query cache on that server?

A not totally foolproof way to solve the dilemma would be to implement a counter table that simply increments a value for each SKU whenever a product is purchased (hook_order(), $op = 'submit'). You can setup the module to populate the table based on existing orders when the block is first enabled.