Magento-lts: Add two indexes, in sales_flat_order & sales_flat_order_item

[pepijnblom]

I've been having to add the following indexes in most of our shops to increase performance:

• sales_flat_order.email
• sales_flat_order_item.product_id

Might it be an idea to add install scripts for those?

I know it can have a bit of an impact on already running shops adding those, the database tends to lock while updating, but it could be added to already existing install scripts. That way they are at least setup on clean installs.

[tmotyl]

can you describe an example query and operation, so we can reproduce the perrformance gain?

[kestraly]

I'm guessing this would be to have products and customer emails on the sales_order_grid in the admin by joining the other tables to show those details? I could be wrong though.

[pepijnblom]

This is not for the admin grid as far as I am aware, we've left that plain vanilla.

At least the following modules query by customer_email on sales_flat_order:

• AW_Rma
• CommerceExternsions_GuestToReg

The product_id is a bit harder to locate since it's going to be part of a join. It's just incredibly handy to have an index on there when you need to retrieve more product information. It's most likely an order export module or something similar which causes these queries.

But yeah, I don't think it will be required for base installs. You can go ahead and close, but have a look at your own logs to see if they pop up, you never know.

[colinmollenhour]

I agree these should be indexed by default just because they are so ubiquitous. It may or may not help the base install but there are many cases where it is handy and there could be conflicts if every module that could benefit from them tried to add them separately and not all using the proper method. To show up in a slow query log you probably need close to a million orders so just because many people aren't experiencing issues doesn't mean they wouldn't benefit eventually. I don't see how adding these two indexes would harm performance for anyone, although depending on how the migration script was written it could throw an error if someone already has these indexes or perhaps create a duplicate so that's something to watch out for.

[seansan]

​+1
however think about how this works "if/when" the index already exists
(because of manual addition)​

On Mon, Nov 27, 2017 at 6:56 PM, Colin Mollenhour [e-mail]
wrote:

I agree these should be indexed by default just because they are so
ubiquitous. It may or may not help the base install but there are many
cases where it is handy and there could be conflicts if every module that
could benefit from them tried to add them separately and not all using the
proper method. To show up in a slow query log you probably need close to a
million orders so just because many people aren't experiencing issues
doesn't mean they wouldn't benefit eventually. I don't see how adding these
two indexes would harm performance for anyone, although depending on how
the migration script was written it could throw an error if someone already
has these indexes or perhaps create a duplicate so that's something to
watch out for.

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/OpenMage/magento-lts/issues/384#issuecomment-347267900,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAn0a03s76QxRi1cby1NUsF_nz6DBSQEks5s6vfFgaJpZM4QlUu2
.

[DannyDaemonic]

I know it's only anecdotal but to help nudge this forward I just wanted to say that adding product_id as an index to my sales_flat_order_item made a _big_ difference in page load times for a store I host. The slowest query I was seeing was due to a custom sorting module but it may have helped other queries as well. It's also worth noting this has crept on me slowly so it may not be obvious for fresh installs. (Or in stores with fewer products?) Either way there must be others out there who would benefit from this as well.

[tmotyl]

@DannyDaemonic please give more details,

• how did you observe the difference in page load?
• which pages/urls did you tried (backend or frontend)
• paste slow query you observed which were gone after adding the index?
• how many orders/order items you have in db
• what hardware the db is running on?

[tmotyl]

I agree this sounds as a good change, but I would love to be able to test it myself on some of the big installations.

[colinmollenhour]

One data point:

My largest installation actually already has two indexes on sales_flat_order.customer_email. One was added by the Ebizmarts Mailchimp module and the other by a custom module in 2012! It also has an index on sales_flat_order_item.product_id since 2017. I'm pretty certain there are no downsides to these indexes, only upsides.

Note, you can add indexes with shorter lengths than the column's full length which is often times more efficient. E.g. for email addresses after the first 15 bytes I don't think the index is very useful.

From MySql "Optimizing Data Size" article:

If it is very likely that a long string column has a unique prefix on the first number of characters, it is better to index only this prefix, using MySQL's support for creating an index on the leftmost part of the column (see Section 13.1.14, “CREATE INDEX Statement”). Shorter indexes are faster, not only because they require less disk space, but because they also give you more hits in the index cache, and thus fewer disk seeks.

<?php
/* @var $this Mage_Core_Model_Resource_Setup */

