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

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

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.