Is there a way to speed up multiple Joins in Magento Order Grid?

Question

I am having an issue with load times in the Sales_Order_Grid after join creating three joins to add additional columns.

This has significantly increased loading times and I was wondering if there is a way to increase the speed or optimise the code to prevent the sluggishness?

Here are my joins

protected function _prepareCollection() {

$collection = Mage::getResourceModel($this->_getCollectionClass());

/* This adds the additional Joins*/

$collection->getSelect()->joinLeft(array('sfop' => 'sales_flat_order_payment'),
    'sfop.parent_id=main_table.entity_id', array(
    'sfop.method', 'sfop.additional_information'));

$collection->getSelect()->joinLeft(array('sfoa'=>'sales_flat_order_address'),
    'main_table.entity_id = sfoa.parent_id AND sfoa.address_type="shipping"',array('sfoa.street',
        'sfoa.city','sfoa.region','sfoa.postcode','sfoa.telephone'));

$collection->getSelect()->joinLeft(array(
        'item'=>$collection->getTable('sales/order_item')),
    'item.order_id=`main_table`.entity_id AND item.product_type="simple"',
    array(
        'skus' => new Zend_Db_Expr('group_concat(item.sku SEPARATOR ", ")'),
        'name' => new Zend_Db_Expr('group_concat(item.name SEPARATOR ", ")'),
        'brand' => new Zend_Db_Expr('group_concat(item.brand SEPARATOR ", ")')
    )
);
/* End additional Joins*/
$collection->getSelect()->group('main_table.entity_id');
$this->setCollection($collection);
return parent::_prepareCollection();

}

I've added the columns for the information that I need to display without any errors

...

        $this->addColumn('customer_firstname', array(
            'header' => Mage::helper('sales')->__('Customer Firstname'),
            'width' => '80px',
            'index' => 'customer_firstname',
            'type' => 'text',
            'filter_index' => 'customer_firstname'
        ));

...

The following 0 - 19 (20 total, Query took 93.0730 sec)

SELECT `main_table`.*, `sfop`.`cc_type`, `sfop`.`cc_last4`, `sfop`.`cc_type`, `sfop`.`last_trans_id`, `sfop`.`method`, `sfop`.`additional_information`, `billing`.`postcode`, `sfoa`.`street`, `sfoa`.`city`, `sfoa`.`region`, `sfoa`.`postcode`, `sfoa`.`telephone`, group_concat(item.sku SEPARATOR ", ") AS `skus`, group_concat(item.name SEPARATOR ", ") AS `name`, group_concat(item.nego SEPARATOR ", ") AS `nego` FROM `sales_flat_order` AS `main_table`
 LEFT JOIN `sales_flat_order_payment` AS `sfop` ON sfop.parent_id=main_table.entity_id
 LEFT JOIN `sales_flat_order_address` AS `billing` ON main_table.entity_id = billing.parent_id AND billing.address_type="billing"
 LEFT JOIN `sales_flat_order_address` AS `sfoa` ON main_table.entity_id = sfoa.parent_id AND sfoa.address_type="shipping"
 INNER JOIN `sales_flat_order_item` AS `item` ON item.order_id=`main_table`.entity_id AND item.product_type="simple" GROUP BY `main_table`.`entity_id` ORDER BY main_table.created_at DESC LIMIT 20

The question is how do I optimise the Grid to increase speed?

Hope someone can help??

Answer 1

The speed optimization tip I can give you will also help you avoid potential issues:

Never ever ever ever perform joins from sales_flat_order_grid on live tables such as sales_flat_order_address; any 'lock' introduced from admins performing work (such as sorting by an unindexed column) will also lock the tables for orders being placed. This contributes to all sorts of issues that are difficult to diagnose and untangle like orders that clear the gateway but cannot be re-placed after a deadlock or lock wait timeout due to Paypal or Authorize (or other gateways) not accepting the reserved order id for a now second "payment".

Instead, create the desired columns in sales_flat_order_grid and display the information from there. In certain cases the existence of the attribute name as a column name in the order placement will automagically add the desired values into the sales grid. In other cases you may need to use an observer.

In summary, put the data you want to view in the admin into the admin grid itself.

Best of luck.