/*
 * Add index to sales_flat_order on customer_email for fast lookup, only first 15 bytes
 */
$keyList = $this->getIndexList($this->getTable('sales/order')});
if ( ! isset($keyList[strtoupper('customer_email')])) {
  $this->run("
  ALTER TABLE  {$this->getTable('sales/order')}
  ADD INDEX  `IDX_SALES_FLAT_ORDER_CUSTOMER_EMAIL` (  `customer_email` ( 15 ) );
  ");
}

/*
 * Add index to sales_flat_order_item.product_id for fast join/lookup
 */
$this->getConnection()->addIndex(
    $this->getTable('sales/order_item'),
    'IDX_SALES_FLAT_ORDER_ITEM_PRODUCT_ID',
    ['product_id']
);

[seansan]

I checked my DB and both indexes also already exist

[tmotyl]

+1 for adding it.

[tmotyl]

Adding it using update script does require change in the module version.
How do we solve it on OpenMage? @colinmollenhour @Flyingmana ?

[tmotyl]

here is the working upgrade script to add these indexes, as the one from @colinmollenhour had issues:

<?php
/** @var Mage_Catalog_Model_Resource_Setup $installer */
$installer = $this;
$installer->startSetup();


/*
 * Add index to sales_flat_order on customer_email for fast lookup, only first 15 bytes
 */
$keyList = $installer->getConnection()->getIndexList($installer->getTable('sales/order'));
if (!isset($keyList[strtoupper('customer_email')])) {
  $installer->run("
  ALTER TABLE  {$installer->getTable('sales/order')}
  ADD INDEX  `IDX_SALES_FLAT_ORDER_CUSTOMER_EMAIL` (  `customer_email` ( 15 ) );
  ");
}

/*
 * Add index to sales_flat_order_item.product_id for fast join/lookup
 */
$this->getConnection()->addIndex(
    $installer->getTable('sales/order_item'),
    'IDX_SALES_FLAT_ORDER_ITEM_PRODUCT_ID',
    ['product_id']
);


$installer->endSetup();

[Flyingmana]

@tmotyl the most safe way would be to introduce a new module with own version.

[seansan]

How about adding it to the read me or some post installation list of
actions?

One can then decide for themselves if they want to run it or not

On Fri, 5 Jun 2020 at 13:57, Tymoteusz Motylewski [e-mail]
wrote:

here is the working upgrade script to add these indexes, as the one from
@colinmollenhour https://github.com/colinmollenhour had issues:

@var Mage_Catalog_Model_Resource_Setup $installer */$installer = $this;$installer->startSetup();

/* * Add index to sales_flat_order on customer_email for fast lookup, only first 15 bytes /$keyList = $installer->getConnection()->getIndexList($installer->getTable('sales/order'));if (!isset($keyList[strtoupper('customer_email')])) {
$installer->run(" ALTER TABLE {$installer->getTable('sales/order')} ADD INDEX IDX_SALES_FLAT_ORDER_CUSTOMER_EMAIL ( customer_email ( 15 ) ); ");
}
/ * Add index to sales_flat_order_item.product_id for fast join/lookup */$this->getConnection()->addIndex(
$installer->getTable('sales/order_item'),
'IDX_SALES_FLAT_ORDER_ITEM_PRODUCT_ID',
['product_id']
);

$installer->endSetup();

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/OpenMage/magento-lts/issues/384#issuecomment-639436677,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/AAE7I223U2O6ORDXEYPNNA3RVDMUJANCNFSM4EEVJO3A
.

[sreichel]

@tmotyl the most safe way would be to introduce a new module with own version.

Or add install/update script to Mage_Sales and raise version ... (doubt in a official 1.6.0.11 release)

    <modules>
        <Mage_Sales>
            <version>1.6.0.10</version>
        </Mage_Sales>
    </modules>

[colinmollenhour]

Agree with just adding it to Mage_Sales. It seems highly unlikely there would be a conflicting update in the next 19 days from Adobe, but could just wait it out as this isn't critical and can be easily added manually for those that don't want to wait.

[sreichel]

@tmotyl the most safe way would be to introduce a new module with own version.

@Flyingmana is right, a new OpenMage_Base module would be better. If we remove modules (GoogleBase, XmlConnect, ...), we should also remove tables and config entries, so we need some kind of update script. Put all db related changes there?