Magento Module Performance Part 2: DB Indexes

This second post in the series about Magento module performance again deals with database queries. This time we’ll be focusing on the use of indexes. This may seem like a fairly obvious subject, but I’ve noticed it’s far too common for developers to neglect this part of their application design. Having proper indexes added to a table will significantly help performance, especially as the table grows. So to which table columns should you add an index? A general rule is to consider columns that are used in a JOIN or WHERE clause or are used in an ORDER BY.

Let’s consider our gift registry model once again.

Looking at the registry table, I know I’ll be querying the table by type and event_date and I’ll also be using the customer_id column in a table join for searches by customer. I will be querying registry type and event_date together so I will use both columns in a composite index.

How about the registry_items table? Well I know I’ll be joining it to the registry table using registry_id. I’ll add an index to that column as well.

I’ve just scratched the surface of optimizing tables for queries, but again my goal here is mainly to remind Magento developers not to forget the backbone of their application. Be good to the database and it will be good to you :)

For some more advanced fun I’d recommend reading up on using MySQL EXPLAIN to optimize your queries! If you’re interested check out this slideshare.

Rails activerecord-import: Conditionally Update on Duplicate Key

Recently I’ve been working on a Rails app that allows users to post large batches of data. New records are inserted and existing records are updated. Since there could be hundreds of records in a single post, I decided to try Zach Dennis’ activerecord-import gem and I was very pleased with the results. By importing with columns and arrays of values and turning off model validations I was seeing huge performance gains. I am using a MySQL database so I made use of the on_duplicate_key option to update a parameters value if an existing parameter was sent.

The only problem was that there is a functional requirement that once a parameter is created by a provider, only that provider can update the value. Looking at the activerecord-import wiki I couldn’t find a way to conditionally update values on duplicate key. I really didn’t want to use model validations because I needed this import to be as fast as possible, so I decided to take a look at the source code and hope to find an undocumented gift.

Huzzah! Besides an array or hash, on_duplicate_key_update accepts a string of SQL!

Awesome, now my import is fast and I can even do conditional updates without using validations.