MySQL / PHP

The evil increment_id? Or why MySQL is not using an index

by , , revisited on


We have by far the largest RPM repository with dynamic stable NGINX modules and VMODs for Varnish 4.1 and 6.0 LTS. If you want to install NGINX, Varnish, and lots of useful modules for them, this is your one-stop repository to get all performance-related software.
You have to maintain an active subscription in order to be able to use the repository!

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”:

EXPLAIN for increment_id query
EXPLAIN for increment_id query

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`;

You’ll notice: 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:

$parentorder=Mage::getModel('sales/order')->loadByIncrementId((int)$orderAttributes['splited_order_number']);

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 app/etc/local.xml:

<resources>
 <default_setup>
  <connection>
   <profiler>1</profiler> 

And let’s not forget to clear our cached .xml configuration using n98-magerun:

n98-magerun cache:flush

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 strval:

$parentorder=Mage::getModel('sales/order')->loadByIncrementId(strval($orderAttributes['splited_order_number']));

Immediate performance boost and a healthier server with one simple change.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.