yumupgrades for production use, this is the repository for you.
Active subscription is required.
This is about MySQL indexes and some serious performance.
Follow me in optimizing your MySQL performance using indexes for Magento (or any PHP framework, really) using this debug-style guide.
When monitoring the MySQL slow query log, I’ve stumbled upon the following query:
SELECT `sales_flat_order`.* FROM `sales_flat_order` WHERE (`sales_flat_order`.`increment_id`=395067);
Let’s check this query using
EXPLAIN. Simply prepending
EXPLAIN keyword to the query:
EXPLAIN SELECT `sales_flat_order`.* FROM `sales_flat_order` WHERE (`sales_flat_order`.`increment_id`=395067);
And we get the following “picture”:
So it scans 126K rows every time and according to slow log runs quite often, every time taking a couple seconds. The
EXPLAIN shows us that there’s a potential index, but it’s not being used and
WHERE (table scan) is used instead.
So why the index is not being used? Let’s check the table definition with:
SHOW CREATE TABLE `sales_flat_order`;
UNIQUE KEY `UNQ_SALES_FLAT_ORDER_INCREMENT_ID` (`increment_id`), and
`increment_id` varchar(50) DEFAULT NULL COMMENT 'Increment Id',
Alright. The only reason the query is not using existing MySQL index is that the searched value and the indexed column are of different types! Looking at our problematic query, you’ll see that
395067 is not quoted. So the query uses wrong, integer datatype for
increment_id column, which is of
VARCHAR type. Quotes are important!
I have located a sample offensive code and was sure that the
incremented_id value wasn’t quoted here:
All we need to make use of the index is to have a string passed to the
loadByIncrementId method, right? I wasn’t sure, so I have setup a sample test.
First, enabled the Magento SQL profiler in
<resources> <default_setup> <connection> <profiler>1</profiler>
And let’s not forget to clear our cached .xml configuration using
Then I’ve created a sample test script test-increment-id.php with these contents:
<?php require_once 'app/Mage.php'; Mage::app(); Mage::getModel('sales/order')->loadByIncrementId(strval(1)); Mage::getModel('sales/order')->loadByIncrementId(10); Mage::getModel('sales/order')->loadByIncrementId('1'); $profiler = Mage::getSingleton('core/resource')->getConnection('core_write')->getProfiler(); print_r($profiler->getQueryProfiles());
I’ve ran it with
php test-increment-id.php and as expected, the first and third queries came up quoted whereas the second was not. So the fix of problematic code was just to replace casting to integer with
Immediate performance boost and a healthier server with one simple change.