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.