fbpx

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 NGINX module packages and VMODs for Varnish. If you want to install NGINX, Varnish, and lots of useful performance/security software with smooth yum upgrades 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”:

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

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

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