Optimize database querying MySQL & PHP

Question

I'm wondering if there's anyway to modify my PHP code to be even a little lighter on the database? This code is ran every minute in a cronjob.

Would like to ensure that this has as little impact on the db as possible.

<?php
ini_set('memory_limit', '128M');
define('MAGENTO_ROOT', getcwd());
//exit;

$time     = time();
$time2    = $time - 0;
$to       = date('Y-m-d H:i:s', $time2);
$lastTime = $time - 60; //orders from the past X seconds
$from     = date('Y-m-d H:i:s', $lastTime);
$curDate  = date('Y-m-d');

$mageFilename = '/app/Mage.php';
require_once $mageFilename;
Mage::app();

$order_collection = Mage::getResourceModel('sales/order_collection');
$order_collection->addAttributeToSelect('*')->addAttributeToFilter('updated_at', array(
    'from' => $from,
    'to' => $to
))->getSelect();

print count($order_collection);
foreach ($order_collection->getItems() as $order) {
    $cards = Mage::getModel('giftcards/giftcards')->getCollection()->addFieldToFilter('order_id', $order->getId());
    print count($cards);
    foreach ($cards as $card) {
        if ($card->getCardStatus() == 0) {        
            if ((($card->getMailDeliveryDate() == null) || ($curDate == $card->getMailDeliveryDate())) && $card->getCardType() != 'offline') {
                $card->setCardStatus(1);
                $card->save();
                $card->send();
            }
        } elseif (($card->getCardStatus() == 1) && ($curDate < $card->getMailDeliveryDate())) {
            $card->setCardStatus(0);
            $card->save();
        }
    }
}
?>

Well for a start your only using the `id` so change `addAttributeToSelect('*')` to `addAttributeToSelect('id')` — Lawrence Cherone, Jan 04 '18 at 14:31
You should perhaps also change it to just pull orders which are not card_status=1 and add a error_status column, instead of all orders within 60 seconds, as if for some reason the cron job stops or misses one, it wont be sent. — Lawrence Cherone, Jan 04 '18 at 14:34

score 0 · Answer 1 · answered Jan 04 '18 at 14:38

the only thing you need to optimise right away is how you're fetching giftcards: you're getting order_collections and you're iterating over the items and get the related data; this is known as N=1 issue.

I'm not sure how Magento works exactly (I'm pretty sure they have some join functionality that you can use) but you need to pre-fetch everything you need and then loop through the result and format the data however you want it.

Optimize database querying MySQL & PHP

1 Answers1